Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Array of Labels

  Asked By: Cesar    Date: Feb 04    Category: MS Office    Views: 603
  

I've got a coworker that brought a problem to me.
And I haven't been able to come up with a solution.

He's got a userform with 15 Text boxes, and 15 Labels.
He's called them (unoriginally)
Userform1.Label1
Userform1.Label2, etc.

He has stored the values for the selections in an array.
He wants to be able to select the "Form Type" from a ComboBox,
and have the Labels updated dynamically based on the values in the
array.

What he'd LIKE to do is something like:
For I = 1 to 15
UserForm1.Label(I).Caption = NoteArray(I)
Next I

which, of course, is not "legal".

In other languages, I've used something like "Eval" to
basically "evaluate" the string and treat it as a command, but I
cannot find an similar statement in VBA.

Any ideas?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Elaine Stevens     Answered On: Feb 04

You should be able to use something like this...

Dim ctrl As Control
Dim i As Integer

For Each ctrl In Controls
If Left(ctrl.Name, 5) = "Label" Then
ctrl.Caption = NameArray(i)
i = i + 1
End If
Next

 
Answer #2    Answered By: Alexis Castillo     Answered On: Feb 04

Don't know if you could do:

MyControlName="Label" and I
Userform.controls(MyControlName).caption=NoteArray(I)

 
Answer #3    Answered By: Dot net Sachin     Answered On: Feb 04

They were great, insightful, and gave me even more "lessons" to pursue!

The approach I recommended to my coworker was the one that David suggested:


For I = 1 To 10
LabelForm.Controls("Checkbox" & I).Caption = NoteArray(I)
Next I

VERY slick!

 
Answer #4    Answered By: Renee Lane     Answered On: Feb 04

Coming to this late but .....

This sort of construct may help....

Dim ctlCurrent As Control

For Each ctlCurrent In frmpForm.Controls

If TypeOf ctlCurrent Is MSForms.TextBox Then

ElseIf TypeOf ctlCurrent Is MSForms.CommandButton Then

ElseIf TypeOf ctlCurrent Is MSForms.Frame Then

ElseIf TypeOf ctlCurrent Is MSForms.Label Then

ElseIf TypeOf ctlCurrent Is MSForms.UserForm Then

ElseIf TypeOf ctlCurrent Is MSForms.CheckBox Then

ElseIf TypeOf ctlCurrent Is MSForms.ComboBox Then

ElseIf TypeOf ctlCurrent Is MSForms.ListBox Then

ElseIf TypeOf ctlCurrent Is MSForms.OptionButton Then

Else

End If

Next ctlCurrent

It's from a word vba  module but I see no reason it shouldn't work in Excel.

 
Didn't find what you were looking for? Find more on Array of Labels Or get search suggestion and latest updates.




Tagged: