Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building a String based on Selected Check boxes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building a String based on Selected Check boxes
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) "Neily" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building a String based on Selected Check boxes
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 -- HTH, Bernie MS Excel MVP "Neily" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building a String based on Selected Check boxes
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 -- HTH, Bernie MS Excel MVP "Neily" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Building a String based on Selected Check boxes
That's brilliant,
A couple more reads of it and checking the syntax for bits and I've got it. Thanks a lot. "Bernie Deitrick" wrote: Neily, Yes, it will work, as long as your naming convention is consistently applied. HTH, Bernie MS Excel MVP "Neily" wrote in message ... 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" wrote: 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 -- HTH, Bernie MS Excel MVP "Neily" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide & Show Rows based on Check Boxes | Excel Discussion (Misc queries) | |||
Building Sum by Matching String | Excel Discussion (Misc queries) | |||
excel check boxes moving when excel selected cells pasted in word | Excel Discussion (Misc queries) | |||
Can one cell hold multiple check boxes (and then sort based off e. | Excel Discussion (Misc queries) | |||
Need to sort based on Check Boxes | Excel Programming |