☰

关闭该选项卡时 Worksheet_Calculate 脚本出现错误

tag-icon • tag-icon microsoft-excel macros vbscript
关闭该选项卡时 Worksheet_Calculate 脚本出现错误

我创建了以下脚本，以在数据查询刷新时根据数字显示不同颜色的形状，并且它可以工作，但是当我关闭选项卡或打开另一个工作簿时，刷新时会出现错误，提示未找到对象并突出显示以下行：

ActiveSheet.Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1

我认为这是因为“ActiveSheet”引用，所以我用实际的工作表名称替换它，它仍然有效，但仍然出现错误。

Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1

我在这里做错了什么？另外，如果您看到任何可以简化的代码，我的 VBS 技能并不是最好的。代码如下：

Private Sub Worksheet_Calculate()
Dim Xrg As Range, Yrg As Range

Set Xrg = Range("K31")
Set Yrg = Range("K32")

For Each aCell In Xrg
    If Not Intersect(Xrg, Range("K31")) Is Nothing Then
        If Range("K31").Value = 0 Then
            If Rows("25:25").EntireRow.Hidden = False Then
            Rows("25:25").EntireRow.Hidden = True
            End If
            ElseIf Range("K31").Value <> 0 Then
            If Rows("25:25").EntireRow.Hidden = True Then
            Rows("25:25").EntireRow.Hidden = False
            End If
        End If
    End If
Next
For Each aCell In Yrg
    If Range("K32").Value < 55 Then
    Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
    Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
    Worksheets("Outbound").Shapes("Util 1").Visible = True
    Worksheets("Outbound").Shapes("Util 2").Visible = False
    Worksheets("Outbound").Shapes("Util 3").Visible = False
    Worksheets("Outbound").Shapes("Util 4").Visible = False
    Worksheets("Outbound").Shapes("Util 5").Visible = False
    End If
    If Range("K32").Value >= 55 And Range("K32").Value < 65 Then
    Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
    Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
    Worksheets("Outbound").Shapes("Util 1").Visible = False
    Worksheets("Outbound").Shapes("Util 2").Visible = True
    Worksheets("Outbound").Shapes("Util 3").Visible = False
    Worksheets("Outbound").Shapes("Util 4").Visible = False
    Worksheets("Outbound").Shapes("Util 5").Visible = False
    End If
    If Range("K32").Value >= 65 And Range("K32").Value < 75 Then
    Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
    Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
    Worksheets("Outbound").Shapes("Util 1").Visible = False
    Worksheets("Outbound").Shapes("Util 2").Visible = False
    Worksheets("Outbound").Shapes("Util 3").Visible = True
    Worksheets("Outbound").Shapes("Util 4").Visible = False
    Worksheets("Outbound").Shapes("Util 5").Visible = False
    End If
    If Range("K32").Value >= 75 And Range("K32").Value < 85 Then
    Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
    Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
    Worksheets("Outbound").Shapes("Util 1").Visible = False
    Worksheets("Outbound").Shapes("Util 2").Visible = False
    Worksheets("Outbound").Shapes("Util 3").Visible = False
    Worksheets("Outbound").Shapes("Util 4").Visible = True
    Worksheets("Outbound").Shapes("Util 5").Visible = False
    End If
    If Range("K32").Value >= 85 Then
    Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
    Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
    Worksheets("Outbound").Shapes("Util 1").Visible = False
    Worksheets("Outbound").Shapes("Util 2").Visible = False
    Worksheets("Outbound").Shapes("Util 3").Visible = False
    Worksheets("Outbound").Shapes("Util 4").Visible = False
    Worksheets("Outbound").Shapes("Util 5").Visible = True
    End If
Next
End Sub

答案1

您可以对代码进行的一个改进是IF用语句替换SELECT CASE并初始化格式，这样可以更容易地看到每种情况下的更改。

Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False

Select Case Range("K32").Value
    Case Is < 55
       Worksheets("Outbound").Shapes("Util 1").Visible = True
    Case 55 To 64
       Worksheets("Outbound").Shapes("Util 2").Visible = True
    Case 65 to 74
       Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
       Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
       Worksheets("Outbound").Shapes("Util 3").Visible = True
    Case 75 to 84
       Worksheets("Outbound").Shapes("Util 4").Visible = True
    Case Is > 84
       Worksheets("Outbound").Shapes("Util 5").Visible = True
End Select

相关内容