How to focus (via macro) on a combobox on a sheet (not on a dialog or a form)?

I´ve tried…
oSheet = thiscomponent.currentcontroller.activesheet
oController = ThisComponent.getCurrentController()
oForm = oSheet.getDrawpage().getForms().getByIndex(0)
oControl = oForm.getByName(“COMBO BOX NAME”)
oControlView = oController.getControl(oControl)
oControlView.setFocus()

but nothing happens.

Could anybody help me?

Hello,
Just tested your code and it works for me. Only change I made was to the name of the Combo Box. Did execute the code from a push button on the sheet.
.
Here is my test: set_focus.ods (10.7 KB)

You can step through the code, one line at a time, from the Macro window, then you will know which line has caused the problem. There is a button on the Macro window’s toolbar with an icon that looks like two curly brackets {} and that’s the one that “steps” through each line one at a time.

There is no error…Nothing happens

I downloaded the ODS file from @Ratslinger and added two lines of code that help you to see that it actually does something.

Sub SetFocus
oSheet = thiscomponent.currentcontroller.activesheet
oController = ThisComponent.getCurrentController()
oForm = oSheet.getDrawpage().getForms().getByIndex(0)
oControl = oForm.getByName(“CB1”)
oControl.Text = "Hello"
MsgBox oControl.Text, 1
oControlView = oController.getControl(oControl)
oControlView.setFocus()
End Sub

image
Let me explain…
I enter a number on combo box, if the number exists, I hit ENTER, The macro colours the cell background yellow. After that, the macro should focus combo box to me enter a new number…that´s the problem.

This is my macro code

Sub KeyPressed(oEvent as Object)
Dim oSheet, oForm, oCombox, oCombobox as Object
Dim oControl, oController, oControlView as object
Dim lPos as Long
Dim sItem, sName as String
Dim iItems as Integer
Dim anything as variant
Dim nomItem As Long
Dim oListBox as object
Dim x as integer
Dim bFound as Boolean
Dim oView, oCell as Object
Dim Indice, nCount as Long
Dim oDoc, oField, oDocView as object

oDoc = ThisComponent
If oEvent.KeyCode = com.sun.star.awt.Key.RETURN Then
iItems = oEvent.Source.ItemCount
sItem = oEvent.Source.Text
Transparencia = True
x = 0
bFound = false
While not bFound and x < iItems
If oEvent.Source.getItem(x) = sItem Then
'Print “Entered item IS in list at position —> " & x + 4
oSheet = ThisComponent.Sheets.getByName(“Cobranca”)
oCell = oSheet.getCellByPosition(1, x+3)
ThisComponent.getCurrentController().Select(oCell)
CordeFundo = rgb(255,255,0)
Transparencia = False
oCell.CellBackColor = CordeFundo
oCell.IsCellBackgroundTransparent = Transparencia
oController = ThisComponent.getCurrentController()
oForm = oSheet.getDrawpage().getForms().getByIndex(0)
oControl = oForm.getByName(“cbCobranca”)
oControlView = oController.getControl(oControl)
oControlView.Text=""
oControlView.setFocus()
bFound = True
Else
x = x + 1
End if
Wend
ElseIf oEvent.KeyCode < 256 or (oEvent.KeyCode > 265 and oEvent.KeyCode<> 1024_
and oEvent.KeyCode<> 1025 and oEvent.KeyCode<> 1280 and oEvent.KeyCode<> 1283) Then
oEvent.Source.Text=”"
End If
End Sub
.
.
.
The bold one should focus the combo box

this is the macro code to populate combobox
.
.
.
.
Private Sub cbCobranca_GotFocus()
Dim UltLinha As Long
Dim oSheet, oCell, oRange, oCursor as object
Dim LastRow as Long
Dim oForm, oCol, oCombox, oCombobox as Object
Dim ip, i as Integer
Dim sItem as String
Dim lItem as Long
Dim nCount as Long
Dim ListEntries as String

dropbuttonclicked = Not dropbuttonclicked
If dropbuttonclicked = True Then
oSheet = ThisComponent.Sheets.getByName(“Cobranca”)
oCol = oSheet.getCellRangeByName(“B1”) ’ Coluna B
oCursor = oSheet.createCursorByRange(oCol)
oCursor.gotoEndOfUsedArea(False)
LastRow = oCursor.RangeAddress.EndRow + 1
oRange = oSheet.getCellRangeByName(“B” & LastRow)
oForm = oSheet.DrawPage.Forms(0) ‘1° Formulário’
oCombox = oForm.getByName(“cbCobranca”) ‘Nome do combobox’
oCombobox = ThisComponent.getCurrentController.getControl(oCombox)
nCount = oCombobox.getItemCount()
oCombobox.removeItems(0,nCount)
ip = 0
For i = 4 To LastRow
sItem = oSheet.getCellRangeByName(“B” & i).String
oCombobox.addItem(sItem, ip)
ip = ip + 1
Next i
oCombox.LineCount = 30
oComBox.Dropdown = True
oCombobox.DropDownLineCount = 10
End If
End Sub

It looks like your work and the answers you’ve gotten aren’t quite sufficing. Could you upload an ODS with what you have?
Also, can you clarify the desired order of events. I think I get the idea–I did something similar once for scanning MEID/ESN numbers for cellular inventory–but

still isn’t clear about what isn’t happening, given the answers that have been offered. Are you, in fact, wanting the focus because you are using barcode entry without keyboard interaction?

Hello!
Could you upload the Calc document with data and macros?

image
I hit 0028466 on combo box. The macro find it. I hit ENTER. The macro colours the cell background yellow and the macro ends…the combo box doesn´t get focus…I have to scroll up to combo box, I have to click on combo box and I have to enter a new number.

Just a thought…have you considered a modal form/dialog? You could use mostly the same code, right?
Still, uploading an ODS would be nice…

Yes…I believe I can do that…but I want to code like this too…I want to code the control on a sheet, not on a dialog. It should be possible. The problem is “how to get focus on control on a sheet itself”…One more thing…A funny thing…If I code a MSGBOX before the bold code lines…the combo box gets focus.

image
.

.
Now…The combo box gets focus…WEIRD

Now I’m beginning to understand what is happening…
The combo box gets focus, but the macro code doesn’t scroll up to combo box.

1 Like