VBA Excel setfocus on Dropdown box

Question

I am trying to set the focus in Excel through VBA code towards a dropdown box. I have tried various things which bring unwanted results. The item below is the only one I have got to focus on the drop down box but it is selecting the dropdown as if it was selected under developer (with the resizing dots), whereas I just want it to point to the cell if thats possible. I don't want the user to be able to resize the dropdown or anything and its locked so its weird that it would do that.

ActiveSheet.Shapes("DropDown1").Select

score 2 · Accepted Answer · answered Mar 06 '12 at 06:07

2

Try this

Sub TestFocus()
    Dim ws As Worksheet
    Dim dd As DropDown

    Set ws = ActiveSheet
    Set dd = ws.Shapes("DropDown1").OLEFormat.Object

    dd.TopLeftCell.Select
End Sub

answered Mar 06 '12 at 06:07

chris neilsen

52,446
10
84
123

This seems to only select the upper left cell. I dont think its actually possible to put focus inside the dropdown box.. Thanks – karlstackoverflow Mar 06 '12 at 23:54
Actually it is possible. See the answer I posted. – ChrisB Feb 08 '20 at 00:04

score 0 · Answer 2 · answered Feb 07 '20 at 23:44

Late answer, but since no one actually answered the Op's question I felt I should post this. There is a hidden .Activate method for OLE objects which does not appear in Intellisense as you type.

This code activates the combo-box, assuming the combo-box is on the active worksheet.

Sub Test()
    Dim oleObj As OLEObject
    Set oleObj = Excel.ActiveSheet.OLEObjects.Item("TempCombo")

    oleObj.Activate
End Sub

VBA Excel setfocus on Dropdown box

2 Answers2