如何让我的宏在单元格选择上运行? [英] How can I get my Macro to run on cell selection?

问题描述

我对编程并不陌生，但是对在Excel中使用宏并不陌生.我正在使用Excel 2010，尝试运行以下宏:

I am not new to programming, but I am new to using macros in Excel. I am using Excel 2010, trying to run the following macro:

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中运行它；如果不给它参数，则只能手动运行它，对我没有多大帮助.

I have seen several postings and tutorials that talk about this, but I can't understand why this won't work. Cell C9 is a dropdown list, and I want this macro to run so that cells are shown / not shown based on what is selected in the list. However, if I give it parameters (as shown above) I can't run it in the UI, and if I don't give it parameters, I can only run it manually, which doesn't help me much.

现在，当我从该C9下拉列表中选择某项时，什么也没发生.谁能帮我找出原因?

Right now, when I select something from that C9 dropdown list, nothing happens. Can anyone help me figure out why?

推荐答案

对于Select Case处理，您的代码看起来很成熟，关于Worksheet_Change事件宏，有几件事情要添加(太多了，无法发表评论)，所以我继续并撰写了Sub Worksheet_Change的草案.我不确定是否已经解释了所有If ElseIf Else End If，但是也许您可以看到我正在尝试使用的方法.

Your code looked ripe for a Select Case treatment and there were several things to add about the Worksheet_Change event macro (too many for a comment) so I went ahead and wrote up a draft of the Sub Worksheet_Change. I'm not sure if I have interpreted all of the If ElseIf Else End If but perhaps you can see what I'm trying to do with this.

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

如果您出于个人目的将其转录为任何问题，请发回评论.

Post back into Comments with any problem you have transcribing this for your own purposes and I'll try to assist.

这篇关于如何让我的宏在单元格选择上运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！