Form to run Macros based on Check Boxes

[gheyman]

I have 5 checkboxes on a form. With them I have 5 separate Macro's.

What's the best way to run each macro if a box is checked (True).

I started with an If statement but then realized it would get crazy long and complicated. I assume some type of loop but have no idea how to.

Thanks

 

[dreid1011]

You could use a button and loop through the check boxes and check their value.

Then, perhaps use a Select Case or If to determine which macro(s) to run.

In fact, I am doing this right now with a sheet of mine.

The red lines pertain to identifying the check boxes as the loop runs.

Private Sub ImportButton_Click()
Dim i As Long
Dim ws As Worksheet, wbImport As Workbook
Dim CBArr() As String, RngArr() As String, FName As String
CBArr() = Split("CBDaniel|CBKatrina|CBSaundra|CBMatthew|CBKasandra", "|")
RngArr() = Split("D21|D22|D23|D24|D25", "|")
Set ws = Sheets("Daily Detail")
For i = 0 To 4
FName = "G:\TAX\BALANCE\Z   Daily Reports to Assemble\Daily Detail Worksheets\Z Checks-" & Right(CBArr(i), Len(CBArr(i)) - 2) & ".xlsm"
If Controls(CBArr(i)).Value = True Then
    If IsFileOpen(FName) Then
        MsgBox "Z Checks-" & Right(CBArr(i), Len(CBArr(i)) - 2) & ".xlsm is open!"
        Exit Sub
    Else
         Set wbImport = Workbooks.Open(FName)
         With wbImport.Worksheets("Daily Input")
            If .Range("F1").Value = Date Then
            '-----------------------------------------------------
                ws.Range(RngArr(i)).Value = .Range("E7").Value
            Else
                ws.Range(RngArr(i)).Value = 0
            End If
         End With
         wbImport.Close
    End If
Else
    ws.Range(RngArr(i)).Value = 0
End If
Next i
End Sub

 

[mikerickson]

You could use something like

Select Case CheckBox1.Value & CheckBox2.Value & CheckBox3.Value & CheckBox4.Value & CheckBox5.Value
    Case "FalseFalseFalseFalseFalse"
        ' Do something
    Case "FalseFalseFalseFalseTrue"
        ' do something else
' etc
End Select

Or another approach would be to combine the Checkbox results into a binary number and then look at that number to see what is checked.

Dim checkState as Long
checkState = CLng(CheckBox1.Value)
checkState = checkState + 2 * CLng(CheckBox2.Value)
checkState = checkState + 4 * CLng(CheckBox3.Value)
checkState = checkState + 8 * CLng(CheckBox4.Value)
checkState = checkState + 16 * CLng(CheckBox5.Value)
checkState = -1 * checkState

If CBool(checkState And 4) Then MsgBox "checkbox3 is checked"

' etc.

 

[My Aswer Is This]

Why not run your Macros from a list of Macros loaded into a Combobox

In you Userform put this script sample:
It will load your macro names into your Combobox
This script loads two macro names into Combobox2:
The macro names are Yes and No

Private Sub UserForm_Initialize()
With ComboBox2
.AddItem "Yes"
.AddItem "No"
End With
End Sub

Then put this script into your Combobox

Private Sub ComboBox2_Change()
On Error GoTo M
Application.Run ComboBox2.Value
Exit Sub
M:
MsgBox "There is no  Macro named  " & ComboBox2.Value
End Sub