Enabling/Disabling Button Based on Condition in Excel VBA-CodePudding

I'm having trouble enabling/disabling a button (form control) based on whether all 4 cells are filled in or not (A2, A4, B2, and B4). Here's my code so far, they're all on the same sheet module.

Sub Disable_Button()

Dim myshape As Shape
Set myshape = ThisWorkbook.Worksheets("FORM").Shapes("Button")
With myshape
    .ControlFormat.Enabled = False    '---> Disable the button
    .TextFrame.Characters.Font.ColorIndex = 15    '---> Grey out button label
    .OnAction = ""
End With
End Sub

Sub Enable_Button()

Dim myshape As Shape
Set myshape = ThisWorkbook.Worksheets("FORM").Shapes("Button")
With myshape
    .ControlFormat.Enabled = True    '---> Enable the button
    .TextFrame.Characters.Font.ColorIndex = 1    '---> Highlight button label
    .OnAction = "Module1.TEST"
End With
End Sub

Private Sub Test_Button(ByVal Target As Range)

If Target.Address= 'I'm not sure what to put here since it won't accept multiple ranges
    Application.EnableEvents = False
    If Target.Value = "" Then
    Disable_Button
    Else
    Enable_Button
    End If
    Application.EnableEvents = True
End If
End Sub

Module1.TEST simply outputs "SUCCESS" in cell C1. I've tested both Disable_Button and Enable_Button separately and I've confirmed that they work. However, I'm having trouble with writing code such that the button is only enabled when all 4 cells (A2, A4, B2, and B4) are all filled. I'm not sure if ByVal Target As Range is the way to go. Thank you for any help.

CodePudding user response：

This would go in the worksheet code module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    
    Set rng = Me.Range("A2:B2,A4:B4") 'the cells to monitor
    'check if any of the cells in `rng` are in `Target`
    If Not Application.Intersect(Target, rng) Is Nothing Then
        ToggleButton (Application.CountA(rng) = 4) 'toggle button
    End If
End Sub

'switch button between enabled/disabled according to `bEnable`
Sub ToggleButton(bEnable As Boolean)
    With Me.Shapes("Button")
        .ControlFormat.Enabled = bEnable
        .TextFrame.Characters.Font.ColorIndex = IIf(bEnable, 1, 15)
        .OnAction = IIf(bEnable, "Module1.TEST", "")
    End With
End Sub