Results 1 to 6 of 6

Thread: Problem with VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Problem with VBA

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,118
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Problem with VBA

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,152
    Post Thanks / Like
    Mentioned
    61 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Problem with VBA

    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
    If posting code please use code tags.

  4. #4
    New Member
    Join Date
    Oct 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with VBA

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

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,118
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Problem with VBA

    Whose code are you referring too?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Oct 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problem with VBA

    Quote Originally Posted by Norie View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •