I’m making a form to fill out a sheet automatically, but I dont know the code to select de firt empty cell.
How do you tell your “macro” what column to search for the “first empty cell”?
Will it start above the filled cells? Amidst the filled cells? Below the filled cells?
(Is “first” related to a specific direction of search?)
Is there an assurance concerning the multitude of filled ranges within the column?
In short: Words are quickly found and even more quickly used.
Coding an automatism you need exactly defined terms or you will have to do it again and again, every time something doesn’t work as expected in a given case.
I wish the Code will select row 2 in column A, and change to next, when I insert new datas.
I did it that way.
Sub insert
Dim oCurrentController As Variant
Dim oActiveSheet as variant
dim oObj1 as variant
dim aRangeAddress As New com.sun.star.table.CellRangeAddress
dim nEndRow as long
dim oSheets as variant
Dim oObj2 as variant
dim oCellRangeByName as variant
oCurrentController = ThisComponent.getCurrentController()
oActiveSheet = oCurrentController.getActiveSheet()
oObj1 = oActiveSheet.createCursor()
oObj1.gotoEndOfUsedArea(True)
aRangeAddress = oObj1.getRangeAddress()
nEndRow = aRangeAddress.EndRow
oSheets = ThisComponent.getSheets()
oObj2 = oSheets.getByIndex(0)
oCellRangeByName = oObj2.getCellRangeByName("F1").Value
oCellRangeByName2 = oObj2.getCellRangeByName("F2").Value
ThisComponent.Sheets(0).getCellByPosition(0,nEndRow+1).value = oCellRangeByName
ThisComponent.Sheets(0).getCellByPosition(1,nEndRow+1).value = oCellRangeByName2
End SubBut now, I wish copy a value from form text box instead from cell.
You didn’t answer to my question in return posted in my first comment.
You simply disregarded the suggestion by @mauricio without commenting on it.
Who then should worry about your new question posted as a comment now?
New question, new topic is the rule.
Commenting on what you presented as your solution:
You get the nEndRow having moved your cursor to the EndOfUsedRange in the ActiveSheet. Then you use this value as if it is the EndRow in the first sheet (Sheets.getByIndex(0)). Anyway you don’t worry about which coulmn is relevant for the EndRow question nor do you give any assurances insofar. What’s the idea of this strange proceeding?
Then: What do you mean by “text box” now? Graphíc shapes of the TextBox type rarely occur in spreadsheets, and if they do it should be suspected to be bad design.
Any way: How do you intend to get access to the one textbox you need in a specific case?
Sorry!! My first code.
I found this solution anyway:
Sub
Dim oCurrentController As Variant
Dim oActiveSheet as variant
dim oObj1 as variant
dim aRangeAddress As New com.sun.star.table.CellRangeAddress
dim nEndRow as long
oCurrentController = ThisComponent.getCurrentController()
oActiveSheet = oCurrentController.getActiveSheet()
oObj1 = oActiveSheet.createCursor()
oObj1.gotoEndOfUsedArea(True)
aRangeAddress = oObj1.getRangeAddress()
nEndRow = aRangeAddress.EndRow
oTextfield1 = oDialog1.getControl("TextField1")
oTextfield2 = oDialog1.getControl("TextField2")
oTextfield3 = oDialog1.getControl("TextField3")
oTextfield4 = oDialog1.getControl("TextField4")
ThisComponent.Sheets(0).getCellByPosition(0,nEndRow+1).string = oTextfield1.text
ThisComponent.Sheets(0).getCellByPosition(1,nEndRow+1).string = oTextfield2.text
ThisComponent.Sheets(0).getCellByPosition(2,nEndRow+1).string = oTextfield3.text
ThisComponent.Sheets(0).getCellByPosition(3,nEndRow+1).string = oTextfield4.text
End Subtry:
Sub next_cell()
cell = ThisComponent.CurrentSelection
cell = get_next_cell(cell)
MsgBox cell.AbsoluteName
End Sub
Function get_next_cell(source) As Object
cursor = source.SpreadSheet.createCursorByRange(source)
cursor.gotoEnd()
cell = source.SpreadSheet.getCellByPosition(source.RangeAddress.StartColumn, cursor.RangeAddress.EndRow +1 )
get_next_cell = cell
End FunctionThanks!! I tried it, but it wasn’t what I was looking for.