Building a String based on Selected Check boxes

[Neily]

Hi,

I'm hoping someone can help here coz I'm pretty sure this is possible by I'm
at a dead end.

I have a UserForm which dynamically creates a set of Check boxes based on
the number of teams in a department. The user should then select what teams
they wish to use and then once they push an OK button, the Code should build
a string putting these teams into appostophies(?sp?) separated by commas

eg. 'team1','team2','team3'

Where I got to was I got the form to create the check boxes and set their
NAME property to the team name, then I thought I might be able to loop
through the checkboxes, if they are checked, add that team into the string,
but I can't dynamically reference the checkboxes.

Anyway, if anyone can offer advise, I'd be very appreciative.

Ta.

Neily

 

[Bob Phillips]

For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox") Then
sTeams = sTeams & "," & ctl.caption
End If
Next ctl

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bernie Deitrick]

Neily,

Assuming that your checkbox names all start with "Check", they are on
Userform1, and their captions are what you are interested in:

Private Sub CommandButton1_Click()
Dim i As Integer
Dim myStr As String

myStr = ""

For i = 0 To UserForm1.Controls.Count - 1
If Left(UserForm1.Controls.Item(i).Name, 5) = "Check" Then
If UserForm1.Controls.Item(i) Then
If myStr = "" Then
myStr = "'" & UserForm1.Controls.Item(i).Caption & "'"
Else
myStr = myStr & ",'" & _
UserForm1.Controls.Item(i).Caption & "'"
End If
End If
End If
Next i

MsgBox myStr

End Sub

 

[Bernie Deitrick]

Neily,

Sorry about that. I guess I didn't read your post closely enough the first
time. You will need to change your naming convention for your checkboxes,s
so that checkboxes can be identified as such by name (which the user never
sees). Then use the caption (which the user sees) for the team name.

HTH,
Bernie
MS Excel MVP

 

[Neily]

Hi Bernie,

Thanks for this. Will this work even with though I have other controls on
the form. It looks like it counts the number of Controls on the form to
create the For Loop, but then there is a Button and a couple of labels on the
form as well.

Ta..

Neily

 

[Bernie Deitrick]

Neily,

Yes, it will work, as long as your naming convention is consistently
applied.

HTH,
Bernie
MS Excel MVP

 

[Neily]

That's brilliant,

A couple more reads of it and checking the syntax for bits and I've got it.
Thanks a lot.