ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   PROBLEM: hide/unhide worksheets based on cell value change (https://www.excelbanter.com/excel-programming/362848-problem-hide-unhide-worksheets-based-cell-value-change.html)

[email protected]

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


Don Guillett

PROBLEM: hide/unhide worksheets based on cell value change
 
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

wrote in message
oups.com...
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




[email protected]

PROBLEM: hide/unhide worksheets based on cell value change
 
Thank you!! This is far better!


Don Guillett

PROBLEM: hide/unhide worksheets based on cell value change
 
glad to help

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Thank you!! This is far better!





All times are GMT +1. The time now is 12:02 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com