VBA to auto display drop down list when cell selected

Hi folks

As a relative newbie to VBA, with any queries I have I try a web search, forum search and then I post! I am pretty sure my issue is not unique and I am pretty sure there will be another post alredy listed... I never quite know the right phrase to enter in the search tools to find what I am looking for!

So, after many searches which result in me going nowhere fast, I apologise for the request in advance!

I have some data validation lists to control data quality in my spreadsheet. As with most users, in order to select a response I must firstly click in to the cell, which in turn displays the dropdown handle, then click the handle and finally make a selection from the list displayed.

Please can you assist me with - How can I achieve an automatic display of the dropdown as soon as I click in to the relevant cell, using VBA?

Will the code be cell specific or can it be a more holistic code, which I would imagine slows down the performance?

I hope the fact that I am a Office for Mac user (Office 365 v16.15) does not further complicate matters... most of the users of my creation will be PC users

Thank you in advance

To do this in VBA requires programming to create mouse clicks. It is complicated and in 20 minutes I could not collect enough information to determine the screen coordinates for the click.

If you are just trying to save clicks, you could use comboboxes instead, which displays the dropdown handle all the time, and you can click directly on that.

Originally Posted by 6StringJazzer

To do this in VBA requires programming to create mouse clicks. It is complicated and in 20 minutes I could not collect enough information to determine the screen coordinates for the click.

If you are just trying to save clicks, you could use comboboxes instead, which displays the dropdown handle all the time, and you can click directly on that.

Thank you for the note about combo boxes, I have considered them. Unfortunately there are so many lines in the sheet where I want the functionality it makes the sheet look really messy.

Hi Ed

What is the nature of your data validation?

A List or a value within a range?

Originally Posted by mehmetcik

Hi Ed

What is the nature of your data validation?

A List or a value within a range?

Without sounding strange, both!

The data validation is from a table column. I have created an INDIRECT reference in the data validation to point me back to the table that holds the drop down data (so the user can amend the list at another point in time without the need for my intervention)

Thank you for your response

You can be column Specific

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Then    ' this means column A
        Target.Application.SendKeys ("%{DOWN}")
    End If
End Sub

Or range specific

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.Intersect(Target, Me.Range("E13:J23")) Is Nothing Then
        Target.Application.SendKeys ("%{DOWN}")
    End If
End Sub

The code belongs in the worksheet module.

RightClickTab.jpg

Originally Posted by davesexcel

You can be column Specific

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Then    ' this means column A
        Target.Application.SendKeys ("%{DOWN}")
    End If
End Sub

Or range specific

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.Intersect(Target, Me.Range("E13:J23")) Is Nothing Then
        Target.Application.SendKeys ("%{DOWN}")
    End If
End Sub

The code belongs in the worksheet module.

Attachment 584255

Thank you for responding. Unfortunately neither set of options works. I had found another piece of code with the ("%{DOWN}") reference and the code crashed at that point.

Using your code had no effect when I click the cells... I must be doing something wrong!

I'll do a search, maybe there is a different sendkeys for mac.

Apologies, l I never knew send keys did not work for mac.

If you search "excel sendkeys mac alternative" you could get some results, I can't test them out.

Originally Posted by davesexcel

Apologies, l I never knew send keys did not work for mac.

If you search "excel sendkeys mac alternative" you could get some results, I can't test them out.

Thank you. Does that mean it will work for my Mac but not ofr PC users though?

You could use a selection change macro to open a Userform

Right Click on your sheet name at the bottom of excel and select view code

Paste this code into the module that opens an then close it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Target

On Error GoTo Skip
MyList = .Validation.Formula1

UserForm1.Show

End With
Skip:
On Error GoTo 0

End Sub

Create a Userform containing a list box

Double click on the listbox to view its code/

replace its code with

Private Sub UserForm_Activate()
MyArray = Split(ActiveCell.Validation.Formula1, ",")
ListBox1.List = MyArray
End Sub

Private Sub ListBox1_Click()
ActiveCell.Value = ListBox1.Value
Unload Me
End Sub

Thank you, that does indeed work, just takes time opening and closing, I do appreciate the effort though. There are literally hundreds of these dropdowns.

This Ammended Code works for Lists and Ranges

Private Sub UserForm_Activate()

B = ActiveCell.Validation.Formula1
T = ActiveCell.Validation.Formula2

If T = "" Then

Temp = ActiveCell.Validation.Formula1

Else

B = B * 1
T = T * 1

For Count = B To T
Temp = Temp & Count & ","
Next

End If

MyArray = Split(Temp, ",")
ListBox1.List = MyArray

End Sub

Thank you. I am grateful for all the responses

A Mash Up of Daves Solution and Mine

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo Skip
B = ActiveCell.Validation.Formula1
T = ActiveCell.Validation.Formula2

If T <> "" Then

B = B * 1
T = T * 1

For Count = B To T
Temp = Temp & Count & ","
Next

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Temp
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If

ActiveCell.Application.SendKeys ("%{DOWN}")

Skip:
On Error GoTo 0

End Sub