PDA

View Full Version : Macro breaking when any edits to sheet are made


jjmnb
09-29-2017, 08:14 AM
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
mdmackillop
09-29-2017, 08:39 AM
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
Paul_Hossler
09-29-2017, 08:46 AM
@jjmnb --

I added
.... macro goes here .... tags for you

It sets off and formats the macro for easier readability

You can use the [#] icon to insert
.... 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
mdmackillop
09-29-2017, 09:04 AM
It sets off and formats the macro for easier readability
:rotflmao:
p45cal
09-29-2017, 10:17 AM
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