suznal


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 left of 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





Re: Excel Command Button - Hide multiple sheets based on IF statement

Andy Pope


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







Re: Excel Command Button - Hide multiple sheets based on IF statement

suznal

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...







Re: Excel Command Button - Hide multiple sheets based on IF statement

Andy Pope

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)





Re: Excel Command Button - Hide multiple sheets based on IF statement

suznal

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.




Re: Excel Command Button - Hide multiple sheets based on IF statement

Andy Pope

Are you sure the cell to the left is completely empty A space will produce a len of more than zero.





Re: Excel Command Button - Hide multiple sheets based on IF statement

suznal

Yes the cell is empty. I 'cleared contents' and well as replaced " " (a space) with 'no space'. It is indeed empty.

Might there be other causes






Re: Excel Command Button - Hide multiple sheets based on IF statement

suznal

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.






Re: Excel Command Button - Hide multiple sheets based on IF statement

Andy Pope

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





Re: Excel Command Button - Hide multiple sheets based on IF statement

suznal

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!