Problem with VBA

Andy_H1965

New Member
Joined
Oct 31, 2018
Messages
3
Hi there

I'm having an issue with a VBA macro- the first half works, however the second 'rule' isn't (in bold type below). I've scoured the forums but can't find an answer.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C23")) Is Nothing Then Exit Sub
Select Case Target.Value
Case "Contractor", "Panel Builder"
ActiveSheet.Rows("63:66").Hidden = False
Case "OEM", "System Integrator"
ActiveSheet.Rows("63:66").Hidden = True
ActiveSheet.Rows("48:49").Hidden = False
ActiveSheet.Rows("51").Hidden = True
End Select

If Intersect(Target, Range("C9")) Is Nothing Then Exit Sub
Select Case Target.Value
Case "Amend"
ActiveSheet.Row("55").Hidden = False
Case "New", "Close", "Transfer"
ActiveSheet.Row("55").Hidden = True
End Select




End Sub


So, basically trying to hide row 55 if the value of cell C9 meets criteria, but it just won't work for me.

Any ideas?? I'm probably missing something really simple but struggling to find the answer. Thanks in advance :)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi & welcome to MrExcel.
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("C23")) Is Nothing Then
      Select Case Target.Value
         Case "Contractor", "Panel Builder"
            Rows("63:66").Hidden = False
         Case "OEM", "System Integrator"
            Rows("63:66").Hidden = True
            Rows("48:49").Hidden = False
            Rows("51").Hidden = True
      End Select
   ElseIf Not Intersect(Target, Range("C9")) Is Nothing Then
      Select Case Target.Value
         Case "Amend"
            Row("55").Hidden = False
         Case "New", "Close", "Transfer"
            Row("55").Hidden = True
      End Select
   End If
End Sub
 
Upvote 0
Your code will exit here if any cell other than C23 has been changed.
Code:
If Intersect(Target, Range("C23")) Is Nothing Then Exit Sub
So the code to check the value of C9 is never reached.

There are various ways to fix this, here's one.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C9, C23")) Is Nothing Then Exit Sub

    If Target.Row = 9 Then
        Select Case Target.Value
            Case "Amend"
                Me.Rows("55").Hidden = False
            Case "New", "Close", "Transfer"
                Me.Rows("55").Hidden = True
        End Select
    Else
    
        Select Case Target.Value
            Case "Contractor", "Panel Builder"
                Me.Rows("63:66").Hidden = False
            Case "OEM", "System Integrator"
                Me.Rows("63:66").Hidden = True
                Me.Rows("48:49").Hidden = False
                Me.Rows("51").Hidden = True
        End Select
    End If
    
End Sub
 
Upvote 0
Thanks, that makes sense. All good except row 55 is now hidden regardless of which dropdown option I select in cell C9:)
 
Upvote 0
Whose code are you referring too?
 
Upvote 0
Your code will exit here if any cell other than C23 has been changed.
Code:
If Intersect(Target, Range("C23")) Is Nothing Then Exit Sub
So the code to check the value of C9 is never reached.

There are various ways to fix this, here's one.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C9, C23")) Is Nothing Then Exit Sub

    If Target.Row = 9 Then
        Select Case Target.Value
            Case "Amend"
                Me.Rows("55").Hidden = False
            Case "New", "Close", "Transfer"
                Me.Rows("55").Hidden = True
        End Select
    Else
    
        Select Case Target.Value
            Case "Contractor", "Panel Builder"
                Me.Rows("63:66").Hidden = False
            Case "OEM", "System Integrator"
                Me.Rows("63:66").Hidden = True
                Me.Rows("48:49").Hidden = False
                Me.Rows("51").Hidden = True
        End Select
    End If
    
End Sub

Thanks, that makes sense. All good except row 55 is now hidden regardless of which dropdown option I select in cell C9:)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top