I'm sorry If I've asked this before but I can't find an answer to it.

I have the following 2 events in a worksheet module....
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

I run some codes, either in UserForms or general procedures that make
changes to this worksheet.
Q. Is there a way to by-pass those two events somehow?

As an example I have a uf that comes up if a cell is selected in that sheet.
So I need those worksheet_Change events to work at that point.
But then, the uf does some stuff like inputing data, clearing data, etc.
that I don't want those worksheet_Change events always to be checking for
any changes.

Part of one of the uf codes....

Private Sub OKButton_Click()
With Application
.Calculation = xlManual
End With
'Clear existing entry in cols P:R
ActiveSheet.Unprotect
ActiveCell.Offset(0, 3).Resize(1, 3).ClearContents

When it gets to this last line, the Worksheet_Change procedure is invoked as
obviously a change has happened (ClearContents).

Rob

application.enableevents = false
'do a bunch of stuff to change selection or values
application.enableevents = true



rob nobel wrote:
>
> I'm sorry If I've asked this before but I can't find an answer to it.
>
> I have the following 2 events in a worksheet module....
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> I run some codes, either in UserForms or general procedures that make
> changes to this worksheet.
> Q. Is there a way to by-pass those two events somehow?
>
> As an example I have a uf that comes up if a cell is selected in that sheet.
> So I need those worksheet_Change events to work at that point.
> But then, the uf does some stuff like inputing data, clearing data, etc.
> that I don't want those worksheet_Change events always to be checking for
> any changes.
>
> Part of one of the uf codes....
>
> Private Sub OKButton_Click()
> With Application
> .Calculation = xlManual
> End With
> 'Clear existing entry in cols P:R
> ActiveSheet.Unprotect
> ActiveCell.Offset(0, 3).Resize(1, 3).ClearContents
>
> When it gets to this last line, the Worksheet_Change procedure is invoked as
> obviously a change has happened (ClearContents).
>
> Rob

--

Dave Peterson
(E-Mail Removed)

Aww, I'm such a dunce! I knew I would have seen that b4.
Works great. Thanks Dave.

Rob

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> application.enableevents = false
> 'do a bunch of stuff to change selection or values
> application.enableevents = true
>
>
>
> rob nobel wrote:
> >
> > I'm sorry If I've asked this before but I can't find an answer to it.
> >
> > I have the following 2 events in a worksheet module....
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >
> > I run some codes, either in UserForms or general procedures that make
> > changes to this worksheet.
> > Q. Is there a way to by-pass those two events somehow?
> >
> > As an example I have a uf that comes up if a cell is selected in that
sheet.
> > So I need those worksheet_Change events to work at that point.
> > But then, the uf does some stuff like inputing data, clearing data, etc.
> > that I don't want those worksheet_Change events always to be checking
for
> > any changes.
> >
> > Part of one of the uf codes....
> >
> > Private Sub OKButton_Click()
> > With Application
> > .Calculation = xlManual
> > End With
> > 'Clear existing entry in cols P:R
> > ActiveSheet.Unprotect
> > ActiveCell.Offset(0, 3).Resize(1, 3).ClearContents
> >
> > When it gets to this last line, the Worksheet_Change procedure is
invoked as
> > obviously a change has happened (ClearContents).
> >
> > Rob
>
> --
>
> Dave Peterson
> (E-Mail Removed)