WorkSheet_Change

Respin

New Member
Joined
Mar 2, 2010
Messages
7
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

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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("I14,I18")) Is Nothing Then
      Sheets(CStr(Target.Value)).Activate
   End If
End Sub
 
Upvote 0
Solution
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("I14,I18")) Is Nothing Then
      Sheets(CStr(Target.Value)).Activate
   End If
End Sub

Fluff, Thank you soo much! That worked perfectly!
You are an amazing person for taking the time to answer my question, it was bugging me not being able to do it and you have made my day. :)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top