asked on

Excel VBA - InCell Dropdown - open with VBA

I have this. When I double-click a cell an InCell Dropdown is created. That works fine.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Call AddInCellDropdown
    
End Sub

Open in new window

and

Sub AddInCellDropdown()

    Dim str As String
    Dim rst As ADODB.Recordset

    str = "one, two, three" 'list of dropdown items
    
    'put in cell
    With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
             Formula1:=str
        .IgnoreBlank = True
        .InCellDropdown = True
    End With

End Sub

Open in new window

That works fine. But the cell is selected in 'edit mode', because of the double-click I guess.

I'd like it ...
1. to come out of the edit mode, like hitting Esc. (in causes an error also when I select an item from the dropdown). Tried recording a macro but no code is written.
AND
2. to open the dropdown. ie. to leave the dropdown open.

Thanks!

[ fanpages ]

Hi,

Do you see the same issue(s) with this additional statement?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Call AddInCellDropdown
    
    Cancel = True ' *** Add this line

End Sub

Open in new window

Tackling your two points may mean a different approach; I just wish to see if I understand your requirements completely.

hindersaliva

ASKER

Fanpages, that works perfectly! Thanks!

now for the second part ....

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

[ fanpages ]

PS. Also a suggestion to stop a potential run-time error...

Sub AddInCellDropdown()

    Dim str As String
    'Dim rst As ADODB.Recordset                         ' *** Removed as not required in this cut-down version

    str = "one, two, three" 'list of dropdown items
    
    Selection.Validation.Delete                         ' *** Suggested additional statement
    
    'put in cell
    With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
             Formula1:=str
        .IgnoreBlank = True
        .InCellDropdown = True
    End With

End Sub

Open in new window

hindersaliva

ASKER

Works perfectly! Man, you're a genius!
THANKS!

[ fanpages ]

No problem at all.

If you wish to read-up on "SendKeys", here is a suitable source of information:

[ https://msdn.microsoft.com/en-us/library/office/gg278655%28v=office.15%29.aspx ]

Subodh Tiwari (Neeraj)

+1 from me too. :)

[ fanpages ]

Thanks sktneer :)