Set Focus to Worksheet Combobox

[Jaamie]

Once I have made a combobox visible, I would like to set the Focus into it. "SetFocus" or "Activate" don't seem to work on a worksheet Combobox??

 

[ZVI]

Hi.
Instead of Combo Box (Form Control) try using ActiveX Combo Box control from Ribbon - Developer - Insert - ActiveX Controls - ComboBox (ActiveX Control).
Run the below code from the ActiveSheet by Alt-F8.

' Actvate list of ActiveX ComboBox control embedded into Sheet1
Sub Test()
  With Sheet1.ComboBox1 ' or With ActiveSheet.ComboBox1
    .Enabled = True
    .Visible = True
    .Activate
  End With
  Application.SendKeys "%{DOWN}"  '  if needed
End Sub

 

[Jaamie]

ZVI,

Thank you. I am already using the ActiveX ComboBox. All previous efforts using Activate or Setfocus resulted in Error. Your method got the Activate to avoid error. However, I get a blank Combobox and have to left click twice for the list of items to appear. The Sendkey's superimposed a white rectangular area half way up the control box window, and it went to the list when I clicked the box twice. I was hoping to be able to just begin typing without having to click the mouse.

Again, Thank you,

Jaamie

 

[ZVI]

It's known that SendKeys is not stable, try this:

Option Explicit

#If VBA7 Then
  Private Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As LongPtr)
#Else
  Private Declare Sub keybd_event Lib "user32" Alias "keybd_event" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
#End If

' Actvate list of ActiveX ComboBox control embedded into Sheet1
Sub Test()
  With Sheet1.ComboBox1 ' or With ActiveSheet.ComboBox1
    .Enabled = True
    .Visible = True
    .Activate
  End With
  PressAltDown
End Sub

Private Sub PressAltDown()
  Const KEYEVENTF_KEYUP = &H2
  Const VK_MENU = &H12
  Const VK_DOWN = &H28
  DoEvents
  DoEvents
  keybd_event VK_MENU, 0, 0, 0
  keybd_event VK_DOWN, 0, 0, 0
  keybd_event VK_DOWN, 0, KEYEVENTF_KEYUP, 0
  keybd_event VK_MENU, 0, KEYEVENTF_KEYUP, 0
End Sub

 

[Jaamie]

ZVI,

Thank you again. Problem resolved. My code was the same as your Test Sub. Yours worked and mine didn't. Stepping thru I realized that code that I had after the Activate had caused the control to Lose Focus. I placed my Activate code last and it worked!!

I did not need the PressAltDown sub because the Activate caused control to my GotFocus routine which displayed the list. (I just didn't realize it was working until I placed the code last)

Again, Thank you

 

[ZVI]

You are welcome and thank you for the feedback ?