Set Focus to Worksheet Combobox

Jaamie

Board Regular
Joined
Apr 16, 2003
Messages
219
Office Version
  1. 2016
Platform
  1. Windows
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??
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,805
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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.
VBA Code:
' 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

Board Regular
Joined
Apr 16, 2003
Messages
219
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,805
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
It's known that SendKeys is not stable, try this:
VBA Code:
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

Board Regular
Joined
Apr 16, 2003
Messages
219
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,805
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
You are welcome and thank you for the feedback 🙂
 

Watch MrExcel Video

Forum statistics

Threads
1,123,487
Messages
5,601,970
Members
414,487
Latest member
inxlsplot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top