Is there a way to use a Data Validation List and run a macro that
hides/unhides worksheets based on what value is selected on the drop down
list? If so, what would the syntax look like for the macro code?

Disregard the subject of "Radio Button Macro". I meant to say "Data
Validation Macro".



"Lisa C." wrote:

Is there a way to use a Data Validation List and run a macro that
hides/unhides worksheets based on what value is selected on the drop down
list? If so, what would the syntax look like for the macro code?

You can use a worksheet event that will look for changes to specific cells.

In this example, I used A1.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim sh As Object

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
End If

For Each sh In Me.Parent.Sheets
If sh.Name = Me.Name Then
'skip it. Keep this sheet visible
Else
If LCase(sh.Name) = LCase(Target.Value) Then
'show this one
sh.Visible = xlSheetVisible
Else
sh.Visible = xlSheetHidden
End If
End If
Next sh

End Sub

If you want to try it, rightclick on the worksheet tab that will have this cell
with the data|validation. Select view code and paste that code into the new
code window.



Lisa C. wrote:

Disregard the subject of "Radio Button Macro". I meant to say "Data
Validation Macro".

"Lisa C." wrote:

Is there a way to use a Data Validation List and run a macro that
hides/unhides worksheets based on what value is selected on the drop down
list? If so, what would the syntax look like for the macro code?


--

Dave Peterson

I pasted this into my worksheet code but I don't understand how to modify it
to identify the text value chosen from the data validation list or the sheet
names to hide/show. For example, if there are two values to choose in the
list ("blue" and "green"), and if "blue" is selected which displays the text
"blue" in cell A1, the macro needs to unhide the sheet named 'Blue Sheet' and
hide the sheet named 'Green Sheet'. Likewise, if "green" is selected which
displays the text "green" in cell A1, the macro needs to unhide the sheet
named 'Green Sheet' and hide the sheet named 'Blue Sheet'. As you can tell,
I am an amateur with VB code.

"Dave Peterson" wrote:

You can use a worksheet event that will look for changes to specific cells.

In this example, I used A1.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim sh As Object

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
End If

For Each sh In Me.Parent.Sheets
If sh.Name = Me.Name Then
'skip it. Keep this sheet visible
Else
If LCase(sh.Name) = LCase(Target.Value) Then
'show this one
sh.Visible = xlSheetVisible
Else
sh.Visible = xlSheetHidden
End If
End If
Next sh

End Sub

If you want to try it, rightclick on the worksheet tab that will have this cell
with the data|validation. Select view code and paste that code into the new
code window.



Lisa C. wrote:

Disregard the subject of "Radio Button Macro". I meant to say "Data
Validation Macro".

"Lisa C." wrote:

Is there a way to use a Data Validation List and run a macro that
hides/unhides worksheets based on what value is selected on the drop down
list? If so, what would the syntax look like for the macro code?


--

Dave Peterson

I think the simplest solution would be to just include the actual names in the
data validation list.

Blue Sheet
Green Sheet

It'll make life lots easier--especially when you start naming sheets something
else. And as a user, I would rather see the actual names. (Some day you may
have "Blue Sheet" and "Blue Sheet Backup" and things will get ugly.)

But if you want (and I wouldn't!):

Change this:
If LCase(sh.Name) = LCase(Target.Value) Then
to
If LCase(sh.Name) = LCase(Target.Value & " Sheet") Then

But then your dropdown changes to
Blue
Green

(no extra spaces!)


Lisa C. wrote:

I pasted this into my worksheet code but I don't understand how to modify it
to identify the text value chosen from the data validation list or the sheet
names to hide/show. For example, if there are two values to choose in the
list ("blue" and "green"), and if "blue" is selected which displays the text
"blue" in cell A1, the macro needs to unhide the sheet named 'Blue Sheet' and
hide the sheet named 'Green Sheet'. Likewise, if "green" is selected which
displays the text "green" in cell A1, the macro needs to unhide the sheet
named 'Green Sheet' and hide the sheet named 'Blue Sheet'. As you can tell,
I am an amateur with VB code.

"Dave Peterson" wrote:

You can use a worksheet event that will look for changes to specific cells.

In this example, I used A1.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim sh As Object

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
End If

For Each sh In Me.Parent.Sheets
If sh.Name = Me.Name Then
'skip it. Keep this sheet visible
Else
If LCase(sh.Name) = LCase(Target.Value) Then
'show this one
sh.Visible = xlSheetVisible
Else
sh.Visible = xlSheetHidden
End If
End If
Next sh

End Sub

If you want to try it, rightclick on the worksheet tab that will have this cell
with the data|validation. Select view code and paste that code into the new
code window.



Lisa C. wrote:

Disregard the subject of "Radio Button Macro". I meant to say "Data
Validation Macro".

"Lisa C." wrote:

Is there a way to use a Data Validation List and run a macro that
hides/unhides worksheets based on what value is selected on the drop down
list? If so, what would the syntax look like for the macro code?


--

Dave Peterson


--

Dave Peterson