vba - 如何让我的宏在选择单元格时运行？

Question

我不是编程新手，但我是在 Excel 中使用宏的新手。我正在使用 Excel 2010，尝试运行以下宏:

Sub HideUnhideCells(ByVal Target As Range)
Dim keyCell As Range
Set keyCell = Range("C9")
Dim Cells1 As Range
Dim Cells2 As Range

'Call the function on C9 cell change
If Target.Address = "$C$9" Then

    'Make Data Source available for for DRG and UCR
    If keyCell.Value = "DRG" Or keyCell.Value = "UCR" Then
        Set Cells1 = Range("C33")
        Cells1.EntireRow.Hidden = False
    Else
        Set Cells1 = Range("C33")
        Cells1.EntireRow.Hidden = True
    End If

    'Make MSA special cells available if MSA is selected
    If keyCell.Value = "MSA" Then
        Set Cells1 = Range("B34:C35")
        Cells1.EntireRow.Hidden = False
    Else
        Set Cells1 = Range("B34:C35")
        Cells1.EntireRow.Hidden = True
    End If

    'Make UCR cells available if UCR is selected
    If keyCell.Value = "UCR" Then
        Set Cells1 = Range("B36:C39")
        Cells1.EntireRow.Hidden = False
    Else
        Set Cells1 = Range("B36:C39")
        Cells1.EntireRow.Hidden = True
    End If

    'Remove extra name cells for 1-file  and 2-file values
    If keyCell.Value = "DRG" Or keyCell.Value = "ICD-9" Or keyCell.Value = "NCCI_Edits" Or keyCell.Value = "UB04" Then
        Set Cells1 = Range("B21:C25")
        Set Cells2 = Range("B28:C32")
        Cells1.EntireRow.Hidden = True
        Cells2.EntireRow.Hidden = True
    ElseIf keyCell.Value = "ICD-10" Or keyCell.Value = "NDC" Then
        Set Cells1 = Range("B22:C25")
        Set Cells2 = Range("B29:C32")
        Cells1.EntireRow.Hidden = True
        Cells2.EntireRow.Hidden = True
    Else
        Set Cells1 = Range("B21:C25")
        Set Cells2 = Range("B28:C32")
        Cells1.EntireRow.Hidden = False
        Cells2.EntireRow.Hidden = False
    End If

End If
End Sub

我看过一些讨论此问题的帖子和教程，但我不明白为什么这不起作用。单元格 C9 是一个下拉列表，我希望运行此宏，以便根据列表中选择的内容显示/不显示单元格。但是，如果我给它参数(如上图)我不能在UI中运行它，如果我不给它参数，我只能手动运行它，这对我没有太大帮助。

现在，当我从 C9 下拉列表中选择某项时，没有任何反应。谁能帮我弄清楚为什么？

Answer 1

您的代码看起来适合Select Case 处理，关于 Worksheet_Change 事件宏有几件事要添加(太多无法评论)所以我继续编写了 Sub Worksheet_Change 的草稿。我不确定我是否已经解释了所有的 If ElseIf Else End If 但也许你可以看到我正在尝试用它做什么。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$9" Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False

        On Error GoTo Whoa

        Rows("21:25").EntireRow.Hidden = False
        Rows("28:32").EntireRow.Hidden = False
        Rows("33:39").EntireRow.Hidden = True
        Select Case Target.Value
            Case "DRG"
                Rows("33").EntireRow.Hidden = False
            Case "MSA"
                Rows("34:35").EntireRow.Hidden = False
            Case "UCR"
                Rows("33").EntireRow.Hidden = False
                Rows("36:39").EntireRow.Hidden = False
            Case "DRG", "ICD-9", "NCCI_Edits", "UB04"
                Rows("21:25").EntireRow.Hidden = True
                Rows("28:32").EntireRow.Hidden = True
            Case "ICD-10", "NDC"
                Rows("22:25").EntireRow.Hidden = True
                Rows("29:32").EntireRow.Hidden = True
            Case Else
                'do nothing
        End Select
    End If
FallThrough:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume FallThrough
End Sub

如果您出于自己的目的转录此内容时遇到任何问题，请发回评论，我会尽力提供帮助。