Dec 05 2019 06:46 AM
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?
Dec 05 2019 07:39 AM
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.
Dec 05 2019 08:30 AM
Dec 05 2019 08:35 AM
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?
Dec 05 2019 08:54 AM
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.
Dec 05 2019 09:09 AM
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?
Dec 05 2019 09:14 AM
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.
Dec 05 2019 09:18 AM
Dec 05 2019 09:50 AM
SolutionDec 05 2019 09:59 AM
The first one I tried was the V6 and it works brilliantly! You are a genius!
Thanks ever so much,
T
Dec 05 2019 10:16 AM
Dec 05 2019 02:21 PM
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
T
Dec 05 2019 05:09 PM
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...
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
Dec 06 2019 06:51 AM
Oct 13 2021 09:50 AM
Dec 05 2019 09:50 AM
SolutionI am attaching here three versions, see which one works for you.