CODEWITHSUNDEEP
vbaexcel
Sean
Sean

Reputation: 105

How can I get my Macro to run on cell selection?

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

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.

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

Upvotes: 3

Views: 2691

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 2

Related Questions