In an Excel UserForm, how do I update a label's caption?

excel vba label caption userform
phan picture phan · Jul 28, 2011 · Viewed 86.9k times · Source

I created my first modeless UserForm in Excel and put an ActiveX label on it. How do I set the caption of the label so that it displays whatever is in Sheet1.Range("A1"), and updates itself when the value in cell A1 changes?

Basically, I want the Userform's label to always be updated the second anything in the Excel cell changes. Thank you!

Answer

Jacob picture Jacob · Jul 28, 2011 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Cells(1, 1), Target) Is Nothing Then
        Exit Sub
    End If
    UserForm1.Label1.Caption = Sheet1.Range("A1").Value
End Sub

The sub Change gets called everytime a cell changes. The code does this: if A1 was changed, change the caption of Label1 on UserForm1. The form must have been opened not modal (vbModeless).

UserForm1.Show vbModeless

Related questions

How do I vertically center the text in an Excel label's caption?

In Excel 2007, I inserted an ActiveX label onto my worksheet. I right-clicked on it and viewed Properties and managed to change the TextAlign property to 2 (frmTextAlignCenter). This aligns the label caption's text to the center of the label (horizontally), but …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More