Code will not fire

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" <[email protected]> wrote in message
news:[email protected]...
> 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
<news:<[email protected]>

> Please help.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
> >

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
>
>