Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change label name with loop
Suppose I have a userform with 10 labels on it named
Label1, Lable2, Label3 ... Label10. These labels have different captions depending on user chosen uses. The captions can be defined by different arrays such as vArray1=Array("Apples", "Oranges" ,"Lemons"...) vArray2=Array("Red","Yellow","Blue",...) Is it possible to change the captions using a For statement like Sub LabelCaptions Dim oLabel as control, i as integer With UserForm1 For i = 1 To 10 Set oLabel =( "Label" & i ) oLabel.Caption = vArray1(i) Next End With End Sub When I try this I get an Error at the Set Statment How can I set oLabel to the variable name Thanks Merlyn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change label name with loop
Your close but you are still missing a few things.
Unless you have specified option base 1 then your array starts at 0 and you will blow through your upper bound. Count from 0 to 9. Since you have no label zero you will have to add 1 to your label counter. The line Set oLabel =( "Label" & i ) is trying to set your control to a string. You want Set oLabel = Controls("Label" & i + 1) Finally don't bother using integer. VB converts int to long anyways so you are better off to just use long. Here is my final code... Sub LabelCaptions() Dim oLabel As Control, i As Long, vArray1() as String vArray1 = Array("Apples", "Oranges", "Lemons") With UserForm1 For i = 0 To 2 Set oLabel = Controls("Label" & i + 1) oLabel.Caption = vArray1(i) Next i End With End Sub -- HTH... Jim Thomlinson " wrote: Suppose I have a userform with 10 labels on it named Label1, Lable2, Label3 ... Label10. These labels have different captions depending on user chosen uses. The captions can be defined by different arrays such as vArray1=Array("Apples", "Oranges" ,"Lemons"...) vArray2=Array("Red","Yellow","Blue",...) Is it possible to change the captions using a For statement like Sub LabelCaptions Dim oLabel as control, i as integer With UserForm1 For i = 1 To 10 Set oLabel =( "Label" & i ) oLabel.Caption = vArray1(i) Next End With End Sub When I try this I get an Error at the Set Statment How can I set oLabel to the variable name Thanks Merlyn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change label name with loop
vArray1 should be of type variant not string... Ooops...
-- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Your close but you are still missing a few things. Unless you have specified option base 1 then your array starts at 0 and you will blow through your upper bound. Count from 0 to 9. Since you have no label zero you will have to add 1 to your label counter. The line Set oLabel =( "Label" & i ) is trying to set your control to a string. You want Set oLabel = Controls("Label" & i + 1) Finally don't bother using integer. VB converts int to long anyways so you are better off to just use long. Here is my final code... Sub LabelCaptions() Dim oLabel As Control, i As Long, vArray1() as String vArray1 = Array("Apples", "Oranges", "Lemons") With UserForm1 For i = 0 To 2 Set oLabel = Controls("Label" & i + 1) oLabel.Caption = vArray1(i) Next i End With End Sub -- HTH... Jim Thomlinson " wrote: Suppose I have a userform with 10 labels on it named Label1, Lable2, Label3 ... Label10. These labels have different captions depending on user chosen uses. The captions can be defined by different arrays such as vArray1=Array("Apples", "Oranges" ,"Lemons"...) vArray2=Array("Red","Yellow","Blue",...) Is it possible to change the captions using a For statement like Sub LabelCaptions Dim oLabel as control, i as integer With UserForm1 For i = 1 To 10 Set oLabel =( "Label" & i ) oLabel.Caption = vArray1(i) Next End With End Sub When I try this I get an Error at the Set Statment How can I set oLabel to the variable name Thanks Merlyn |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change label name with loop
Just as a point of interest, your code can be reduced to this if desired...
Sub LabelCaptions() Dim i As Long For i = 0 To 2 Controls("Label" & (i + 1)).Caption = Array("One", "Two", "Three")(i) Next i End Sub I changed your label names so the assignment line wouldn't wrap. -- Rick (MVP - Excel) "Jim Thomlinson" wrote in message ... Your close but you are still missing a few things. Unless you have specified option base 1 then your array starts at 0 and you will blow through your upper bound. Count from 0 to 9. Since you have no label zero you will have to add 1 to your label counter. The line Set oLabel =( "Label" & i ) is trying to set your control to a string. You want Set oLabel = Controls("Label" & i + 1) Finally don't bother using integer. VB converts int to long anyways so you are better off to just use long. Here is my final code... Sub LabelCaptions() Dim oLabel As Control, i As Long, vArray1() as String vArray1 = Array("Apples", "Oranges", "Lemons") With UserForm1 For i = 0 To 2 Set oLabel = Controls("Label" & i + 1) oLabel.Caption = vArray1(i) Next i End With End Sub -- HTH... Jim Thomlinson " wrote: Suppose I have a userform with 10 labels on it named Label1, Lable2, Label3 ... Label10. These labels have different captions depending on user chosen uses. The captions can be defined by different arrays such as vArray1=Array("Apples", "Oranges" ,"Lemons"...) vArray2=Array("Red","Yellow","Blue",...) Is it possible to change the captions using a For statement like Sub LabelCaptions Dim oLabel as control, i as integer With UserForm1 For i = 1 To 10 Set oLabel =( "Label" & i ) oLabel.Caption = vArray1(i) Next End With End Sub When I try this I get an Error at the Set Statment How can I set oLabel to the variable name Thanks Merlyn |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change label name with loop
Thanks for the help
Merlyn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change all label controltiptexts in userform | Excel Programming | |||
how to change width of label box in pie chart | Charts and Charting in Excel | |||
change label font | Excel Programming | |||
change label on multipage | Excel Programming | |||
On Error GoTo Label in a loop only working once. | Charts and Charting in Excel |