Teaching myself VBA

[lankaw]

Hi Guys ,

I'm new to vba and coding in excel and ive gotten myself into quite the pickle

Simply put , i have 5 check boxes on my user form thereby creating 120 possible combinations which each will give me a combination msgbox of the text fields the check boxes are in front off. .

Is there a easier way to code this without doing 120 nested if's lol.

thanks !

 

[Andrzejek]

If you have:

[pre]
checkbox1[blue]
checkbox2
checkbox3[/blue]
checkbox4[blue]
checkbox5[/blue]
[/pre]

and [blue]BLUE[/blue] checkboxes are checked, what do you want to display in your message box?

Unless I missed your question...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

If you want to display Captions of all your (checked) checkboxes on your UserForm, you may do this:

Dim c As Control
Dim s As String
[green]
'Loop thru all controls on your Form[/green]
For Each c In Me.Controls[green]
    'Detect if control is a checkbox[/green]
    If TypeOf c Is MSForms.CheckBox Then[green]
        'See if the checkbox is checked[/green]
        If c.Value = True Then
            s = s & vbNewLine & c.Caption
        End If
    End If
Next c

If Len(s) > 0 Then
    MsgBox "You have checked: " & s
Else
    MsgBox "Nothing has been checked."
End If

With this code it does not matter how many checkboxes you have. Also, you may add or delete your checkboxes on your UserForm and you don’t have to change this code.

Unless you have some checkboxes that you do not want to take under consideration.



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[combo]

How did you get 120 possible states from 5 checkboxes? As Andy asked, how the message is build depending on checkboxes states? If each checkbox generates separate part of message, you could combine text in one line. If not, maybe other solution will be more conveniant, comboboxes or groups of option buttons?
In case of complicated nested Ifs, I prefer Select Case True...End Select block, it's easier to handle.

combo

 

[softhemc]

I haven't counted them out but I would guess it is possible if you view the checkboxes as a series of bits eg:

NNNNN
YNNNN
YYNNN
YYYNN
YYYYN
YYYYY

NYNNN
NNYNN

etc
etc

Whether or not would expand to 120 I don't know.

 

[strongm]

If the order the checkboxes are selected in changes the text options at each choice, then you'd get 120: 5 x 4 x 3 x 2 x 1

 

[lankaw]

Thanks everyone that replied !

Strongm- yep that's exactly how you come up with 120 combinations with 5 possible variables

Softhemc - for the sake of clarity let say

Check box 1 gives me a msgbox "a"
Check box 2 gives me a msgbox "b"
Check box 3 gives me a msgbox "c"
Check box 4 gives me a msgbox "d"
Check box 5 gives me a msgbox "e"

Pattern would look like...ignore the capitalization on a tablet

abcde
Abcd
ABC
Ab
A
Bcde
Bcd
Bc
B
Etc ...etc so on .


Andy - I will try your suggestion and let everyone know how it goes

 

[combo]

For this problem I would use single listbox with MultiSelect set to Multi, ListStyle can be set to Option, use drag&drop for rearranging list (see for instance

http://dailydoseofexcel.com/archives/2007/01/11/listbox-drag-and-drop/).

With this, the user can see the order and selection of items, one can get selection+order in a single loop through list, if necessary, the list can be easily extended.

combo

 

[MajP]

Either you only have 31 possibilities, or Andy's code does not work for you. In other words does order matter? In Andy's code order does not matter. If they click B then A, or A then B the message will be you clicked A and B. This will not generate two different messages depending on the order. If they click A and B and C you get one message not six possible messages. With Andy's code order does not matter and thus only 31 possibilites.

I may not understand correctly, but I would think
You could pick 1 of 5, 2 of 5, 3 of 5, 4 of 5, or 5 of 5

This is combinations without repetition and order does not matter.
N!/(r!(N-r)!)

5 choose 5 = 5!/(5!(0!)) = 1
5 choose 4 = 5!/(4!(1!)) = 5
5 choose 3 = 5!/(3!(2!)) = 10
5 choose 2 = 10
5 choose 1 = 5

1 + 5 + 10 + 10 + 5 = 31

If you need the order to matter then the code would be different. You would need to save your choice after it is selected, not by looping the selected choices after the fact.
So if you are allowing them to build a combination lock by clicking the buttons in an order, then you need to use different code.

 

[zelgar]

There's only a total of 32 possibilities (2^5), because each of the 5 checkboxes have 2 options (checked or not checked). This is the same as if we're talking about the possibilities of flipping 5 coins. Now, if instead of a checkbox, we're talking about making selections that affected the other items (e.g., you have 5 items that you have to pick your favorite to least favorite, ranking systems, etc.) then the number of combinations would be 5! or 120.

 

[MajP]

I thought I already explained that, but yeah there is 32 not 31. There is the possibility of choosing nothing, but did not consider that a legit choice. But I guess you would need a message saying that you have made no choices. So

5 choose 5 = 5!/(5!(0!)) = 1
5 choose 4 = 5!/(4!(1!)) = 5
5 choose 3 = 5!/(3!(2!)) = 10
5 choose 2 = 10
5 choose 1 = 5
5 choose 0 = 1

1 + 5 + 10 + 10 + 5 = 1 = 32

 

[strongm]

Indeed. It depends how the checkboxes are being used.

 

[lankaw]

MajP & zelgar - you both are absolutely correct , i was looking at this from a math problem stand point with 120 possibilities but as you both stated its 31 combinations with the order not mattering. Also there will be no "5 choose 0 = 1" so only 31

strongm - using my prior example

Check box 1 gives me a msgbox "a"
Check box 2 gives me a msgbox "b"
Check box 3 gives me a msgbox "c"
Check box 4 gives me a msgbox "d"
Check box 5 gives me a msgbox "e"

for example .....says when we select check box 1 2 and 5

i should get a message or copying the findings into a cell saying "a & b & e"

and so on and so forth having 31 possible combinations as stated by MajP & zelgar.

Andy - i tried you're solution and it did not work as i more than just the check boxes as controls in my user form.

 

[MajP]

Use the tag property of the controls you want to include.
If c.tag = "?" Then

 

[lankaw]

MajP - I am assuming you are referring to Andy's solution , the problem is i have more check boxes for other functions that the MSForms.CheckBox goes through unless there is a way to specify to these 5 specific check boxes

 

[MajP]

Yes I am referring to Andy's solution. Since you do not want to consider all the checkboxes, then you need a way to identify the controls you are considering. You can use the controls tag property for the ones to consider. Or you could name the controls with a constant naming convention like chk1, chk2, chk3 and do something like

dim I as integer
dim c as control
for I = 1 to 5
set c = me.controls("chk"&I)
....
next I

 

[lankaw]

Dim c As Control
Dim s As String
Dim I As Integer

 

If UserForm1.chk1 = True Then
Count = Count + 1
End If
If UserForm1.chk2 = True Then
Count = Count + 1
End If
If UserForm1.chk3 = True Then
Count = Count + 1
End If
If UserForm1.chk4 = True Then
Count = Count + 1
End If
If UserForm1.chk5 = True Then
Count = Count + 1
End If


'Loop thru all controls on your Form
For I = 1 To 5
Set c = Me.Controls("chk" & I)
    'Detect if control is a checkbox
    If TypeOf c Is MSForms.CheckBox Then
        'See if the checkbox is checked
        If c.Value = True Then
            If Count = 1 Then
            s = s & vbNewLine & c.Caption
            Else
            s = s & vbNewLine & c.Caption & " & "
            End If
        End If
    End If
    Next I



If Len(s) > 0 Then
    MsgBox s
Else
    MsgBox "Nothing has been checked."
End If

thank you Andrzejek & MajP this above works

but i do have one more issue , as i have put in "&" after each caption .... the problem is i get an extra "&" after the last caption for example (below examples is when you tick all 5 boxes)

checkbox1 &
checkbox2 &
checkbox3 &
checkbox4 &
checkbox5 &

while it should be like this


checkbox1 &
checkbox2 &
checkbox3 &
checkbox4 &
checkbox5

 

[lankaw]

silly me

this works yay ! thank you all

Dim c As Control
Dim s As String
Dim I As Integer

 

If UserForm1.chk1 = True Then
Count = Count + 1
End If
If UserForm1.chk2 = True Then
Count = Count + 1
End If
If UserForm1.chk3 = True Then
Count = Count + 1
End If
If UserForm1.chk4 = True Then
Count = Count + 1
End If
If UserForm1.chk5 = True Then
Count = Count + 1
End If


'Loop thru all controls on your Form
For I = 1 To 5
Set c = Me.Controls("chk" & I)
    'Detect if control is a checkbox
    If TypeOf c Is MSForms.CheckBox Then
        'See if the checkbox is checked
        If c.Value = True Then
            If Count = 1 Then
            s = s & vbNewLine & c.Caption
            Else
            s = s & vbNewLine & c.Caption & " & "
            Count = Count - 1
            End If
        End If
    End If
    Next I



If Len(s) > 0 Then
    MsgBox s
Else
    MsgBox "Nothing has been checked."
End If



End Sub

 

[MajP]

Dim c As Control
Dim s As String
Dim I As Integer
dim count as integer
 
'Loop thru chekboxes
S = "Nothing has been checked"
For I = 1 To 5
 Set c = Me.Controls("chk" & I)
 if c.value = true then count = count + 1
  If Count = 1 Then
       s = c.Caption
  Else
     s = s & "&" & vbNewLine & c.Caption
  end If
Next I

End Sub

I believe this does the same. Not sure why you need to count the controls and then decrement the count. But you can count the controls while you loop. Since you are using the name of the controls there is no need to check that your control is a checkbox. If count = 1 then the default message gets wiped out and the first time through it gets set to c.caption. So the first control met S =

"First Caption"
Next time through the loop if value selected
"First Caption &
Second Caption"

 

[Andrzejek]

I would either use MajP’s first suggestion – Tag the checkboxes you want to include, or place the checkboxes on a Frame (GroupBox?) and detect if the control I am interested in is located in the Container (the Frame). I think this would work.

Why I would use one of those approaches? I am a lazy guy and I don’t want to change my code. So if the User would come to me and say: “I need another 2 checkboxes” and 2 months later: “I need another 6 checkboxes”, I would need to touch my code.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.