Consulting

Results 1 to 5 of 5

Thread: Macro breaking when any edits to sheet are made

  1. 09-29-2017, 08:14 AM #1
    jjmnb
    jjmnb is offline
    VBAX Newbie
    Joined
    Sep 2017
    Posts
    1
    Location

    Question Macro breaking when any edits to sheet are made

    Hi,

    I'm pretty much a novice at VBA, but have been trying to work on a fillable sheet where there is a drop down function in cell C2 that contains a list of countries. When a country is selected, I want certain non-contiguous rows in the same sheet to be hidden. When another country is selected, the same concept applies, but potentially some of the previous hidden rows are unhidden, and some of the previously shown rows are hidden.

    I have this aspect working, but when any entry is made in the sheet the macro stops working and all rows are unhidden. Hopefully someone can give me some guidance as to how to have this work, even if that includes some form of button so the macro only works when pressed? My attempt at the code is below:

    Thanks in advance!

    ======================================

    Private Sub Worksheet_Change(ByVal Target As Range)
        
         
    If Target.Column = 3 And Target.Row = 2 And Target.Value = "Select Country" Then
        Rows("4:75").Hidden = True
       Else
        Rows("4:75").Hidden = False
    
    If Target.Column = 3 And Target.Row = 2 And Target.Value = "Australia" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
         If Target.Column = 3 And Target.Row = 2 And Target.Value = "Austria" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
         If Target.Column = 3 And Target.Row = 2 And Target.Value = "Belgium" Then
        Rows("7:68").Hidden = True
    Range("7:9,11:14,18:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
      If Target.Column = 3 And Target.Row = 2 And Target.Value = "Canada" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
         If Target.Column = 3 And Target.Row = 2 And Target.Value = "Chile" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
        If Target.Column = 3 And Target.Row = 2 And Target.Value = "Czech Republic" Then
        Rows("7:68").Hidden = True
    Range("8:10,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
    
     If Target.Column = 3 And Target.Row = 2 And Target.Value = "Denmark" Then
        Rows("7:68").Hidden = True
    Range("8:10,11:14,19:22,24:27,29:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
        If Target.Column = 3 And Target.Row = 2 And Target.Value = "Finland" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
       If Target.Column = 3 And Target.Row = 2 And Target.Value = "France" Then
        Rows("7:68").Hidden = True
    Range("7:9,11:14,19:23,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
        If Target.Column = 3 And Target.Row = 2 And Target.Value = "Germany" Then
        Rows("7:68").Hidden = True
    Range("8:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
        If Target.Column = 3 And Target.Row = 2 And Target.Value = "Greece" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
                
         If Target.Column = 3 And Target.Row = 2 And Target.Value = "Hungary" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,68:72").EntireRow.Hidden = False
    Else
       Rows("7:68").Hidden = False
        
    If Target.Column = 3 And Target.Row = 2 And Target.Value = "Ireland" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
    
    If Target.Column = 3 And Target.Row = 2 And Target.Value = "Italy" Then
        Rows("7:68").Hidden = True
    Range("7:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
    
    If Target.Column = 3 And Target.Row = 2 And Target.Value = "Japan" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:23,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
    
         If Target.Column = 3 And Target.Row = 2 And Target.Value = "Luxembourg" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
    
      If Target.Column = 3 And Target.Row = 2 And Target.Value = "Netherlands" Then
        Rows("7:68").Hidden = True
    Range("7:9,11:14,19:22,24:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
    
     If Target.Column = 3 And Target.Row = 2 And Target.Value = "Norway" Then
        Rows("7:68").Hidden = True
    Range("8:9,10:17,19:22,24:27,29:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
    
     If Target.Column = 3 And Target.Row = 2 And Target.Value = "Poland" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:17,19:22,52:67,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
    
    If Target.Column = 3 And Target.Row = 2 And Target.Value = "Portugal" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
         If Target.Column = 3 And Target.Row = 2 And Target.Value = "Slovak Republic" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
         If Target.Column = 3 And Target.Row = 2 And Target.Value = "South Korea" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
         If Target.Column = 3 And Target.Row = 2 And Target.Value = "Spain" Then
        Rows("7:68").Hidden = True
    Range("7:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
        If Target.Column = 3 And Target.Row = 2 And Target.Value = "Sweden" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:17,19:22,24:27,29:29,32:34,37:39,42:44,47:49,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
         If Target.Column = 3 And Target.Row = 2 And Target.Value = "Switzerland" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
         If Target.Column = 3 And Target.Row = 2 And Target.Value = "United Kingdom" Then
        Rows("7:68").Hidden = True
    Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Else
        Rows("7:68").Hidden = False
        
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
End Sub
    Last edited by Paul_Hossler; 09-29-2017 at 08:44 AM. Reason: Added CODE Tags
    Reply With Quote Reply With Quote
  2. 09-29-2017, 08:39 AM #2
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base     VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    The bit you need for your code is:
    If Target.Address <> "$C$2" Then Exit Sub

    A better style for your code is:

    Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$C$2" Then Exit Sub
Rows("4:75").Hidden = False
Select Case Target
    Case "Select Country"
        Rows("4:75").Hidden = True
    Case "Australia"
        Rows("7:68").Hidden = True
        Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Case "Austria"
        Rows("7:68").Hidden = True
        Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    '..........
    Case Else
    
    
End Select
    
End Sub
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  3. 09-29-2017, 08:46 AM #3
    Paul_Hossler
    Paul_Hossler is online now
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,727
    Location
    @jjmnb --

    I added [CODE].... macro goes here .... [/CODE] tags for you

    It sets off and formats the macro for easier readability

    You can use the [#] icon to insert [CODE].... [/CODE] tags and just paste your macro between them


    And to add to mac's, you can have multiple values on the Case's which also saves typing and IMHO improves the readability

    And probably not necessary for just this, I like to always turn off further event handling until I leave the event handler


    Private Sub Worksheet_Change(ByVal Target As Range) 
     
    If Target.Address <> "$C$2" Then Exit Sub 

    Application.EnableEvents = False

    Rows("4:75").Hidden = False 

    Select Case Target 

    Case "Select Country" 
        Rows("4:75").Hidden = True 
    Case "Australia", "Norway", "Sweden"
        Rows("7:68").Hidden = True 
        Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False 
    Case "Austria", "Hungery", "Ireland", "Italy"
        Rows("7:68").Hidden = True 
        Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False 

    Case Else 
         
         
    End Select 


    Application.EnableEvents = True
     
End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  4. 09-29-2017, 09:04 AM #4
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    It sets off and formats the macro for easier readability
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  5. 09-29-2017, 10:17 AM #5
    p45cal
    p45cal is offline
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,875
    Since you have several countries requiring the same set of rows to be showing/hidden you can condense a bit; something along the lines of: 
    Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" Then
  Rows("4:75").Hidden = True
  Select Case Target
    Case "Select Country": Rows("4:75").Hidden = True
    Case "Australia", "Austria", "Canada", "Chile", "Finland", "Greece", "Ireland", "Luxembourg", "Portugal", "Slovak Republic", "South Korea", "Switzerland", "United Kingdom": Range("8:9,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Case "Belgium": Range("7:9,11:14,18:22,52:66,69:72").EntireRow.Hidden = False
    Case "Czech Republic": Range("8:10,11:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Case "Denmark": Range("8:10,11:14,19:22,24:27,29:66,69:72").EntireRow.Hidden = False
    Case "France": Range("7:9,11:14,19:23,52:66,69:72").EntireRow.Hidden = False
    Case "Germany": Range("8:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Case "Hungary": Range("8:9,11:14,19:22,52:66,68:72").EntireRow.Hidden = False
    Case "Italy", "Spain": Range("7:9,10:14,19:22,52:66,69:72").EntireRow.Hidden = False
    Case "Japan": Range("8:9,11:14,19:23,52:66,69:72").EntireRow.Hidden = False
    Case "Netherlands": Range("7:9,11:14,19:22,24:66,69:72").EntireRow.Hidden = False
    Case "Norway": Range("8:9,10:17,19:22,24:27,29:66,69:72").EntireRow.Hidden = False
    Case "Poland": Range("8:9,11:17,19:22,52:67,69:72").EntireRow.Hidden = False
    Case "Sweden": Range("8:9,11:17,19:22,24:27,29:29,32:34,37:39,42:44,47:49,52:66,69:72").EntireRow.Hidden = False
    Case Else: Rows("7:68").Hidden = False
  End Select
End If
End Sub
    Last edited by p45cal; 09-29-2017 at 10:29 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules