Nested For Each Statement

  • Thread starter Thread starter V. Roe
  • Start date Start date
V

V. Roe

I am attempting to loop through a list of intials on a hidden sheet and
assign each set of initials as a caption to an optionbutton in a group of
optionbuttons on a userform. I cannot get the loop sequence correct. I
have tried several variations of the code below, but I cannot get it to
change to the next optionbutton. Any help would be greatly appreciated. I
am using excel 97.

For i = 10 To 30
For Each icell In Range("InitialList")
If Not IsEmpty(icell) Then
BillTrackingForm.Controls("OptionButton" & i).Caption = icell.Text
BillTrackingForm.Controls("OptionButton" & i).Enabled = True
End If
Next icell
Next i
 
what do you mean it won't change to the next option button?
do you recieve any error messages? do you have any error handling code in
your routine? (even On Error Resume Next?)
Ben
 
"I am attempting to loop through a list of intials on a hidden sheet and
assign each set of initials as a caption to an optionbutton "...
Question: Do certain buttons get certain initials, or do you want to
concatenate all the initials onto each button's caption?
In any event, the problem with your current loop is that while you loop
properly through the cells in InitialList, you always overwrite your prior
caption with the new icell.Text, so at the end the only thing showing (I
presume) is the last initial in the list.
If what you want to do is concatenate those initials, use
BillTrackingForm.Controls("OptionButton" & i).Caption =
BillTrackingForm.Controls("OptionButton" & i).Caption & icell.Text
 
something i just noticed about your code.
The way you have the for next loops set up. each cell will change each
optionbutton NUMEROUS TIMES. optionbutton10 will change for how many ever
cells you have in your "InitialList" range. are you trying to assign each
cell taht contains a SINGLE SET OF initials to a SINGLE Option Button?
Ben
 
Yes I am trying to set a single optionbutton to a single set of initials. I
want the user to select their intials using the optionbutton.
Thank you
 
try this

i = 10
For Each icell In Range("InitialList")
If Not IsEmpty(icell) Then
BillTrackingForm.Controls("OptionButton" & i).Caption = icell.Text
BillTrackingForm.Controls("OptionButton" & i).Enabled = True
i = i +1
End If
Next icell
 
Back
Top