Set Focus to Worksheet Combobox

Jaamie

Board Regular
Joined
Apr 16, 2003
Messages
224
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??
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,875
Office Version
  1. 2019
  2. 2016
  3. 2010
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
224
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,875
Office Version
  1. 2019
  2. 2016
  3. 2010
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
224
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,875
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
You are welcome and thank you for the feedback ?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,673
Messages
5,838,705
Members
430,564
Latest member
Raeyven

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