Hi Bob,
I'm using the Worksheet_Calculate event to hide some columns dependant
on the formula updated change in cell A6. When users enter data
anywhere on the sheet, I'd like them to be able to use the Undo icon in
case they want to revert back to the original data prior to overwriting.
How do I make changes to my code to make it possible? Is it possible to
lock cell A6 to calculate only when it's changed? So that the Undo icon
would still be feasible? Here's my code:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("A6").Value = 1 Then
Range("B:B").EntireColumn.Hidden = False
Range("C:C").EntireColumn.Hidden = True
Range("D
").EntireColumn.Hidden = True
Range("E:E").EntireColumn.Hidden = True
Range("AA:AA").EntireColumn.Hidden = False
Range("AB:AB").EntireColumn.Hidden = True
Range("AC:AC").EntireColumn.Hidden = True
Range("AD:AD").EntireColumn.Hidden = True
ElseIf Range("A6").Value = 2 Then
Range("B:B").EntireColumn.Hidden = False
Range("C:C").EntireColumn.Hidden = False
Range("D
").EntireColumn.Hidden = True
Range("E:E").EntireColumn.Hidden = True
Range("AA:AA").EntireColumn.Hidden = False
Range("AB:AB").EntireColumn.Hidden = False
Range("AC:AC").EntireColumn.Hidden = True
Range("AD:AD").EntireColumn.Hidden = True
ElseIf Range("A6").Value = 3 Then
Range("B:B").EntireColumn.Hidden = True
Range("C:C").EntireColumn.Hidden = False
Range("D
").EntireColumn.Hidden = False
Range("E:E").EntireColumn.Hidden = True
Range("AA:AA").EntireColumn.Hidden = True
Range("AB:AB").EntireColumn.Hidden = False
Range("AC:AC").EntireColumn.Hidden = False
Range("AD:AD").EntireColumn.Hidden = True
ElseIf Range("A6").Value = 4 Then
Range("B:B").EntireColumn.Hidden = True
Range("C:C").EntireColumn.Hidden = True
Range("D
").EntireColumn.Hidden = False
Range("E:E").EntireColumn.Hidden = False
Range("AA:AA").EntireColumn.Hidden = True
Range("AB:AB").EntireColumn.Hidden = True
Range("AC:AC").EntireColumn.Hidden = False
Range("AD:AD").EntireColumn.Hidden = False
Else
Range("B:AD").EntireColumn.Hidden = False
End If
Application.EnableEvents = True
End Sub
*** Sent via Developersdex
http://www.developersdex.com ***