I have a drop down list for the user to choose a particular city. Depending on the city they choose I need for that city's "tab" to simply be displayed.
Display a certain Worksheet based on drop down list
- jetski1202
- Thread is marked as Resolved.
-
-
Re: Display a certain Worksheet based on drop down list
Right click on the main tab and select "View Code", then add this event macro to the editor:
Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) Dim Sh As String If Not Application.Intersect(Target, Range("A1")) Is Nothing Then If Range("A1").Value = "" Then Exit Sub Sh = Range("A1").Value With Sheets(Sh) .Activate .Range("A1").Select End With End If End SubWhere A1 contains your drop down.
-
Re: Display a certain Worksheet based on drop down list
Thank you. I tried that but I get a subscript out of range message. I changed the reference cell to "D30" on the "Main" tab since that is where the drop down list is. When I choose a city from the drop down list (I created that in Data Validation) nothing happens...My drop down has about 20 cities. So if the user chooses Miami, then I need for the tab labeled "Miami" to appear in something like a display box....I hope this makes sense.
-
Re: Display a certain Worksheet based on drop down list
You have to replace 4 occurrances of A1 with D30 in the code.
Are the tab names exactly like the items in the drop down menu?
-
Re: Display a certain Worksheet based on drop down list
Yes they match exactly and I did make sure I changed all of the A1s to D30s....Ok it looks like after I select the city, I have to click around on my Main page and then re-click on the drop down menu and then it switches to the correct tab...Is there anyway to make it so they click an "OK" button to activate the code? Would that be easier?
-
-
Re: Display a certain Worksheet based on drop down list
By chance did you use Worksheet_SelectionChange instead of Worksheet_Change ?
-
Re: Display a certain Worksheet based on drop down list
I used Worksheet_Change
-
Re: Display a certain Worksheet based on drop down list
I have tested a sample workbook, and it works fine with that code...
If you want to post your workbook, (remove data from sheets) we can see if there is something else not allowing it to work at desired...
-
Re: Display a certain Worksheet based on drop down list
NBVC, I got it to work! Thank you sooo much for all your help!
-
Re: Display a certain Worksheet based on drop down list
Great! Glad it worked.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!