Excel Command Button - Hide multiple sheets based on IF statement
I have a sheet in a workbook that acts as a sort of table of contents.
All of the sheets in the workbook are listed in a column and the column to the leftof each worksheet name is either blank or contains an ‘X’ depending on whether the sheet was used on a particular occasion.
I have a command button on the page that will hide a sheet if a particular cell is empty. Currently this only works for one worksheet.
What I would like to do is have the code check each cell in range A9 to A46 and hide if necessary the corresponding sheet for each row if the cell is empty.
Below is what I have so far...
Code Snippet
Private Sub CommandButton1_Click()
If Range("A9") = "" Then
Worksheets("020-100F1").Visible = False
Else
Worksheets("020-100F1").Visible = True
End If
End Sub
I have so many sheets and I'm not sure how to takle this.
Just for reference...
Cell Sheet
A9 020-100F1
A10 020-100F2
A11 020-100F3
and so on...
Is there an easy way to do this?
[1583 byte] By [
suznal] at [2008-1-10]
Hi,
You need to loop through the range of cells.
This will set the visible property depending on the length of the cells content.
The sheet name is picked up using the Offset method.
Private Sub CommandButton1_Click()
Dim rngTemp As Range
' loop through a range of cells
For Each rngTemp In Range("A9:A46").Cells
' test each cell for some content
Worksheets(rngTemp.Offset(0, 1)).Visible = (Len(rngTemp.Value) > 0)
Next
End Sub
After inserting the code, calling the command gives me a "Type Mismatch" error on line...
Code Snippet
Worksheets(rngTemp.Offset(0, 1)).Visible = (Len(rngTemp.Value) > 0)
I can see that it calls up the correct sheet name, but other than that I am lost.
I tried it on a new book, but the error still comes up.
I'm lost...
The will stop the error. If any of the cells in the range have empty cells where the sheetname should be you will need to add code the check otherwise you will get a subscript error.
Worksheets(rngTemp.Offset(0, 1).Value).Visible = (Len(rngTemp.Value) > 0)
This works perfectly with only one problem. It does not seem to affect the first cell in the range "A9".
I did have to modify the range (as shown below) as there are rows that are filled in to act as borders, therefore there are breaks in the range.
Code Snippet
Private Sub CommandButton1_Click()
Dim rngTemp As Range
For Each rngTemp In Range("A9:A15,A18:A29,A31:A35,A37:A38,A40,A43:A45").Cells
Worksheets(rngTemp.Offset(0, 1).Value).Visible = (Len(rngTemp.Value) > 0)
Next
End Sub
However, no matter what the value of "A9" the sheet is never hidden, all other sheets work fine. The sheet is referenced correctly in the corresponding cell, yet it doesn't seem to be affected.
I have marked this as answered. I have tried this a few times on new workbooks and it works wonderfully.
There has to be something that I cannot see preventing it from working on A9 on my original workbook. I've tried everything but can't seem to find what it might be.
If anyone has any insight into what might be causing the error, please let me know. Most likely it is something extremely simple that I am missing.
Hi,
Try this modification to handle spaces.
Private Sub CommandButton1_Click()
Dim rngTemp As Range
For Each rngTemp In Range("A9:A15,A18:A29,A31:A35,A37:A38,A40,A43:A45").Cells
Worksheets(rngTemp.Offset(0, 1).Value).Visible = (Len(Trim(rngTemp.Value)) > 0)
Next
End Sub
If you want to email the problem workbook I will take a look.
andy AT andypope DOT info
I knew it was something simple!
It took me a while but I found that the worksheet referenced on row 9 was also referenced towards the end of the list! I had a value entered in the cell towards thge bottom but not in row 9, so it hid and unhid the sheet! That's why it didn't seem to work.
As I said before Andy, the code worked beautifully, there was just a simple error I was overlooking.
Thanks!
