Good Evening Experts,
I have the following VBA code that works perfectly for opening a drop down menu, within the drop down menu are a set of 'dated' worksheets (weekly dates for the year 2021), and when you click the dated worksheet, it opens the selected worksheet.
The question is, I want to create a second drop down menu on the same worksheet that does what the above does, the Range would be 'I18' however, it will be for a different set of 'dated' worksheets (Weekly dates for the year 2022)
Is is possible to have 2 Change events with the same worksheet and if so how do I write the code??...... or is there an alternative method to what I am trying to achieve?
Any help is much appreciated.
Many Thanks,
Matt
I have the following VBA code that works perfectly for opening a drop down menu, within the drop down menu are a set of 'dated' worksheets (weekly dates for the year 2021), and when you click the dated worksheet, it opens the selected worksheet.
The question is, I want to create a second drop down menu on the same worksheet that does what the above does, the Range would be 'I18' however, it will be for a different set of 'dated' worksheets (Weekly dates for the year 2022)
Is is possible to have 2 Change events with the same worksheet and if so how do I write the code??...... or is there an alternative method to what I am trying to achieve?
Any help is much appreciated.
Many Thanks,
Matt
Excel Formula:
Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ThisWorkbook.Sheets("Work Menu").Range("I14:")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error GoTo Stopsub:
Call ChangeSheet
Stopsub:
Application.EnableEvents = True
End Sub
Sub ChangeSheet()
Dim SelectedSheet As String
SelectedSheet = ThisWorkbook.Sheets("Work Menu").Range("I14")
ThisWorkbook.Sheets(SelectedSheet).Activate
End Sub