PROBLEM: hide/unhide worksheets based on cell value change

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

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

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
>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
>

Thank you!! This is far better!

glad to help

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> Thank you!! This is far better!
>