Auto Show Drop-Down List When Selecting the Cell

**Anshu** · 09-04-2020, 01:26 PM

Moderator Notice:
Solved by Peter SSs here:
https://www.mrexcel.com/board/thread...-cell.1144911/

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. (Please see the attachment)
Clicking.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. Selection.png

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

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!!

**DocAElstein** · 09-05-2020, 04:30 PM

Hi,
From your other Thread, ( https://excelfox.com/forum/showthrea...ll=1#post14871 )

Originally Posted by DocAElstein

....I did just record a macro whilst selecting the arrow on a drop down list. Unfortunately , no coding is produced by this: Not all actions taken manually produce coding by the macro recorder. So this does not help us. ....

I am 90% sure that we cannot automate with VBA the clicking of the arrow.
You could possibly check by asking the question at some other places / Forums., just to be sure. I may be wrong because I have not done much with drop down validation things

The best I can offer is a macro which will give you a message box for a few seconds to tell the person clicking the cell to select the arrow.

This would need to go in a normal macro module

Code:

' Pseudo Non Modal MsgBox, MessageBoxA API Standard Non Standard Stuff, More Fundamentally complicated UnWRap it and.. "Pseudo Non Modal MsgBox" --- A valid handle, hWnd, other than the Excel spreadsheet window ( Private Declare Function FindWndNumber Lib "user32" Alias "FindWindowA" (Optional ByVal lpClassName As String, Optional ByVal lpWindowName As String) As Long --- hWndParent = FindWndNumber(lpClassName:="XLMAIN", lpWindowName:=vbNullString) ), or even no ( Null ) hWnd results in a pseudo Non Modal MsgBox http://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10476#post10470 http://www.tek-tips.com/faqs.cfm?fid=4699
Public Declare Function APIsinUserDLL_MsgBox Lib "user32.dll" Alias "MessageBoxTimeoutA" (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal uType As Long, Optional ByVal wLanguageID As Long, Optional ByVal Delay_ms As Long) As Long   '     https://www.excelforum.com/development-testing-forum/1215283-gimmie-da-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u.html#post4822413

This would need to go in the worksheets code module

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$B$3" Then
     APIsinUserDLL_MsgBox hWnd:=WndNumber, Prompt:="Please click Arrow shown on right side of the cell", Title:="NonModalPopUpThingy", Delay_ms:=2000  '
    Else
    
    End If
End Sub

Alan

**Anshu** · 09-05-2020, 05:00 PM

Thank you for the help. As suggested, and in order to get the solution positively, I've posted this thread here,too...https://www.mrexcel.com/board/thread...-cell.1144911/

**Anshu** · 09-06-2020, 05:27 AM

SOLVED!
The issue is resolved here https://www.mrexcel.com/board/thread...-cell.1144911/

Thanks!

**DocAElstein** · 09-06-2020, 01:24 PM

Thanks for letting us know.
This is good news.
It is very interesting. I just tried it, and it works for me too, in Excels 2003 2007 and 2010
I would never have thought it possible!
I am always very pleased when I am proved wrong about something in VBA, because it usually reveals something very interesting and useful. It does not happen very often, but when it does it usually reveals something very interesting and useful for me also. It is nice to be in a position to allow me to look forward to being proved wrong. ( I have seen that most professional programmers, and senior forum members get very upset when they are proved wrong, and so try to hide it, which often means that interesting things like this never get to be seen. I find that very sad, but it seems that most senior professional programmes and senior forum members seem to have mental problems)

Note I have seen many professionals warn against using Send Keys since they say it can be unstable and lead to some nasty crashes. But I use Send keys also ( to automate opening the VB Editor and from that the Immediate window) and so far I have not experienced any problems.

( If the solution works well for you then that might give you some new ideas for your Horizontal Tree structure idea )

( I would love to understand how Peter’s macro works, as I would like to learn more about Send Keys. Unfortunately there are so many things to learn about VBA that there is not enough time in a life time. ( Many people who will give you many great codings are strangely reluctant to explain them… ) )