Worksheet_change working, but won't call a sub routine

63 Views Asked by At

I've been searching for an answer but nothing fits my problem. I have Sheet 1 where I've created a private sub ("disable1") to enable or disable a checkbox based on the value of a cell. I then created a Worksheet_Change in Sheet 1 ("Entry Sheet") to call disable1. I've done this same thing on another workbook and it works fine. If I go into disable1 and click F5, it works like a charm. With it in Worksheet_Change, it does nothing. I checked and Application.EnableEvents is True in the Immediate window. I know I'm in the right worksheet. The macro works when manually forced. I added a msgbox to the Worksheet_Change to confirm it is indeed working. I put the box before the Call disable1 and then after the Call disable1 line. It worked both times. I am at a loss here. What am I doing wrong? (I use the call function in Worksheet_Change to keep activities separated for me to keep track of.)

Private Sub disable1()

    If Sheets("Entry Sheet").Range("J15").Value = "Site Area Emergency" Then
        CheckBox1.Enabled = True
    ElseIf Sheets("Entry Sheet").Range("J15").Value = "General Emergency" Then
        CheckBox1.Enabled = True
    ElseIf Sheets("Entry Sheet").Range("J15").Value = "Alert" Then
        CheckBox1.Enabled = False
    ElseIf Sheets("Entry Sheet").Range("J15").Value = "" Then
        CheckBox1.Enabled = False

    End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


    Call disable1
    MsgBox "changed:" & Target.Address
    
End Sub
2

There are 2 best solutions below

0
On BEST ANSWER

Turns out that the way I'd set up the radio buttons was not making the worksheet see a change was happening. When I changed from linking the properties to a cell to a macro, everything worked for me.

0
On

A Worksheet Change: Toggle CheckBox

enter image description here

Sheet Module, e.g. Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range: Set cell = Me.Range("J15")
    If Intersect(cell, Target) Is Nothing Then Exit Sub
    
    ToggleCheckBox cell, Me.CheckBox1
    
End Sub

Standard Module, e.g. Module1

Option Explicit

Sub ToggleCheckBox( _
        ByVal cell As Range, _
        ByVal cbx As Object)

    Dim CellString As String: CellString = CStr(cell.Value)
    
    Select Case CellString
        Case "Site Area Emergency", "General Emergency"
            cbx.Enabled = True
        Case "Alert", ""
            cbx.Enabled = False
        Case Else
            MsgBox "Invalid string """ & CellString & """.", vbCritical
    End Select

End Sub
  • By putting the ToggleCheckBox procedure in a standard module, you can easily (without specifying the module) utilize it with another worksheet, e.g. Sheet2:

Sheet Module, e.g. Sheet2

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range: Set cell = Me.Range("A1") ' ***
    If Intersect(cell, Target) Is Nothing Then Exit Sub
    
    ToggleCheckBox cell, Me.CheckBox2 ' ***
    
End Sub