How can I get my macro to work when a cell is selected?
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've seen several posts and guides that talk about this, but I can't figure out why it won't work. Cell C9 is a dropdown and I want this macro to execute so that the cells show / not show depending on what is selected in the list. However, if I give it parameters (as shown above), I cannot start it in the UI, and if I don't give it parameters, I can only start it manually, which doesn't help me much.
Right now, when I select something from this C9 dropdown, nothing happens. Can anyone help me understand why?
source to share
Your code looked ripe for processing Select Case
and a few things were added about the event macro Worksheet_Change
(too many to comment), so I went ahead and wrote a draft Sub Worksheet_Change
. I'm not sure if I have interpreted everything If ElseIf Else End If
, but perhaps you can see what I am 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 in the comments with any problem you rewrite for your own purposes and I'll try to help.
source to share