I am trying to create a macro that would hide certain sheets based on a drop
down value on the main page. Basically I have main tab, and 4 additional
tabs (Options A, B, C, & D). I have a drop down menu on the main page which
the user chooses which option fits their needs best. Based on that
selection, I want the other tabs to be hidden? Can anyone help?

Thanks!

Mike

Here is an example

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "A"
Worksheets("Sheet1").Visible = xlSheetVisible
Worksheets("Sheet2").Visible = xlSheetHidden
Worksheets("Sheet2").Visible = xlSheetHidden
Case "B"
Worksheets("Sheet1").Visible = xlSheetVisible
Worksheets("Sheet2").Visible = xlSheetHidden
Worksheets("Sheet2").Visible = xlSheetHidden
Case "C"
Worksheets("Sheet1").Visible = xlSheetHidden
Worksheets("Sheet2").Visible = xlSheetHidden
Worksheets("Sheet2").Visible = xlSheetVisible
Case Else
Worksheets("Sheet1").Visible = xlSheetVisible
Worksheets("Sheet2").Visible = xlSheetHidden
Worksheets("Sheet2").Visible = xlSheetVisible

End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Mike Milmoe" wrote in message
...
I am trying to create a macro that would hide certain sheets based on a
drop
down value on the main page. Basically I have main tab, and 4 additional
tabs (Options A, B, C, & D). I have a drop down menu on the main page
which
the user chooses which option fits their needs best. Based on that
selection, I want the other tabs to be hidden? Can anyone help?

Thanks!

Mike

Mike,
I am assuming that the cell that holds the value is named as "Code".
Then you can use this on Change event procedure in your Main sheet
after modifying it as per the comments.

Private Sub Worksheet_Change(ByVal Target As Range)
If IsNull(Intersect(Target, Range("Code"))) Then Exit Sub
Dim OptSheets(1 To 4, 1 To 2) As String
'These are the names of the sheets. First column of the array
OptSheets(1, 1) = "my fist sheet name"
OptSheets(2, 1) = "my second sheet name"
OptSheets(3, 1) = "my third sheet name"
OptSheets(4, 1) = "my fourth sheet name"
'These are the codes corresponding to each sheet. Returned in Main
sheet
OptSheets(1, 2) = "my fist sheet code"
OptSheets(2, 2) = "my second sheet code"
OptSheets(3, 2) = "my third sheet code"
OptSheets(4, 2) = "my fourth sheet code"

For i = 1 To 4
If Range("Code") = OptSheets(i, 2) Then
Sheets(OptSheets(1, 1)).Visible = xlSheetHidden
Else
Sheets(OptSheets(1, 1)).Visible = xlSheetVisible
End If
End Sub

To install: Right-click the sheet tab of Main sheet. Choose View Code.
In the VBA window that appears paste the above code.

HTH
Kostis Vezerides

Mike Milmoe wrote:
I am trying to create a macro that would hide certain sheets based on a drop
down value on the main page. Basically I have main tab, and 4 additional
tabs (Options A, B, C, & D). I have a drop down menu on the main page which
the user chooses which option fits their needs best. Based on that
selection, I want the other tabs to be hidden? Can anyone help?

Thanks!

Mike

I am very new to macros, so I am not sure how to go about this. When I copy
it in as you have laid it out, it does nothing. What piecs of the code you
sent do I need to tailor to my specific spreadsheet?
"vezerid" wrote:

Mike,
I am assuming that the cell that holds the value is named as "Code".
Then you can use this on Change event procedure in your Main sheet
after modifying it as per the comments.

Private Sub Worksheet_Change(ByVal Target As Range)
If IsNull(Intersect(Target, Range("Code"))) Then Exit Sub
Dim OptSheets(1 To 4, 1 To 2) As String
'These are the names of the sheets. First column of the array
OptSheets(1, 1) = "my fist sheet name"
OptSheets(2, 1) = "my second sheet name"
OptSheets(3, 1) = "my third sheet name"
OptSheets(4, 1) = "my fourth sheet name"
'These are the codes corresponding to each sheet. Returned in Main
sheet
OptSheets(1, 2) = "my fist sheet code"
OptSheets(2, 2) = "my second sheet code"
OptSheets(3, 2) = "my third sheet code"
OptSheets(4, 2) = "my fourth sheet code"

For i = 1 To 4
If Range("Code") = OptSheets(i, 2) Then
Sheets(OptSheets(1, 1)).Visible = xlSheetHidden
Else
Sheets(OptSheets(1, 1)).Visible = xlSheetVisible
End If
End Sub

To install: Right-click the sheet tab of Main sheet. Choose View Code.
In the VBA window that appears paste the above code.

HTH
Kostis Vezerides

Mike Milmoe wrote:
I am trying to create a macro that would hide certain sheets based on a drop
down value on the main page. Basically I have main tab, and 4 additional
tabs (Options A, B, C, & D). I have a drop down menu on the main page which
the user chooses which option fits their needs best. Based on that
selection, I want the other tabs to be hidden? Can anyone help?

Thanks!

Mike

This sub will always exit at the first line, since

IsNull(Intersect(Target, Range("Code")))

will always be false. Try

If Intersect(Target, Range("Code")) Is Nothing Then

instead. Make sure you're putting the code in the worksheet code module.


In article . com,
"vezerid" wrote:

Mike,
I am assuming that the cell that holds the value is named as "Code".
Then you can use this on Change event procedure in your Main sheet
after modifying it as per the comments.

Private Sub Worksheet_Change(ByVal Target As Range)
If IsNull(Intersect(Target, Range("Code"))) Then Exit Sub
Dim OptSheets(1 To 4, 1 To 2) As String
'These are the names of the sheets. First column of the array
OptSheets(1, 1) = "my fist sheet name"
OptSheets(2, 1) = "my second sheet name"
OptSheets(3, 1) = "my third sheet name"
OptSheets(4, 1) = "my fourth sheet name"
'These are the codes corresponding to each sheet. Returned in Main
sheet
OptSheets(1, 2) = "my fist sheet code"
OptSheets(2, 2) = "my second sheet code"
OptSheets(3, 2) = "my third sheet code"
OptSheets(4, 2) = "my fourth sheet code"

For i = 1 To 4
If Range("Code") = OptSheets(i, 2) Then
Sheets(OptSheets(1, 1)).Visible = xlSheetHidden
Else
Sheets(OptSheets(1, 1)).Visible = xlSheetVisible
End If
End Sub

To install: Right-click the sheet tab of Main sheet. Choose View Code.
In the VBA window that appears paste the above code.

HTH
Kostis Vezerides

Mike Milmoe wrote:
I am trying to create a macro that would hide certain sheets based on a drop
down value on the main page. Basically I have main tab, and 4 additional
tabs (Options A, B, C, & D). I have a drop down menu on the main page which
the user chooses which option fits their needs best. Based on that
selection, I want the other tabs to be hidden? Can anyone help?

Thanks!

Mike