Enable Button only if all the checkboxes are ticked - VBA

[Wafee]

I have a vba code that has been assigned to a button named as "run report". Have some instructions with check boxes and what I am trying to do is to enable button only if all the checkboxes are ticked. I tried something like below but somehow it's not working. It seems something is wrong with declaration.

```
Sub buttonenable()
Dim B1 as button
Dim C1 as checkbox
Dim C2 as checkbox
Dim C3 as Checkbox
Set B1 = Thisworbook.Sheets("Home").Button("Run report")
Set C1 = Thisworbook.Sheets("Home").Checkbox("Checkbox1")

Set C2 = Thisworbook.Sheets("Home").Checkbox("Checkbox2")

Set C3 = Thisworbook.Sheets("Home").Checkbox("Checkbox3")

IF C1 = True and C2 = True and C3 = True Then
B1.Enable = True

Else
B1.enable = False

End If

End Sub()
```

 

[dmt32]

Hi welcome to forum

To enable your Report Button, each checkbox would need to call a code to validate selections all complete.
Another approach would be to place the test at top of your Report Button Code & if checkbox selection incomplete, display msgbox & exit sub

Something like following

Sub RunReport()
Dim cb As CheckBox

'Loop Checkboxes
  For Each cb In ActiveSheet.CheckBoxes
    If cb.Value = xlOff Then
        MsgBox "Select All CheckBoxes To Continue", 48, "Selection Required"
        Exit Sub
    End If
  Next cb
  
  'rest of code

End Sub

I have assumed that your checkboxes are Form controls - If ActiveX different code will be needed

Hope Helpful

Dave

 

[Wafee]

Hi welcome to forum

To enable your Report Button, each checkbox would need to call a code to validate selections all complete.
Another approach would be to place the test at top of your Report Button Code & if checkbox selection incomplete, display msgbox & exit sub

Something like following

Sub RunReport()
Dim cb As CheckBox

'Loop Checkboxes
  For Each cb In ActiveSheet.CheckBoxes
    If cb.Value = xlOff Then
        MsgBox "Select All CheckBoxes To Continue", 48, "Selection Required"
        Exit Sub
    End If
  Next cb
 
  'rest of code

End Sub

I have assumed that your checkboxes are Form controls - If ActiveX different code will be needed

Hope Helpful

Dave

Hi Dave,

Thank you so much that works perfectly fine and sorry as it took time try and get back due to some system issues.

Regards,
Wafee

 

[dmt32]

Hi Dave,

Thank you so much that works perfectly fine and sorry as it took time try and get back due to some system issues.

Regards,
Wafee

no worries - glad suggestion helped

many thanks for feedback

Dave