Auto Show Drop-Down List When Selecting the Cell

mistersend

New Member
Joined
May 29, 2020
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
Hello!
I have craeted a simple drop down menu with the help of data validation in cell "B3". The list contains 4 options, A,B,C and D.
Click.png


Now, as we all know, If I want to see this 4 option or select any one of them, I must first select the cell "B3". Then a down arrow will appear on right side of the cell.

Only after Clicking this arrow, we can see the list.


My Requirement:
Just after selecting the cell "B3", the drop down list should appear automatically, without clicking the down arrow shown in right side of the cell.


Is there any way, any Macro, anything else to do that??

I do not want to use User Form. It may make the things more complicated, while I want to make the things more easy!

Thanks in Advance!!


(This question is first posted here Auto Show Drop-Down List When Selecting the Cell
But I couldn't get the solution I expected. Hence, I've to post it here)
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Try this worksheet selection change code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lDVType As XlDVType
    
    If Target.Cells.CountLarge = 1 Then
        On Error Resume Next
        lDVType = Target.Validation.Type
        On Error GoTo 0
        If lDVType = xlValidateList Then SendKeys "%{down}"
    End If
End Sub
 

mistersend

New Member
Joined
May 29, 2020
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
Thank you for the code!
The code is working great with unmerged cells.

But it's not working with merged cells, as shown in the picture.
Merged cell.png


Would you please help!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Merged cells often cause difficulties with vba code.

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lDVType As XlDVType

    On Error Resume Next
    If ActiveCell.MergeArea.Address = Selection.Address Then lDVType = ActiveCell.Validation.Type
    On Error GoTo 0
    If lDVType = xlValidateList Then SendKeys "%{down}"
End Sub
 

mistersend

New Member
Joined
May 29, 2020
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
SOLVED!
Thank you so much for the macro!
It works absolutely fine with merged as well as unmerged cells.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,911
Messages
5,627,594
Members
416,255
Latest member
amethystia

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