How to store multiple values from checkboxes for later use?

[chuckles1066]

I'll try and explain.

I have a userform where there are eight possible options - only one of the options can be selected at any time (the rest are flagged as disabled when one becomes enabled).

I then grab which box has been ticked, feed that into a variable and then run a For/Next loop to cycle through another sheet and extract the relevant data.

Easy.

Now I've been asked if it's possible for more than one checkbox to be selected.....that bit is easy but how do I store multiple values? In an array of some sort?

As an example, let's assume the user has selected London, New York and Paris. I need to be able to run my For / Next loop for London, then for New York and then for Paris.

All help appreciated.

(Why do people always want to mess with something that works perfectly? Grrrrr.....)

 

[mikerickson]

Something like

Dim CheckboxSummary as String, i As Long

For i = 1 to 8
    CheckBoxSummary = CheckBoxSummary & CStr(-1 * CLng(Userform1.Controls("Checkbox" & i).Value))
Next i

If you want to be obscure and assembly languagey you could use

Dim CheckboxSummary as Byte, i As Long

For i = 1 to 8
    CheckBoxSummary = CheckBoxSummary Or (2^(i-1) * - Userform1.Controls("Checkbox" & i).Value)) )
Next i

 

[chuckles1066]

Thanks for that, I understand none of it but, hey, I'm here to learn!

So where in that code should I call my For/Next routine?

 

[mikerickson]

Let's talk about the first (string) version.
What it is doing is constructing a string of 0 and 1 showing which checkbox is checked.

"00000000" would mean than none were checked.
"10100000" means the first and third checkboxes are checked.

Where to put it and how to use it would depend on the overall structure of you project.
Is the user making their selections, dismissing the userform and then acting based on the selections?
or
is the action occuring before the userform is dismissed?

Also, what kind of userform, modal or modeless? How is the the UF dismissed, hidden or unloaded?

These are the questions one would ask when approaching the redsign of the project.

(I'm trying to gently explain that while I have the time to explain how to consolidate entries into multiple checkboxes, I'm not interested in helping redesign the architecture of your company's entire code.)

 

[chuckles1066]

Let's talk about the first (string) version.
What it is doing is constructing a string of 0 and 1 showing which checkbox is checked.

"00000000" would mean than none were checked.
"10100000" means the first and third checkboxes are checked.

Where to put it and how to use it would depend on the overall structure of you project.
Is the user making their selections, dismissing the userform and then acting based on the selections?
or
is the action occuring before the userform is dismissed?

Also, what kind of userform, modal or modeless? How is the the UF dismissed, hidden or unloaded?

These are the questions one would ask when approaching the redsign of the project.

(I'm trying to gently explain that while I have the time to explain how to consolidate entries into multiple checkboxes, I'm not interested in helping redesign the architecture of your company's entire code.)

Thanks for your input.

The user makes their decisions, clicks an OK button, the form is unloaded and the main code then runs driven by which checkbox value is true (I have eight variables cb1v thru cb8v where cb1v = checkbox1.value, cb2v = checkbox2.value etc and these variables are declared publicly so that they are available to the main code).

I don't know about modal or modeless - I just load the userform!

 

[mikerickson]

Since you are Unloading the form, you could use a Public variable to pass the data.

In the Userform, the OK button could have code like this, that sets the value of the variable according to how the checkboxes are selected.

Private Sub OK_Button_Click()
    Dim i As Long
    
    Rem do stuff
    
    CheckBoxState = 0
    With Me
        For i = 1 To 8
            CheckBoxState = CLng(-.Controls("CheckBox" & i).Value) * (2 ^ i) + CheckBoxState
        Next i
    End With
    
    Unload Me
End Sub

In a normal module, the Function WasCheckBoxClicked is used to interpret that variable.

Sub test()

    UserForm1.Show
    
    If WasCheckBoxClicked(3) Then
        MsgBox "Checkbox 3 was selected."
    Else
        MsgBox "Checkbox 3 not." & vbCr
    End If
        
End Sub

Function WasCheckBoxClicked(N As Long)
    WasCheckBoxClicked = CBool((2 ^ N) And CheckBoxState)
End Function

 

[chuckles1066]

Thank you for your time, I am going to implement the code tomorrow.

 

[chuckles1066]

In a normal module, the Function WasCheckBoxClicked is used to interpret that variable.

Sub test()

    UserForm1.Show
    
    If WasCheckBoxClicked(3) Then
        MsgBox "Checkbox 3 was selected."
    Else
        MsgBox "Checkbox 3 not." & vbCr
    End If
        
End Sub

Function WasCheckBoxClicked(N As Long)
    WasCheckBoxClicked = CBool((2 ^ N) And CheckBoxState)
End Function

I assume I can do something like?

For counter = 1 to 8
Select Case WasCheckBoxClicked(counter)
      Case True
          'do code
End Select
Next counter

 

[mikerickson]

I would do that by

For counter = 1 to 8
    If WasCheckBoxClicked(i) Then
        'do same code for every checkbox
    End If
Next counter

If you have a different macro for each checkbox, then code like this could be used

Dim counter As Long
    Dim ArrayOfMacros As Variant

    ArrayOfMacros = Array("Macro0", "Macro1", "Macro2", "Macro3", "Macro4", "Macro5", "Macro6", "Macro7", "Macro8")
   
For counter = 1 to 8
    If WasCheckBoxClicked(i) Then
        Application.Run (ArrayOfMacros(counter))
    End If
Next counter

 

[chuckles1066]

Ahhh, I see.

I try and avoid If / Then scenarios because I'm never 100% sure whether I need an End If?

Sometimes I add one and get a "End If without Block If" error and I get very confused