Enabling/Disabling Button Based on Condition in Excel VBA

Question

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.

Use and() to test the cells, specify the condition for each: and(A1="yes",B1="Blue") etc — Solar Mike
– Solar Mike, Commented Apr 22, 2022 at 16:46
If you want to respond to changes in cell values then you need to use the Worksheet_Change event. Tons of previous posts here on that topic. — Tim Williams
– Tim Williams, Commented Apr 22, 2022 at 16:54

Tim Williams · Accepted Answer · 2022-04-22 18:28:22Z

1

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

answered Apr 22, 2022 at 18:28

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Enabling/Disabling Button Based on Condition in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related