Labels in user forms

[rob nobel]

Hi group,
In a Userform, can the label (where you enter text) show the result of a
formula so that the message changes depending on the formula?
Or is there a way to do this in another way if the label can't do that?
Rob

 

[Bob Phillips]

Rob ,

I assume that you mean a textbox not label, as that is where you enter text.

One way could be to trap the Calculate event for the sheet that your formula
is on and update the text box from there, something like

Private Sub Worksheet_Calculate()
UserForm1.TextBox1.Text = Range("A1")
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[rob nobel]

Hi Bob and thanks for that.
I did actually mean label but not being too conversant with UserForms I
didn't know another way to enter text and went for the first option I saw.
But if the text box will do it I'll convert the text to a textbox instead.
Rob

 

[Bob Phillips]

Rob,

If you want a label, just use

Private Sub Worksheet_Calculate()
UserForm1.Label1.Caption = Range("A1")
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[rob nobel]

Thanks Bob. But the Text box has worked fine for me. The
Worksheet_Calculate(), I've used before in another application and it gave
me heaps of problems in that it seemed to want to recalculate every
worksheet and workbook that was open at the time. I guess I didn't really
know how to use that procedure properly as I was told that shouldn't
happen. (But it did.)
Rob

 

[Bob Phillips]

Rob,

Worksheet_Calculate doesn't do the recalculate, it is event code that is
triggered whenever a cell on that worksheet changes that causes a calculate.
Whoever told you that probably meant that the event macro would be triggered
on every change that causes recalculation, but as there is only one
recalculation, regardless of how many cells get changed that is not too big
a deal.

The code I gave you will cause your textbox to update even if its source
cell does not get updated. A bit of an overhead, but best I could come up
with.

Anyway, glad it's sorted for you.

Regards

Bob

 

[rob nobel]

Yes Bob, all true. That particular worksheet did have a loop procedure
checking a range of cells. It worked fine but I just could not get it to
stop (seemingly) calculating other open books as well which was a real
puzzler to me.
I eventually tossed the worksheet_calculate() and made the code part of
another procedure. When that worked OK I took it to mean that the
Worksheet_Calculate () event was the cause.
Nevertheless, as I don't understand all of this fully, such as ..."it is
event code that is triggered whenever a cell on that worksheet changes that
causes a calculate." I use what I can get to work and experiment with any
other suggestions.
Thank you for your advice and input,
Rob