Forms: Option buttons to hide/unhide a worksheet

[Dumb Bo]

I have a workbook with a number of sheets, two of which are "Details" and "Details (cont)". Based on the inputs in sheet "Details" I would like the second sheet to be visible or hidden.

More specifically, I have created a Group box (from the Forms toolbar) within sheet "Details" and inserted 3 Option boxes ("Yes", "No", and "Not applicable"). These option boxes are linked to cell G1, and return 1, 2 or 3 respectively. I would like second sheet, "Details (cont)", to be hidden if cell G1 within sheet "Details" = 3, i.e. if the user selected "Not applicable". I have done some research and I came up with the following code which I placed in the sheet "Details":

Private Sub worksheet_change(ByVal target As Excel.Range)
    Select Case Worksheets("Details").Range("G1").Value
        Case 3
            Worksheets("Details (cont)").Visible = False
        Case Is <> 3
            Worksheets("Details (cont)").Visible = True
    End Select
End Sub

The problem with the above is that the second sheet is only toggled between hidden and unhidden if I manually edit cell G1 to read "3". Changing cell G1 indirectly via the Option buttons has no effect on whether the sheet "Details (cont)" is hidden or not.

Any help on how I can resolve this would be greatly appreciated.

 

[Smitty]

I'd just put your Select Case statement in a general sub, then assign the code to the Option buttons.

HTH,

 

[Dumb Bo]

I'd just put your Select Case statement in a general sub, then assign the code to the Option buttons.

HTH,

Thanks for the prompt reply. It worked like a charm.

I had initially thought of doing that, but not having ever studied any VBA, it was a case of seeing what I find, and trying to adapt it to my scenario, and I wasn't previously managing it.

Your message prompted me to replace Private Sub worksheet_change(ByVal target As Excel.Range) with Sub Hide(), rather than try to incorporate the two together. Like I said, it worked.

Thanks again for your help!

 

[Dumb Bo]

I have another question related to the above. I have replaced the Option buttons with Check boxes representing "N/A". I have modified the code to require a "TRUE" value, instead of the previous "3".

In most cases I have one sheet per Check box. I however have one instance where two check boxes must refer to the same sheet, and the sheet is to be hidden only in the case of both checkmarks returning a TRUE value. Does anyone know how the code should be modified from the current:

Private Sub worksheet_change(ByVal target As Excel.Range)
    Select Case Worksheets("Details").Range("G1").Value
        Case True
            Worksheets("Details (cont)").Visible = False
        Case Is <> True
            Worksheets("Details (cont)").Visible = True
    End Select
End Sub

 

[Smitty]

In that case you can use an IF statement:

If Sheets("Details").Range("A1") = True And Sheets("Details").Range("B1") True Then...

 

[Dumb Bo]

Thanks Smitty. I'm however not managing to get it to work. I have tried:

Sub Hide()
    If Sheets("Details").Range("G1") = True And Sheets("Details").Range("G2") = True Then
            Worksheets("Details (cont)").Visible = False
    Else
            Worksheets("Details (cont)").Visible = True
    End If
End Sub

I therefore tried to combine elements from both codes, and tried:

Sub Hide()
    If Worksheets("Details").Range("G1").Value = True And Worksheets("Details").Range("G2").Value = True Then
            Worksheets("Details (cont)").Visible = False
    Else
            Worksheets("Details (cont)").Visible = True
    End If
End Sub

Unfortunately neither did this work. The macro runs (i.e. the debugger doesn't stop it indicating an error), however sheet Details (cont) is reamining visible at all times.

Again, I thank anyone in advance for any help, and apologise if these may seem as really basic VBA questions.

 

[Dumb Bo]

On a positive note, from replies to other threads I did manage to add another piece to my own code to hide a particular row in a third sheet in addition to hiding the entire Details (cont) sheet!

Any help with my query on the IF statement is appreciated.

 

[Smitty]

This works fine for me:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> Hide()<br>    <SPAN style="color:#00007F">If</SPAN> Sheets("Details").Range("G1") = <SPAN style="color:#00007F">True</SPAN> And Sheets("Details").Range("G2") = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Worksheets("Details (cont)").Visible = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>            Worksheets("Details (cont)").Visible = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

What value do you expect to be in G1 & G2? If it's not "TRUE" then your code won't respond.

 

[Dumb Bo]

Thanks Smitty. I'm feeling really stupid. In the Sheets("Details") piece I was doing exactly that... Sheets("Details"). I had forgotten that I restructured the workbook a little bit and changed the basic input sheet name to Index!

What can I say? Thanks ever so much for your patience and clarity in responding!