)我对VBA很陌生!在使用复选框(ActiveX)时,我有一个插入日期的工作代码,其中有书签。问题是我有33个复选框(我实际上希望33x2 )。(一个代表是,一个代表否定)。所以我得到了33个潜艇和33个书签。我敢打赌,这个代码可以更有效地制动它,只剩下几个潜艇。安娜有个好主意,如果能做到?下面的代码是33个重复subs中的第一个,其中Sub和书签名为agi1,agi2 agi3.
Private Sub agi1_Click()
Dim rngFormat As Range
Set rngFormat = ActiveDocument.Range( _
Start:=ActiveDocument.Bookmarks("agi1").Range.Start, _
End:=ActiveDocument.Bookmarks("agi1").Range.End)
With rngFormat
.Font.Size = 8
End With
Dim v
Dim BMRange As Range
v = ThisDocument.agi1.Value
'Sjekke om boks er sjekket eller ikke
If v = True Then
'Sett inn dato i bokmerke
Set BMRange = ActiveDocument.Bookmarks("agi1").Range
With Selection.Font
.Size = 9
End With
BMRange.Text = (Format(Date, "dd.mm.yyyy"))
Else
'Erstatte dato med tom tekst hvis boks ikke er sjekket
Set BMRange = ActiveDocument.Bookmarks("agi1").Range
BMRange.Text = " "
End If
'Sett inn bokmerke på nytt
ActiveDocument.Bookmarks.Add "agi1", BMRange
End Sub发布于 2019-01-21 18:37:20
ActiveX控件总是注册其事件处理程序,如下所示:
Private Sub NameOfTheControl_NameOfTheEvent({args})如果重命名该处理程序,则该控件将停止工作--因为处理程序的名称必须按上面的形式形成,并以下划线分隔控件的名称和已处理事件的名称。
因此,如果您的控件必须在编译时存在,则无法避免:对于33个控件,您需要33个处理程序。
这并不意味着你需要那个庞大的程序重复33次!
提取一个程序。选择处理程序的整个主体,剪掉它。
现在制作一个新的过程原型:
Private Sub HandleCheckBoxClick(ByVal controlName As String)
End Sub把身体贴在里面。然后将具有硬编码"agi1"的所有位置替换为对此controlName参数的引用:
Dim rngFormat As Range
Set rngFormat = ActiveDocument.Range( _
Start:=ActiveDocument.Bookmarks(controlName).Range.Start, _
End:=ActiveDocument.Bookmarks(controlName).Range.End)
With rngFormat
.Font.Size = 8
End With
'...您所指的使用控件的编程名称的位置将更加困难:
v = ThisDocument.agi1.Value您可以通过MSForms.CheckBox集合获得ThisDocument.InlineShapes控件,但是that won't let you find a checkbox by its name,因此您需要一个可以为您执行此操作的函数:
Private Function FindCheckBoxByName(ByVal controlName As String) As MSForms.CheckBox
Dim sh As InlineShape
For Each sh In ThisDocument.InlineShapes
If TypeOf sh.OLEFormat.Object Is MSForms.CheckBox Then
If sh.OLEFormat.Object.Name = controlName Then
'return the MSForms control:
Set FindControlByName = sh.OLEFormat.Object
End If
End If
Next现在你可以这么做了:
Dim cb As MSForms.ChecBox
Set cb = FindCheckBoxByName(controlName)
If cb Is Nothing Then
MsgBox "No ActiveX CheckBox control named '" & controlName & "' was found in ThisDocument."
Exit Sub
End If
v = cb.Value一旦参数化了对ActiveX控件的所有引用,您的33个处理程序现在可以如下所示:
Private Sub agi1_Click()
HandleCheckBoxClick "agi1"
End Sub
Private Sub agi2_Click()
HandleCheckBoxClick "agi2"
End Sub
'...
Private Sub agi33_Click()
HandleCheckBoxClick "agi33"
End Sub或者,您可以在运行时创建复选框,然后在一个专门的类模块中处理它们的Click事件,但这涉及得更多;-)
发布于 2019-01-21 18:55:42
你可以利用事件下沉,也许。
在普通模块中,创建一个集合并填充它以保存将控制复选框事件的类。
在这里有代码,这将需要在打开文档时运行,这是它生命早期的一些东西来填充集合。
Public col As Collection
Public Sub SETUP()
Dim o As InlineShape
Dim c As MSForms.CheckBox
Dim cust As clsCustomCheckBox
Set col = New Collection
For Each o In ActiveDocument.InlineShapes
Set c = o.OLEFormat.Object
Set cust = New clsCustomCheckBox
cust.INIT c
col.Add cust
Next o
End Sub然后有一个名为clsCustomCheckBox的类模块,并将其代码作为
Private WithEvents c As MSForms.CheckBox
Public Function INIT(cmdIN As MSForms.CheckBox)
Set c = cmdIN
End Function
Private Sub c_Click()
MsgBox "Here you can get the name " & c.Name
End Sub这将把每个复选框的点击转移到类c_click,而不是它自己的类。
所以对你来说
Dim rngFormat As Range
Set rngFormat = ActiveDocument.Range( _
Start:=ActiveDocument.Bookmarks(c.name).Range.Start, _
End:=ActiveDocument.Bookmarks(c.name).Range.End)
With rngFormat
.Font.Size = 8
End With
.......https://stackoverflow.com/questions/54295409
复制相似问题