Changing the labels on a form via VBA

  • Thread starter Thread starter MJP
  • Start date Start date
M

MJP

Apologies if this is really stupid question...

In Excel 95 my forms were via Dialog sheets and setting the name of labels
via VBA was easily achieved by :

DialogSheets("Dialog1").label(x).text = lab_txtname

where x represented the label number and lab_txname the name I wanted the
label to have(at run time the data on the sheet determines the label names
for dynamic form creation)

However, now I have moved to Excel 97, dialog sheets have become User forms,
but I've tried the same syntax as above, but with UserForm1, rather than
Dialog1, but it isn't working.

Can anyone tell me how to set the label texts via VBA?

Thanks in advance,

Mitch
 
Mitch,

Big disadvantage, you cannot refer to the labels in a Userform as if they
were in an array, because Userfoirms in VBA don't support control arrays.

Therefore, you have to refer to each control individually, for instance

Userform1.Label1.Caption = lab_txtname
 
Ok thanks - I take it this means if I have 30 labels on a form then I need
to put this line of code in for each one? Is there a quicker way of doing
this?

This seems to be a step backwards on Excel 95 dialogsheets or am I missing
something here?

Thanks,

Mitch
 
Mitch,

I agree, it is, especially as VB has control arrays!

You can loop through all the controls on the form and test for labels. This
code demonstrates. I don't suggest this is what you use, but it shows the
technique
Dim lbl As msforms.Control

For Each lbl In UserForm1.Controls
If TypeOf lbl Is msforms.Label Then
lbl.Caption = "Bob" & lbl.TabIndex
End If
Next lbl

I haven't tested this, but I would suspect the objects are presented in the
For . Next loop in the order that they are created, so you need to be
careful there.
 
MJP,

Don't know if this will work for you, but have you considered putting ALL
the labels on at design time, setting their Visible properties to False, and
at Run time, setting the required label's visible property to true.
You can position all the labels at the same place, one behind the other.
Only the topmost visible one will be shown.

HTH
Henry
 
MJP,

How about setting up Global Strings for the label captions in your module?
Global Lbl1Cap as String
Global Lbl2Cap as String etc.

Lbl1Cap ="Test"
Lbl2Cap = "O.K."
You can set these to whatever you want to appear on the labels before
opening the form.

In the form-initialize event
Label1.Caption = Lbl1Cap
Label2.Caption = Lbl2Cap etc.

That way you can use the same form many times, with different labels.
You just have to set up the string values for the captions before you open
your form.
AFAIK there's no way you can do this in bulk.

HTH
Henry
 
Back
Top