|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
hiyou 发表于 2014-7-22 11:31
快捷的方法是有的,把你的VB模块代码做成一个文本文件,然后在VBA中打开这个文件读进去赋值给一个字符变量,就 ...
朋友谢谢你的回复,我还想请教个问题,关于checkbox的。我录制了一段添加checkbox的宏,当我按下宏的快捷键时当前工作表中就会添加一个checkbox,但此checkbox并没有事件代码,每次还得手动为其添加我已编写好的事件代码。
如何改这个录制的宏当,当我按下宏的快捷键时,checkbox添加后自动带上我编写好的事件代码...?
以下就是我添加checkbox的宏代码。
Set BeiJ = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=409.5, Top:=0, Width:=30, Height:=19.5)
With BeiJ
.Object.Value = True
.Object.BackColor = &HFFFF&
.Object.Caption = "BJ"
'.Object.(Name) = "BJ"
End With
我为其编写的事件代码如下:
Private Sub checkbox1_Click()
Dim BJ, i&
On Error Resume Next
BJ = Array("3001", "3002", "3003", "3004", "3005", "3006", "3007", "3008", "3009", "3010", "3011", "3012", "3013", "3014", "3015", "3016", "3018", "3019", "3020", "3021", "6202", "6224", "6230", "6232", "6233", "6234", "6236", "6237", "6238", "6240", "6241", "6242", "6243", "6244", "6245")
If CheckBox1.Object.Value = True Then
For i = 1 To UBound(BJ)
With ActiveSheet.PivotTables("PivotTable1").PivotFields("T")
.PivotItems(BJ(i)).Visible = True
End With
Next i
Else
For i = 1 To UBound(BJ)
With ActiveSheet.PivotTables("PivotTable1").PivotFields("T")
.PivotItems(BJ(i)).Visible = False
End With
Next i
End If
End Sub
|
|