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

Question

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!

Jacob · Accepted Answer · 2011-07-28T12:27:30.093

5

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

edited Jul 28 '11 at 12:27

answered Jul 28 '11 at 12:18

Jacob

41,721
6
79
81

Hi cularis, thanks for the amazingly fast response! This worked for me. – MrPatterns Jul 28 '11 at 12:28

score 1 · Answer 2 · answered Dec 15 '15 at 22:52

1

This Worked for me.

Sheets("Sheet").Shapes("TheNameOfTheLabel").TextFrame.Characters.Text = "Hello"

answered Dec 15 '15 at 22:52

moberme

669
7
13

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

2 Answers2

Linked