PROBLEM: hide/unhide worksheets based on cell value change

[cydenney]

I have a drop down list that allows the user to pick a number 1-10.
Based on that number, I want to hide/unhide the appropriate sheets
(e.g. 4 reveals "Country 1", "Country 2", "Country 3", and "Country 4"
sheets). The code i have so far is below. The if statement is currently
not working, but I also want to know if there is a simplier way to code
this.

BONUS: Add a hide/unhide row command to go along with the hide/unhide
sheet action

Thanks!!!
-----------------------------------------


Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Target.Address = "$A$1" Then
'Ensure target is a number
If IsNumeric(Target) Then
'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so as to avoid putting the code
into a loop.
Application.EnableEvents = False

If Target = 1 Then
Sheets("Country 1").Visible = True
Sheets("Country 2").Visible = False
Sheets("Country 3").Visible = False
Sheets("Country 4").Visible = False
Sheets("Country 5").Visible = False
Sheets("Country 6").Visible = False
Sheets("Country 7").Visible = False
Sheets("Country 8").Visible = False
Sheets("Country 9").Visible = False
Sheets("Country 10").Visible = False
Else
If Target = 2 Then
Sheets("Country 1").Visible = True
Sheets("Country 2").Visible = True
Sheets("Country 3").Visible = False
Sheets("Country 4").Visible = False
Sheets("Country 5").Visible = False
Sheets("Country 6").Visible = False
Sheets("Country 7").Visible = False
Sheets("Country 8").Visible = False
Sheets("Country 9").Visible = False
Sheets("Country 10").Visible = False
Else
If Target = 3 Then
Sheets("Country 1").Visible = True
Sheets("Country 2").Visible = True
Sheets("Country 3").Visible = True
Sheets("Country 4").Visible = False
Sheets("Country 5").Visible = False
Sheets("Country 6").Visible = False
Sheets("Country 7").Visible = False
Sheets("Country 8").Visible = False
Sheets("Country 9").Visible = False
Sheets("Country 10").Visible = False
Else
If Target = 4 Then
Sheets("Country 1").Visible = True
Sheets("Country 2").Visible = True
Sheets("Country 3").Visible = True
Sheets("Country 4").Visible = True
Sheets("Country 5").Visible = False
Sheets("Country 6").Visible = False
Sheets("Country 7").Visible = False
Sheets("Country 8").Visible = False
Sheets("Country 9").Visible = False
Sheets("Country 10").Visible = False
Else
If Target = 5 Then
Sheets("Country 1").Visible = True
Sheets("Country 2").Visible = True
Sheets("Country 3").Visible = True
Sheets("Country 4").Visible = True
Sheets("Country 5").Visible = True
Sheets("Country 6").Visible = False
Sheets("Country 7").Visible = False
Sheets("Country 8").Visible = False
Sheets("Country 9").Visible = False
Sheets("Country 10").Visible = False
Else
If Target = 6 Then
Sheets("Country 1").Visible = True
Sheets("Country 2").Visible = True
Sheets("Country 3").Visible = True
Sheets("Country 4").Visible = True
Sheets("Country 5").Visible = True
Sheets("Country 6").Visible = True
Sheets("Country 7").Visible = False
Sheets("Country 8").Visible = False
Sheets("Country 9").Visible = False
Sheets("Country 10").Visible = False
Else
If Target = 7 Then
Sheets("Country 1").Visible = True
Sheets("Country 2").Visible = True
Sheets("Country 3").Visible = True
Sheets("Country 4").Visible = True
Sheets("Country 5").Visible = True
Sheets("Country 6").Visible = True
Sheets("Country 7").Visible = True
Sheets("Country 8").Visible = False
Sheets("Country 9").Visible = False
Sheets("Country 10").Visible = False

End If
End If
End If
End If
End If
End If
End If



'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0
End If
End If

End Sub

 

[Don Guillett]

something like this might help

For Each Sh In Worksheets' gotta have one visible
If Sh.Name <> "Sheet1" Then Sh.Visible = False
Next

For i = 1 To target
Sheets("country " & i).Visible = True
Next

 

[cydenney]

Thank you!! This is far better!

 

[Don Guillett]

glad to help