changing the caption of a label

[Koos]

Hello all,

I created a userform with a label in Excel97 SR2. After the userform is
shown I want to change it's label through my code.
Below is the code I wrote, but the label doesn't display the string I
want it to display.

Dim MyString As String
MyString = "sample text"
UserForm1.Show
UserForm1.MyLabel.Caption = MyString

When the code that tells the form to appear is positioned at the end,
the label displays the right string. But this is not what I want, the
caption of the label must be changed multiple times.

thanks in advance,
Koos

 

[Chip Pearson]

Koos,

In Excel97, all forms are what is called "Modeless". This means that no code
outside the form will run after the Show method until the form is hidden.
Therefore, the line 'UserForm1.MyLabel.Caption = MyString' doesn't execute
until you close the form.

Change the order of the lines, placing the Show method after changing the
caption. E.g.,

UserForm1.MyLabel.Caption = MyString
UserForm1.Show


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[J.E. McGimpsey]

In XL97, all userforms are modal, which means that the macro code is
suspended once the userform is shown, until the form is closed.

You could use the userform's Initialize event (right-click the form,
choose View code, then select "Initialize" from the right-hand
dropdown) to set the label text, and you can change it in the event
code of any userform controls.

 

[Koos]

I already noticed that I can change the label's caption through some
code behind a commandbutton. Although that works fine, it is not the way
I want it to do.
What the problem really is, is that the label has to indicate the
progress of the calculations the excel-macro is doing. And I thought the
best way was to do that through text displayed on a label (and possibly
a progressbar). If there are any other possibilities to indicate this
progress I am interested in them. If these aren't available in excel 97
would it than be better to upgrade to a newer version?

thanks in advance,
Koos

 

[Tom Ogilvy]

See John Walkenbach's tip for an example:
http://j-walk.com/ss/excel/tips/tip34.htm
Displaying a Progress Indicator

what version depends on who is going to use your application. If you
upgrade to xl2000 and your users are using xl97, this won't solve your
problem because those users are limited to xl97 capabilities (and in any
event you should always develop on the lowest version that will use the code
unless you are going to build separate apps for each version).

You seemed to miss the thrust of the advice given. Yes a commandbutton can
change the label, but so can code in the userform. With a modal userform,
you need to show the userform and then have any code run be run from the
userform. This is illustrated in John Walkenbach's tip above.

So, in the initialize event, as recommended, you initialize the value in the
userform label. Then you might have a button that starts the calculations
or otherwise start them - within the calculation code, you change the label
value or update the progress bar as shown by John.

You don't do

userform1.show
Calccode

because the Calccode is not executed until the userform is dropped. (hidden
or unloaded).