工作表中的VBA ActiveX标签 [英] VBA ActiveX label in a sheet

问题描述

我想在Excel中有一个ActiveX标签，其内部值与更新时Range("F4")中的值相对应.它不是表格(很多标签信息都在表格上)，而只是在常规工作表上.我该如何做到这一点?

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

必要的计算已经完成，并正确显示在单元格F4中.

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

谢谢！

推荐答案

我更喜欢在代码中定义并设置所有对象.

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

首先，将 Worksheets("Sheet1")设置为 Sht 对象(类型为 Worksheet ).

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

第二，将Label1 Active-X设置为 MyLbl ，对象(类型为 OLEObject ).

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

最后，更改 MyLbl 对象的 Caption .

代码

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

---

编辑1 :在客户注释后，需要将代码移至 Worksheet 模块，并将其绑定到 Worksheet_Change 事件.

---

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

这篇关于工作表中的VBA ActiveX标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！