Unhiding a Specific Number of Worksheets Based On A Cell Value

[NoviceMacroMan]

Hello everybody! I have been attempting to cobble together code from different forums for the past several hours to make a macro to unhide a specific number of worksheets based on a cell value, and have failed miserably thus far. To that end, I thought I would try appealing to you nice folks for assistance before I lose it. Here are the specifics of what I have going on:

Microsoft Excel 2010
Workbook Name: TASERR Model - 06-26-13.xlsm

In this workbook, I have a worksheet called "Input". In cell C21 of that worksheet, workbook users will fill in a number between 1 and 50 (I have the value for that cell limited to those numbers through data validation). Based on the value in that cell (C21), I want to make a macro to unhide that specific number of hidden worksheets. I have 50 hidden worksheets which are named "Route (1)", "Route (2)", "Route (3)", etc.. If the value in cell C21 is 7, I want the worksheets named "Route (1)", "Route (2)", "Route (3)", "Route (4)", "Route (5)", "Route (6)", and "Route (7)" to unhide. If the value in cell C21 is 3, I want the worksheets named "Route (1)", "Route (2)", and "Route (3)" to unhide. I'm sure y'all can see the functionality I'm going for at this point.

I'd be extremely grateful for any help on this one. Thank you kindly (in advance)!

 

[Snakehips]

NoviceMacroMan,

Welcome to MrExcel.

Copy this code to a code module and give it a try....

Sub Unhide()
For c = 1 To Sheets("Input").Range("C21")
Sheets("Route (" & c & ")").Visible = True
Next c
End Sub

Hope that helps.

 

[NoviceMacroMan]

Well, that was so simple it's a little embarrassing

Thank you very much Snakehips!!! I am in your debt kind sir

 

[NoviceMacroMan]

Thank you again for your help! Truly awesome! One more quick question... if I needed that same macro to also be able to hide sheets based on the value in C21 (example: a user initially enters 5 into cell C21; they run the macro and it unhides the worksheets named "Route (1)", "Route (2)", "Route (3)", "Route (4)", "Route (5)" as it should. A moment later, the user realizes they didn't need all 5 sheet, so they change the value in C21 to 4; when they click on the same macro that unhid the 5 sheets, it would be ideal if it could hide the appropriate sheet or sheets based on the value in C21 (which in this example being "Route (1)" through "Route (5)" were visible and the value in C21 is now 4, the macro would hide the worksheet titled "Route (5)", leaving only "Route (1)" through "Route (4)" visible) is that possible?

 

[Snakehips]

NMM,

I've learned plenty down the years through embarrasing moments!!

How's about this approach....

Remove the previous code.

Right click the Input sheet tab and 'View Code'. Paste the below code into that sheet module. Ensure that the line declaring OldNum is at the very top of any code in that sheet module.

Then see what happens when the value in C21 is changed.

Public OldNum As Integer

 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Target.Address = "$C$21" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
OldNum = Target.Value
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrOut
If OldNum < 1 Then OldNum = 1
If OldNum > Target Then
For c = Target + 1 To OldNum
Sheets("Route (" & c & ")").Visible = False
Next c
Else
For c = OldNum To Target
Sheets("Route (" & c & ")").Visible = True
Next c
End If
ErrOut:
On Error GoTo 0
End Sub

 

[NoviceMacroMan]

Thanks for the encouraging words and assistance, Snakehips. My goal is to keep working to get better at this and one day be able to help others on this forum with their issues!

Your code looks beautiful, and is way over my head. So much so that even upon following your instructions (right clicking the Input sheet tab and 'View Code', pasting the code into the sheet module, and ensuring that the line declaring OldNum is at the very top of any code in that sheet module) I have not yet been able to get that macro to show up as under my list of macro choices when I try to assign it to an object (button) my end users will be utilizing... I've gone back to confirm that I saved the code on Sheet 1 (Input) in VBA and everything looks good to me. Above the code I see dropdowns that currently say "(General)" on the left and "(Declarations)" on the right, and the window in question says the file name - Sheet 1 (Code) just above the line with General and Declarations on it. I have this sneaking suspicion I'm yet again missing something terribly obvious and will kick myself when I learn what that happens to be. Any thoughts on where I could have gone awry on this one?

 

[Snakehips]

NMM,

Prepare to kick yourself but maybe, not too hard.

The latest code is code that is triggered by two of the EVENTS that are associated with the worksheet. Selection_Change and Change. If you click Worksheet in the left-hand drop box and then click the right-hand drop box, you will se a list of all the worksheet events that you can take advantage of. Normally there is no code associated with these events but you can add custom code as I have done here.
In broad terms.....
OldNum is a variable with Public scope so it can be read by any of the workbook's macros.
The Worksheet_SelectionChange code is triggered each time a new cell (Target) or range is selected. I have coded to test if it is a) a single cell and b) if it is our cell of interest. If not then just exit the code and do nothing. If it is, then store the current value of our cell C21 in the variable OldNum.
Then I use the Worksheet_Change event, which is triggered every time a value in the sheet is changed either, manually or by code (not by way of a formula in that cell). Again the changing cell is referred to as Target. If the Target is our cell C21 then Target value is the new value of our cell. I have then used the old value, by referring to the Public variable OldNum and the new value, to control the sheet hiding / unhiding.

So you will not see the event codes in a list of macros to be assigned to a button!!! All the above should now occur as and when you change the value in C21!! No need for a button!!!
If this works, but not in a fashion that suit you then it can be altered to work from a button.
If it does not work at all then it's me who needs a kick up the @ss!!

I feel sure you will let me know, either way.

 

[NoviceMacroMan]

Snakehips,

If I understood your last note properly, what you're saying is that the code you wrote is so dummy proof that I don't even need to do anything beyond drop the code into VBA as I previously described and watch the magic happen when I change the value in cell C21. That... is... awesome! I didn't know Excel could be so slick! Unfortunately, I still can't make it work. Here's what I've tried: I dropped the code in as previously described, went to my Input sheet, and changed the value in cell C21 (which was just a number i.e. in no way formula driven). I think at that point the appropriate sheets should be hiding and unhiding based on the number in cell C21, correct? No matter what value I changed cell C21 to be, none of my sheets were hiding or unhiding automatically. Would it be difficult to change this type of code to a button based code?

 

[NoviceMacroMan]

Well, once more I had a kick me moment After we discussed the issue I was having with the previously posted code, I now see the problem was with me not allowing your fantastic code do it's thing. The code works perfectly, better than I would have hoped for! Thank you very much for your patient and expert help, Snakehips!!! I absolutely could not have done it without you. You are a gentleman and a scholar, my friend. Thanks again!
~NoviceMacroMan

 

[s_duggie]

Morning

I have found this thread of help and have used the above macro however I have come across an issue. I have changed the cell that the macro is looking at to $C$14 but when i change the figure in other cells the additional pages are appearing. Any ideas how i can sort this issue? I have copied the macro below so you can see how it looks:

Public OldNum As Integer



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Target.Address = "$C$14" Then Exit Sub
If Target.Cells.Count > 2 Then Exit Sub
OldNum = Target.Value
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrOut
If OldNum < 2 Then OldNum = 2
If OldNum > Target Then
For c = Target + 1 To OldNum
Sheets("Additional (" & c & ")").Visible = False
Next c
Else
For c = OldNum To Target
Sheets("Additional (" & c & ")").Visible = True
Next c
End If
ErrOut:
On Error GoTo 0
End Sub