CODEWITHSUNDEEP
excelvba
reallybadatmath
reallybadatmath

Reputation: 315

How to use VBA to show/hide a sheet using option buttons (Worksheet_Change not triggered by option values)

I am trying to show or hide a worksheet based on a cell value. I used to achieve this through a dropdown list, but I am now trying to use option buttons instead. Since changing the approach, my code no longer works.

In my example, the dropdown (JobType) had options Empty, "Type1" and "Type2" as possible values.

Instead of the dropdown, I now have two option buttons, one for "Type1" and "Type2". These are linked to cell G10.

Cell H10 is named JobType and contains the following formula:

=IF(G10=1, "Type1", IF(G10=2, "Type2", ""))

I want MyWorksheet to be visible when the "Type1" or "Type2" buttons are selected, and not visible when they are both cleared.

I am using the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("JobType"), Target) Is Nothing Then

            Application.ScreenUpdating = False
            Worksheets("ThisWorksheet").Visible = False

            Select Case Range("JobType").Value
                Case "Type1"
                    Worksheets("ThisWorksheet").Visible = True
                Case "Type2"
                    Worksheets("ThisWorksheet").Visible = True

            End Select
            Application.ScreenUpdating = True

        End If

End Sub

This worked with the dropdown, but not with the approach I am using now.

I am guessing it has something to do with replacing the direct value in the cell with the formula? Any guidance would be appreciated.

Upvotes: 0

Views: 891

Answers (2)

QHarr
QHarr

Reputation: 84465

If using Option buttons I would add form control options buttons and associate them with the same macro optionButtons. I am not sure however, how you are getting both cleared unless you have another option button(s) i.e. at least an additional one for empty.

Then in a standard module put:

Option Explicit
Public Sub optionButtons()
    Application.EnableEvents = False
    Select Case Application.caller

    Case "Option Button 1", "Option Button 2"
         MsgBox "Unhiding sheet" ' < Do stuff   
         ThisWorkbook.Worksheets("Sheet1").Visible = True
    Case "Option Button 3" '<==Empty option 
         Msgbox "Hiding sheet"   ' .Visible = False
         ThisWorkbook.Worksheets("Sheet1").Visible  = False
    End Select

    Application.EnableEvents = True
End Sub

Upvotes: 2

user4039065
user4039065

Reputation:

A Worksheet_Change is not triggered on a formula's calculation change so Target will never be H10. You need to work with G10 where the values are being set by the option buttons.

You can stack multiple possible results in a Select Case.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("G10"), Target) Is Nothing Then

        on error goto safe_exit

        Select Case clng(target.Value)
            Case 1, 2
                Worksheets("ThisWorksheet").Visible = True
            Case else
                Worksheets("ThisWorksheet").Visible = false
        End Select

    End If

safe_exit:

End Sub

Upvotes: 2

Related Questions