simple code problem

[Dave W]

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

 

[Bob Phillips]

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]

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?

 

[Bob Phillips]

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]

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)




.