CODEWITHSUNDEEP
vbaexcel
Taylor Andy
Taylor Andy

Reputation: 25

VBA ActiveX label in a sheet

I want to have an ActiveX label in Excel with a value inside that corresponds to the value in Range("F4") as it updates. It is not in a form (a lot of the info on labels are on forms), but just on a regular worksheet. How can I make this happen?

I've been using this to assign label to macro but it's not working:

Sub CurrentPoints()
   Worksheets("Sheet1").Label1.Value = Range("F4").Value
End Sub

The necessary calculations are already done and appear correctly in cell F4.

Thanks!

Upvotes: 2

Views: 8788

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

I prefer to define and Set all my objects in my code.

First, set Worksheets("Sheet1") to Sht object (type Worksheet).

Second, set Label1 Active-X to MyLbl, object (type OLEObject).

At last, change the Caption of MyLbl object.

Code

Option Explicit

Sub CurrentPoints()

Dim Sht  As Worksheet
Dim MyLbl As OLEObject

' set the worksheet object
Set Sht = ThisWorkbook.Worksheets("Sheet1")

' set the Active-X label object
Set MyLbl = Sht.OLEObjects("Label1")

' change the Caption to the value in Range "F4")
MyLbl.Object.Caption = Sht.Range("F4").Value

End Sub

Edit 1: After client's notes, need to move the code to Worksheet module, and tie it to Worksheet_Change event.

Modified Code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyLbl As OLEObject

' check if the cell that was changed is "F4"
If Not Intersect(Target, Range("F4")) Is Nothing Then

    ' set the Active-X label object
    Set MyLbl = ActiveSheet.OLEObjects("Label1")

    ' change the Caption to the value in Range "F4")
    MyLbl.Object.Caption = Target.Value
End If

End Sub

Upvotes: 2

Related Questions