Hi,
I copied this code from Debra Dalgleish's wonderful site contextures.com. It
does not work for me. I placed the code in a module in the workbook. It
should zoom when the active cell has Data Valadation. When I activate a cell
the code will not fire. Any suggestions? Please help.
TIA
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim intZoom As Integer
Dim intZoomDV As Integer
intZoom = 100
intZoomDV = 120
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler
If rngDV Is Nothing Then GoTo errHandler
If Intersect(Target, rngDV) Is Nothing Then
With ActiveWindow
If .Zoom < intZoom Then
.Zoom = intZoom
End If
End With
Else
With ActiveWindow
If .Zoom < intZoomDV Then
.Zoom = intZoomDV
End If
End With
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

You have to right click on the sheet tab and select view code. Then place
the code in that module.

--
Regards,
Tom Ogilvy

"Ray A" wrote in message
...
Hi,
I copied this code from Debra Dalgleish's wonderful site contextures.com.
It
does not work for me. I placed the code in a module in the workbook. It
should zoom when the active cell has Data Valadation. When I activate a
cell
the code will not fire. Any suggestions? Please help.
TIA
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim intZoom As Integer
Dim intZoomDV As Integer
intZoom = 100
intZoomDV = 120
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler
If rngDV Is Nothing Then GoTo errHandler
If Intersect(Target, rngDV) Is Nothing Then
With ActiveWindow
If .Zoom < intZoom Then
.Zoom = intZoom
End If
End With
Else
With ActiveWindow
If .Zoom < intZoomDV Then
.Zoom = intZoomDV
End If
End With
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

Tom,
I opened the VBA editor and inserted a module then pasted the code. I can
not make the code fire when I activate a cell containing Data Validation. I
suspect I am doing something wrong but I am not sure what. I doubt the code
is flawed. I did have to remove the sheet protection....
Confused in Chicago

"Tom Ogilvy" wrote:

You have to right click on the sheet tab and select view code. Then place
the code in that module.

--
Regards,
Tom Ogilvy

"Ray A" wrote in message
...
Hi,
I copied this code from Debra Dalgleish's wonderful site contextures.com.
It
does not work for me. I placed the code in a module in the workbook. It
should zoom when the active cell has Data Valadation. When I activate a
cell
the code will not fire. Any suggestions? Please help.
TIA
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim intZoom As Integer
Dim intZoomDV As Integer
intZoom = 100
intZoomDV = 120
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler
If rngDV Is Nothing Then GoTo errHandler
If Intersect(Target, rngDV) Is Nothing Then
With ActiveWindow
If .Zoom < intZoom Then
.Zoom = intZoom
End If
End With
Else
With ActiveWindow
If .Zoom < intZoomDV Then
.Zoom = intZoomDV
End If
End With
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

did you close excel and then re open it and the workbook. IF not, try that.
There is a possibility you have disabled events.

They can be enabled by running code like

Sub Backon()
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Ray A" wrote in message
...
Tom,
I opened the VBA editor and inserted a module then pasted the code. I can
not make the code fire when I activate a cell containing Data Validation.
I
suspect I am doing something wrong but I am not sure what. I doubt the
code
is flawed. I did have to remove the sheet protection....
Confused in Chicago

"Tom Ogilvy" wrote:

You have to right click on the sheet tab and select view code. Then
place
the code in that module.

--
Regards,
Tom Ogilvy

"Ray A" wrote in message
...
Hi,
I copied this code from Debra Dalgleish's wonderful site
contextures.com.
It
does not work for me. I placed the code in a module in the workbook.
It
should zoom when the active cell has Data Valadation. When I activate
a
cell
the code will not fire. Any suggestions? Please help.
TIA
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim intZoom As Integer
Dim intZoomDV As Integer
intZoom = 100
intZoomDV = 120
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler
If rngDV Is Nothing Then GoTo errHandler
If Intersect(Target, rngDV) Is Nothing Then
With ActiveWindow
If .Zoom < intZoom Then
.Zoom = intZoom
End If
End With
Else
With ActiveWindow
If .Zoom < intZoomDV Then
.Zoom = intZoomDV
End If
End With
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

I'm pretty sure Debra included some instructions...

code for these event handlers must reside in the codemodule of the
worksheet NOT in a standard module.

in VBE's project explorer. doubleclick the sheetname. copy your code
there.

Also in VBE immediate pane, type
?Application.EnableEvents
must return TRUE.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ray A wrote in


Please help.

How high are your security settings set? Could it be that it's auto disabled
because they're set to High??

"Ray A" wrote:

Hi,
I copied this code from Debra Dalgleish's wonderful site contextures.com. It
does not work for me. I placed the code in a module in the workbook. It
should zoom when the active cell has Data Valadation. When I activate a cell
the code will not fire. Any suggestions? Please help.
TIA
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim intZoom As Integer
Dim intZoomDV As Integer
intZoom = 100
intZoomDV = 120
Application.EnableEvents = False
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo errHandler
If rngDV Is Nothing Then GoTo errHandler
If Intersect(Target, rngDV) Is Nothing Then
With ActiveWindow
If .Zoom < intZoom Then
.Zoom = intZoom
End If
End With
Else
With ActiveWindow
If .Zoom < intZoomDV Then
.Zoom = intZoomDV
End If
End With
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub