Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple code problem
I have imported some code to enlarge the window while
picking data valadation lists from the Contextures website When i put these on the same sheet code module I get an compile error mesaage It won't let me use Worksheet_SelectionChange twice on the same sheet module what is the best solution to this? Is it alright just to insert the second line of code after the first without the Private Sub Worksheet...sentence. Thanks in advance for any help Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$2" Then ActiveWindow.Zoom = 120 Else ActiveWindow.Zoom = 100 End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub If Intersect(Target, rngDV) Is Nothing Then ActiveWindow.Zoom = 100 Else ActiveWindow.Zoom = 120 End If Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple code problem
They are doing the same thing, on different ranges. Delete the first.
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave W" wrote in message ... I have imported some code to enlarge the window while picking data valadation lists from the Contextures website When i put these on the same sheet code module I get an compile error mesaage It won't let me use Worksheet_SelectionChange twice on the same sheet module what is the best solution to this? Is it alright just to insert the second line of code after the first without the Private Sub Worksheet...sentence. Thanks in advance for any help Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$2" Then ActiveWindow.Zoom = 120 Else ActiveWindow.Zoom = 100 End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub If Intersect(Target, rngDV) Is Nothing Then ActiveWindow.Zoom = 100 Else ActiveWindow.Zoom = 120 End If Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple code problem
thank you for the quick answer but the first one just
zooms when selecting a single cell and the second one zooms when any cell that contains data validation list is selected. I have a cell that I want to zoom that does not have a data validation list. Should I combine the codes some how? -----Original Message----- They are doing the same thing, on different ranges. Delete the first. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave W" wrote in message ... I have imported some code to enlarge the window while picking data valadation lists from the Contextures website When i put these on the same sheet code module I get an compile error mesaage It won't let me use Worksheet_SelectionChange twice on the same sheet module what is the best solution to this? Is it alright just to insert the second line of code after the first without the Private Sub Worksheet...sentence. Thanks in advance for any help Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$2" Then ActiveWindow.Zoom = 120 Else ActiveWindow.Zoom = 100 End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False On Error Resume Next Set rngDV = Cells.SpecialCells (xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub If Intersect(Target, rngDV) Is Nothing Then ActiveWindow.Zoom = 100 Else ActiveWindow.Zoom = 120 End If Application.EnableEvents = True End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple code problem
Dave,
Try this then Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False On Error GoTo ws_exit Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) If rngDV Is Nothing Then Exit Sub Select Case True Case Not Intersect(Target, rngDV) Is Nothing: ActiveWindow.Zoom = 120 Case Target.Address = "$A$2": ActiveWindow.Zoom = 120 Case Else: ActiveWindow.Zoom = 100 End Select ws_exit: Application.EnableEvents = True On Error GoTo 0 End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave W" wrote in message ... thank you for the quick answer but the first one just zooms when selecting a single cell and the second one zooms when any cell that contains data validation list is selected. I have a cell that I want to zoom that does not have a data validation list. Should I combine the codes some how? -----Original Message----- They are doing the same thing, on different ranges. Delete the first. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave W" wrote in message ... I have imported some code to enlarge the window while picking data valadation lists from the Contextures website When i put these on the same sheet code module I get an compile error mesaage It won't let me use Worksheet_SelectionChange twice on the same sheet module what is the best solution to this? Is it alright just to insert the second line of code after the first without the Private Sub Worksheet...sentence. Thanks in advance for any help Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$2" Then ActiveWindow.Zoom = 120 Else ActiveWindow.Zoom = 100 End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False On Error Resume Next Set rngDV = Cells.SpecialCells (xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub If Intersect(Target, rngDV) Is Nothing Then ActiveWindow.Zoom = 100 Else ActiveWindow.Zoom = 120 End If Application.EnableEvents = True End Sub . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple code problem
Thank you, that's what I needed.
-----Original Message----- Dave, Try this then Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False On Error GoTo ws_exit Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) If rngDV Is Nothing Then Exit Sub Select Case True Case Not Intersect(Target, rngDV) Is Nothing: ActiveWindow.Zoom = 120 Case Target.Address = "$A$2": ActiveWindow.Zoom = 120 Case Else: ActiveWindow.Zoom = 100 End Select ws_exit: Application.EnableEvents = True On Error GoTo 0 End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave W" wrote in message ... thank you for the quick answer but the first one just zooms when selecting a single cell and the second one zooms when any cell that contains data validation list is selected. I have a cell that I want to zoom that does not have a data validation list. Should I combine the codes some how? -----Original Message----- They are doing the same thing, on different ranges. Delete the first. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave W" wrote in message ... I have imported some code to enlarge the window while picking data valadation lists from the Contextures website When i put these on the same sheet code module I get an compile error mesaage It won't let me use Worksheet_SelectionChange twice on the same sheet module what is the best solution to this? Is it alright just to insert the second line of code after the first without the Private Sub Worksheet...sentence. Thanks in advance for any help Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$2" Then ActiveWindow.Zoom = 120 Else ActiveWindow.Zoom = 100 End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False On Error Resume Next Set rngDV = Cells.SpecialCells (xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub If Intersect(Target, rngDV) Is Nothing Then ActiveWindow.Zoom = 100 Else ActiveWindow.Zoom = 120 End If Application.EnableEvents = True End Sub . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Help with some simple code | Excel Discussion (Misc queries) | |||
simple code | Excel Programming | |||
Help With Very Simple Code | Excel Programming | |||
Simple For Each Next code | Excel Programming |