Changing the text of a label on a Userform

[Robert Crandal]

My userform contains one label control that contain a
text caption. The problem is, I want the text caption
to mirror the contents of cell "A1" at all times.

So, when the userform is loaded, I want it to immediately
read the value from cell "A1" and store it in my label's
caption. Then, every time someone changes the contents of
cell "A1" I want my label to immediately detect the change
and re-load the text from cell "A1" onto the label.

What would be a good way to implement this?? Would it
involve using a combination of Userform_Initialize()
and Worksheet_Change()?? Does anyone have any code
examples?

thank you!

 

[Rick Rothstein]

Yes, you would use the UserForm's Initialize event and the Worksheet's
Change event. Of course, this assumes you are showing the UserForm as
non-modal; that is, when executing the command to show the UserForm, you do
so by specifying the optional vbModeless argument to the Show method like
this...

UserForm1.Show vbModeless

Here is the other code you will need (read the comments)...

' Put this in the UserForm's code window
Private Sub UserForm_Initialize()
Label1.Caption = Worksheets("Sheet1").Range("A1").Value
End Sub

' Put this in the worksheet's code window
Private Sub Worksheet_Change(ByVal Target As Range)
UserForm1.Label1.Caption = Range("A1").Value
End Sub

Note that I used the default names for the UserForm and Label and assumed
that A1 was on Sheet1 (change these values in the code above to match your
actual conditions).