请参考工作表中框架中的控件 [英] Refer To Controls in a Frame in Worksheet

问题描述

我有Class Module，并且用它来处理Click事件.我使用Macro3()将事件与框架T1上的按钮链接起来，并且可以正常工作.

I have the Class Module, and I use it to handle Click event. I use Macro3() to link events with button on the Frame T1 and it works.

我有数百个帧(ActiveX控件)，其名称类似于T1,T2,T3....

I have hundred of Frames (ActiveX Control) with names like T1,T2,T3....

如何在每个框架中引用控件?我尝试了ActiveSheet.Shapes("T1").Controls("title_pic_tog").它不起作用.如果我们可以完成这项工作，那么我可以使用变量替换Shapes("t1").

How do I reference the controls in each frame? I tried ActiveSheet.Shapes("T1").Controls("title_pic_tog"). It doesn't work. If we can make this work then I can use a variable to replace Shapes("t1").

Option Explicit
Public WithEvents cBox As MSForms.ToggleButton

Private Sub cBox_Click()
    msgbox("clicked")
    'End If
End Sub

Sub Macro3()
    Set title_pic_tob_Event_Coll = New Collection

    Set title_pic_tob_Event = New titlepictog
    Set title_pic_tob_Event.cBox = ActiveSheet.t1.Controls("title_pic_tog")
    title_pic_tob_Event_Coll.Add title_pic_tob_Event
End sub

推荐答案

在类模块中(说Class1)

Option Explicit

Public WithEvents cBox As MSForms.ToggleButton

Private Sub cBox_Click()
    MsgBox ("clicked")
End Sub

在模块中

Dim TBArray() As New Class1

Private Sub Sample()
    Dim i As Integer, FrmBCtl As OLEObject, TBCtl As Variant
    Dim ws As Worksheet

    '~~> Change this to the relevant worksheet
    Set ws = Sheet2

    '~~> Loop through all objects in the worksheet
    For Each FrmBCtl In ws.OLEObjects
        '~~> Check if the oleobject is a frame
        If TypeName(FrmBCtl.Object) = "Frame" Then
            '~~> Loop through all controls in the frame
            For Each TBCtl In FrmBCtl.Object.Controls
                i = i + 1
                ReDim Preserve TBArray(1 To i)
                Set TBArray(i).cBox = TBCtl
            Next TBCtl
        End If
    Next FrmBCtl

    Set FrmBCtl = Nothing
    Set TBCtl = Nothing
End Sub

屏幕截图

这篇关于请参考工作表中框架中的控件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！