![]() |
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 |
PROBLEM: hide/unhide worksheets based on cell value change
|
PROBLEM: hide/unhide worksheets based on cell value change
Thank you!! This is far better!
|
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