Drop down LINKING

[jpbonono]

Hello,

Is it possible to link several sheets by just using a drop down? (even VBA)

Thanks in advance!

Example:

Instead of using a normal hyperlink I'm just gonna use a drop down where after I choose it would automatically redirect me to that sheet?

 

[jpbonono]

up

 

[scott_n_phnx]

You can use a Worksheet_Change event. Something like this, placed in the Worksheet where the drop down is located. I am using the Data Validation with the sheets listed for this.

Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("Q1")) Is Nothing Then
    Worksheets(Target.Value).Select
End If

End Sub

 

[jpbonono]

Hi thank goodness for your reply but how am I going to put the code if I am going to use Data validation? Is it going to work even I am only going to create a new module without using controls?

 

[ghrain22]

Bumping your post after 9 minutes?

VBA Excel Drop-down List for Worksheets - Stack Overflow

 

[scott_n_phnx]

I am not sure what you mean by "how am I going to put the code if I am going to use Data validation?" Using Data Validation, you would just place it into the worksheet module for the sheet. It will not work from another module, sheet or otherwise.


If you are using a ActiveX ComboBox, this works as well:

Private Sub ComboBox1_Change()
Worksheets(ComboBox1.Text).Select
End Sub

This also must be placed into the worksheet module where the combo box is located.

 

[jpbonono]

@Scott
Hi, I apologize for my ignorance but will you please give me the code if the name of the sheets are "Grading 1", "Grading 2", and "Grading 3".

I can easily learn and follow instructions, I just dont know what and where to put those codes though.

@grain
I tried to follow what is instructed on the link but when I tried to debug the code it gives me an error. Maybe because there are other codes that were already in the the workbook module (PrivateSub Workbook_Open()).

Thank you again.

 

[scott_n_phnx]

In regards to your inquiry "please give me the code if the name of the sheets are "Grading 1", "Grading 2", and "Grading 3", are you asking how to get the drop down to list those sheet names, or how to modify the VB codes that I provided? The sheet names can be listed out in cells and then hidden (if using Data Validation). If asking how to modify the code, that is not needed, as it will go to the sheet based on the drop down value.

For placing the code, I am not sure how much work you have done with VB in Excel, so I will walk you through how to use this. For worksheet change codes, you need to place these into the Worksheet module(s). To get to these, beginning from within Excel, hit <b>ALT-F11</b>. From here, you are looking for your Workbook module, which looks like <b>VBAProject(My Workbook.xls).</b> Expand this using the <b>[ + ]</b>. Now you will see your worksheets listed out. Double-click on the worksheet where your drop down is located. This is where you will place the worksheet codes. The worksheet_change events only work on the worksheets where the code is located, no others. You can duplicate this code onto other sheets, but it is a one-for-one deal. Does this make sense?