slang
10-01-2012, 06:43 AM
I have a sheet that rows hide based on values entered into specific cells.
The problem is that there are 30 values pasted into this sheet from a database when the sheet is activated which makes the code run for each cell pasted.
Here is the on change code
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="donttouch"
rows("5").EntireRow.Hidden = Range("e4").Value < 1
rows("8").EntireRow.Hidden = Range("e7").Value < 1
rows("11:12").EntireRow.Hidden = Range("e10").Value < 1
rows("15:17").EntireRow.Hidden = Range("e14").Value < 1
rows("20:22").EntireRow.Hidden = Range("e19").Value < 1
rows("25").EntireRow.Hidden = Range("e24").Value <> "y"
rows("32").EntireRow.Hidden = Range("e31").Value <> "y"
rows("35:36").EntireRow.Hidden = Range("e34").Value <> "y"
ActiveSheet.Protect Password:="donttouch", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
Here is the sheet activate code
I know, long hand at the best:blush
Private Sub Worksheet_Activate()
ActiveSheet.Unprotect Password:="donttouch"
Range("E4").Select
Range("e4").Value = Sheets("questions").Range("w192")
Range("e5").Value = Sheets("questions").Range("x192")
Range("e7").Value = Sheets("questions").Range("y192")
Range("e8").Value = Sheets("questions").Range("z192")
Range("e10").Value = Sheets("questions").Range("aa192")
Range("e11").Value = Sheets("questions").Range("ab192")
Range("e12").Value = Sheets("questions").Range("ac192")
Range("e14").Value = Sheets("questions").Range("ad192")
Range("e15").Value = Sheets("questions").Range("ae192")
Range("e16").Value = Sheets("questions").Range("af192")
Range("e17").Value = Sheets("questions").Range("ag192")
Range("e19").Value = Sheets("questions").Range("ah192")
Range("e20").Value = Sheets("questions").Range("ai192")
Range("e21").Value = Sheets("questions").Range("aj192")
Range("e22").Value = Sheets("questions").Range("ak192")
Range("e24").Value = Sheets("questions").Range("al192")
Range("e25").Value = Sheets("questions").Range("am192")
Range("e26").Value = Sheets("questions").Range("an192")
Range("e28").Value = Sheets("questions").Range("ao192")
Range("e29").Value = Sheets("questions").Range("ap192")
Range("e31").Value = Sheets("questions").Range("aq192")
Range("c33").Value = Sheets("questions").Range("ar192")
Range("e38").Value = Sheets("questions").Range("as192")
Range("c44").Value = Sheets("questions").Range("av192")
Range("E4").Select
ActiveSheet.Protect Password:="donttouch", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Is there a cleaner way to do this or stop the on change code until all the values are updated?:dunno
Its a monster..
Thanks oh gods of Excel.
The problem is that there are 30 values pasted into this sheet from a database when the sheet is activated which makes the code run for each cell pasted.
Here is the on change code
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="donttouch"
rows("5").EntireRow.Hidden = Range("e4").Value < 1
rows("8").EntireRow.Hidden = Range("e7").Value < 1
rows("11:12").EntireRow.Hidden = Range("e10").Value < 1
rows("15:17").EntireRow.Hidden = Range("e14").Value < 1
rows("20:22").EntireRow.Hidden = Range("e19").Value < 1
rows("25").EntireRow.Hidden = Range("e24").Value <> "y"
rows("32").EntireRow.Hidden = Range("e31").Value <> "y"
rows("35:36").EntireRow.Hidden = Range("e34").Value <> "y"
ActiveSheet.Protect Password:="donttouch", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
Here is the sheet activate code
I know, long hand at the best:blush
Private Sub Worksheet_Activate()
ActiveSheet.Unprotect Password:="donttouch"
Range("E4").Select
Range("e4").Value = Sheets("questions").Range("w192")
Range("e5").Value = Sheets("questions").Range("x192")
Range("e7").Value = Sheets("questions").Range("y192")
Range("e8").Value = Sheets("questions").Range("z192")
Range("e10").Value = Sheets("questions").Range("aa192")
Range("e11").Value = Sheets("questions").Range("ab192")
Range("e12").Value = Sheets("questions").Range("ac192")
Range("e14").Value = Sheets("questions").Range("ad192")
Range("e15").Value = Sheets("questions").Range("ae192")
Range("e16").Value = Sheets("questions").Range("af192")
Range("e17").Value = Sheets("questions").Range("ag192")
Range("e19").Value = Sheets("questions").Range("ah192")
Range("e20").Value = Sheets("questions").Range("ai192")
Range("e21").Value = Sheets("questions").Range("aj192")
Range("e22").Value = Sheets("questions").Range("ak192")
Range("e24").Value = Sheets("questions").Range("al192")
Range("e25").Value = Sheets("questions").Range("am192")
Range("e26").Value = Sheets("questions").Range("an192")
Range("e28").Value = Sheets("questions").Range("ao192")
Range("e29").Value = Sheets("questions").Range("ap192")
Range("e31").Value = Sheets("questions").Range("aq192")
Range("c33").Value = Sheets("questions").Range("ar192")
Range("e38").Value = Sheets("questions").Range("as192")
Range("c44").Value = Sheets("questions").Range("av192")
Range("E4").Select
ActiveSheet.Protect Password:="donttouch", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Is there a cleaner way to do this or stop the on change code until all the values are updated?:dunno
Its a monster..
Thanks oh gods of Excel.