我有一个包含多张工作表的工作簿,在其中我使用选项按钮来激活不同的脚本。
并不是所有的按钮都用在所有的图纸上。它们写在一个子中,所以如果我不使用工作表上的选项按钮,Set shp
会给我一个错误,因为它找不到按钮,除非我使用On Error Resume Next
。
我该如何摆脱On Error Resume Next
?我想到了一些类似检查选项按钮是否存在的方法,并且只有当该按钮存在于工作表上但我无法使其工作时才使用Set shp
。
Private Sub ob(opt As String)
Dim shp1 As Shape
Dim shp2 As Shape
Dim shp3 As Shape
Dim shp4 As Shape
Dim shp5 As Shape
On Error Resume Next ' <-I want to get rid of this
Set shp1 = ActiveSheet.Shapes("OptionButton1")
Set shp2 = ActiveSheet.Shapes("OptionButton2")
Set shp3 = ActiveSheet.Shapes("OptionButton3")
Set shp4 = ActiveSheet.Shapes("OptionButton4")
Set shp5 = ActiveSheet.Shapes("OptionButton5")
Select Case xlOn
Case shp1.ControlFormat.Value
opt = "ob1"
Case shp2.ControlFormat.Value
opt = "ob2"
Case shp3.ControlFormat.Value
opt = "ob3"
Case shp4.ControlFormat.Value
opt = "ob4"
Case shp5.ControlFormat.Value
opt = "ob5"
End Select
End Sub
编辑:
在Ralph的版本之后,实际的脚本是这样的:
Private Sub ob(opt As String)
Dim shp1 As Shape
Dim shp2 As Shape
Dim shp3 As Shape
Dim shp4 As Shape
Dim shp5 As Shape
For Each ws In ActiveWorkbook.Worksheets
For Each shp In Worksheets(ws.Name).Buttons
Select Case btn.Name
Case "OptionButton1"
Set shp1 = ActiveSheet.Shapes("OptionButton1")
Case "OptionButton2"
Set shp2 = ActiveSheet.Shapes("OptionButton2")
Case "OptionButton3"
Set shp3 = ActiveSheet.Shapes("OptionButton3")
Case "OptionButton4"
Set shp4 = ActiveSheet.Shapes("OptionButton4")
Case "OptionButton5"
Set shp5 = ActiveSheet.Shapes("OptionButton5")
End Select
Next shp
Next ws
Select Case xlOn
Case shp1.ControlFormat.Value
opt = "ob1"
Case shp2.ControlFormat.Value
opt = "ob2"
Case shp3.ControlFormat.Value
opt = "ob3"
Case shp4.ControlFormat.Value
opt = "ob4"
Case shp5.ControlFormat.Value
opt = "ob5"
End Select
End Sub
我在网上得到了所需的对象错误:
Select Case btn.Name
我建议采用这种方法。希望能有所帮助:
Public Sub AllShapesOnAllSheets()
For Each btn In ActiveSheet.OptionButtons
Select Case btn.Name
Case "OptionButton1"
If btn.Value = xlOn Then opt = "ob1"
Case "OptionButton2"
If btn.Value = xlOn Then opt = "ob2"
Case "OptionButton3"
If btn.Value = xlOn Then opt = "ob3"
Case "OptionButton4"
If btn.Value = xlOn Then opt = "ob4"
Case "OptionButton5"
If btn.Value = xlOn Then opt = "ob5"
End Select
Next btn
End Sub