SOLVED

Using conditional formatting or VBA to automatically open the next drop down menu after selecting ?

Copper Contributor

I am trying to streamline a report format to make it more usable: it has a list of questions followed by a drop down menu - example attached. After selecting an item in the current drop down I would like the next drop down to open automatically. This would speed things up considerably and also possibly ensure the questions are actually dealt with instead of remaining populated with previous selections. 

Is there a way to do this? 

14 Replies

@Trismos 

You may place the following code on Sheet Module to see if this is what you are trying to achieve.

e.g. if you select an answer from the dropdown list in AM4, the dropdown list in AM5 will expand automatically for you to choose an answer.

 

Code on Sheet Module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Variant

On Error Resume Next
n = Target.Validation.Type
On Error GoTo 0

If n = 3 Then
    n = 0
    On Error Resume Next
    n = Target.Offset(1, 0).Validation.Type
    On Error GoTo 0
    If n = 3 Then
        Target.Offset(1, 0).Select
        SendKeys "%{Down}"
    End If
    NUM_On
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
NUM_On
End Sub

 

Code on a Standard Module like Module1:

Private Declare Sub keybd_event Lib "user32" ( _
ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)
Private Const VK_NUMLOCK = &H90
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer

Sub NUM_On()
If Not (GetKeyState(vbKeyNumlock) = 1) Then
keybd_event VK_NUMLOCK, 1, 0, 0
keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End If
End Sub

 

SendKeys method may mess up with the Numlock. Though I have tried to handle that, it is inconsistent to work the same way. See if this is something you can work with.

 

 

 

@Subodh_Tiwari_sktneer 

 

Thanks very much for the reply. I will give this a whirl!

 

Kind regards,

T

@Subodh_Tiwari_sktneer 

 

I am a rank amateur with Excel. I can find my way around the basics but VBA is still a bit of black magic. I received the attached error message and haven't gone further with it to avoid making errors. Advice?

@Trismos 

That means you are using 64 bit of Excel. I am using 32 bit of Excel.

Please find the attached and let me know if that works without producing an error on your end.

 

@Subodh_Tiwari_sktneer 

 

No sir. I still get an error message. Your first example had two sets of code. One seemed error free. (Actually there is a compiler error - Private Sub Worksheet_SelectionChange(ByVal Target As Range)   Please elucidate? 

@Trismos 

I forgot to tweak something. Please check the attached and see if this works now.

Actually since I am using 32 bit version, it works on my end.

 

 

best response confirmed by Trismos (Copper Contributor)
Solution

@Trismos 

I am attaching here three versions, see which one works for you.

 

 

@Subodh_Tiwari_sktneer 

 

The first one I tried was the V6 and it works brilliantly! You are a genius! 

Thanks ever so much, 

T

@Trismos 

You're welcome! Glad it worked as desired in the end.

Thanks for your feedback!

@Subodh_Tiwari_sktneer 

 

Question good sir: does the VBA code you wrote apply specifically to the sheet it was written for? What we have done is created inspection reports that look much like what you and I have been tinkering with, except it's over up to a dozen or so separate sheets, some not so tidily arranged - ie some have Comment sections and or charts between different sections of questions. Can this code be adapted to work for a whole workbook or does one need to insert this code into each sheet?

 

Regards

@Trismos 

If you want this functionality on each sheet of the workbook, you don't need to place the change event and selection change event codes on sheet modules of each sheet.

 

For that case, place the following codes on ThisWorkbook Module and to do so, follow these steps...

 

  1. First delete the any existing codes from sheet modules.
  2. On VB Editor window, double click ThisWorkbook in the project explorer pane, you will find it on left side of the VB Editor and paste the code given below into the opened code window.

In the attached, I have placed the codes on ThisWorkbook Module and created some duplicate sheets. So now the code works for all the sheets in the workbook.

 

Code on ThisWorkbook Module:

 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim n As Variant

On Error Resume Next
n = Target.Validation.Type
On Error GoTo 0

If n = 3 Then
    n = 0
    On Error Resume Next
    n = Target.Offset(1, 0).Validation.Type
    On Error GoTo 0
    If n = 3 Then
        Target.Offset(1, 0).Select
        SendKeys "%{Down}"
        SendKeys "{NUMLOCK}", True
    End If
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
SendKeys "{NUMLOCK}", True
End Sub

 

Good morning Subodh_Tiwari_sktneer
I will try this later today. Thanks ever so much for your help. As an aside I'm a little put off by my lack of knowledge regarding this. I have 2 weeks coming up where I will be stuck with my in-laws so I do believe I will take the opportunity to become at least somewhat proficient in understanding VBA and macros.
I am so hoping that your spouse and/or in-laws never saw this post.
1 best response

Accepted Solutions
best response confirmed by Trismos (Copper Contributor)
Solution

@Trismos 

I am attaching here three versions, see which one works for you.

 

 

View solution in original post