时间戳

首页 > 解决方案 > 如何在同一个工作表上运行多个 VBA 代码

excel - 如何在同一个工作表上运行多个 VBA 代码

问题描述

我目前正在运行以下代码：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Worksheet_SelectionChange Target

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If Intersect(.Cells, Range("E4:K120")) Is Nothing Or .Count > 1 Then Exit Sub
        Select Case .Value
        Case ""
            .Interior.ColorIndex = 3
        Case 1
            .Interior.ColorIndex = xlNone
            .Value = vbNullString
        Exit Sub
        Case Else
        Exit Sub
        End Select
            .Value = .Value + 1
    End With

End Sub

我现在需要为同一个工作表上的不同单元格区域运行类似的代码。单击 N 列中的单元格时，我需要代码在 4 种不同的颜色和文本中循环。我不是编码员，所以这远高于我的工资等级。谢谢！

标签： excelvba

解决方案

也许是这样的：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Set a flag to let us know if we're in that "one color" range or "four color" range
    Dim GroupIndicator As Integer
    GroupIndicator = 0


    With Target
        'if our selection has more than one cell, just don't do anything (exit the sub)
        If .Count > 1 Then
            Exit Sub
        End If

        'if our selection is in the first range, set our indicator to 1.  if it's in the second range, set the indicator to 2
        If Not Intersect(.Cells, Range("E4:K120")) Is Nothing Then
            GroupIndicator = 1
        ElseIf Not Intersect(.Cells, Range("N4:N120")) Is Nothing Then
            GroupIndicator = 2
        Else
            Exit Sub
        End If

        'do this block if indicator is 1 (the first range).  If there's no value, make the cell red and put in a value of 1.  Otherwise, clear the color and remove the value
        If GroupIndicator = 1 Then
            If .Value = "" Then
                .Interior.ColorIndex = 3
                .Value = 1
            Else
                .Interior.ColorIndex = xlNone
                .Value = vbNullString
            End If
        End If

        'do this block if indicator is 2 (the second range).  increment our value and then assign the value indicated.
        If GroupIndicator = 2 Then
            .Value = .Value + 1

            Select Case .Value
                Case 1
                    .Interior.ColorIndex = 5
                Case 2
                    .Interior.ColorIndex = 6
                Case 3
                    .Interior.ColorIndex = 7
                Case 4
                    .Interior.ColorIndex = 8
                Case Else
                    .Interior.ColorIndex = xlNone
                    .Value = vbNullString
            End Select
        End If


    End With

End Sub

推荐阅读