WorkSheet Event - Copied
 

xl2k on win2kPro.

I have a workbook that has a "Worksheet_SelectionChange" event on the initial sheet that
works just fine.

Once entry is complete the user runs a macro (located in a separate module) that copies
the sheet (creates another sheet in the same workbook via "OrdEnt.Copy After:=OrdEnt").

Works fine, except;
The worksheet change event can still be fired on the newly created (copied) sheet.
I suppose I could copy/paste the sheet to get rid of the change event but was wondering if
there's another way?

Hints?
--
Regards;
Rob
------------------------------------------------------------------------

WorkSheet Event - Copied
 

1) Instead of copying sheet you insert a new sheet and copy the used rage in
sheet OrdEnt to the new sheet.
e.g.:
Dim newSheet As Worksheet
Set newSheet = Worksheets.Add(After:=OrdEnt)
With Worksheets("OrdEnt").UsedRange
.Copy Destination:=newSheet.Cells(.Cells(1, 1).Row, .Cells(1,
1).Column)
End With

2) OR in the Worksheet_SelectionChange code of OrdEnt, Add following line
at the top:
If Not Me.Name = "OrdEnt" Then Exit Sub
So the code will be copied to new sheet, event will be fired but will exit
immediately with above
first line.

3) OR see below link - how to remove the code, through VBA
http://www.cpearson.com/excel/vbe.htm

Sharad


"RWN" wrote in message
...
xl2k on win2kPro.

I have a workbook that has a "Worksheet_SelectionChange" event on the
initial sheet that
works just fine.

Once entry is complete the user runs a macro (located in a separate
module) that copies
the sheet (creates another sheet in the same workbook via "OrdEnt.Copy
After:=OrdEnt").

Works fine, except;
The worksheet change event can still be fired on the newly created
(copied) sheet.
I suppose I could copy/paste the sheet to get rid of the change event but
was wondering if
there's another way?

Hints?
--
Regards;
Rob
------------------------------------------------------------------------

WorkSheet Event - Copied
 

Some good suggestions there.
Just to add...

UsedRange does not necessarily start on row/column 1.


You could move the Worksheet events into the Workbook.
That way the worksheet doesnt contain code.
eg.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Sh.CodeName = "OrdEnt" Then
Beep
End If
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Sharad Naik" wrote in message
...
1) Instead of copying sheet you insert a new sheet and copy the used rage
in sheet OrdEnt to the new sheet.
e.g.:
Dim newSheet As Worksheet
Set newSheet = Worksheets.Add(After:=OrdEnt)
With Worksheets("OrdEnt").UsedRange
.Copy Destination:=newSheet.Cells(.Cells(1, 1).Row, .Cells(1,
1).Column)
End With

2) OR in the Worksheet_SelectionChange code of OrdEnt, Add following line
at the top:
If Not Me.Name = "OrdEnt" Then Exit Sub
So the code will be copied to new sheet, event will be fired but will exit
immediately with above
first line.

3) OR see below link - how to remove the code, through VBA
http://www.cpearson.com/excel/vbe.htm

Sharad


"RWN" wrote in message
...
xl2k on win2kPro.

I have a workbook that has a "Worksheet_SelectionChange" event on the
initial sheet that
works just fine.

Once entry is complete the user runs a macro (located in a separate
module) that copies
the sheet (creates another sheet in the same workbook via "OrdEnt.Copy
After:=OrdEnt").

Works fine, except;
The worksheet change event can still be fired on the newly created
(copied) sheet.
I suppose I could copy/paste the sheet to get rid of the change event but
was wondering if
there's another way?

Hints?
--
Regards;
Rob
------------------------------------------------------------------------

WorkSheet Event - Copied
 

Yes moving to Workbook class is good and simplest solution for him!
So RWN, if I were you I would go with Ron's suggestion.

BTW, just for informaion : - yes UsedRange does not necessarily start at
row/column 1,
But UsedRange.Cells(1, 1).Row returns the row where it starts (and
simillarly .Column returns the Column where it starts),
hence the code I gave, copies it on new sheet same place as it was on
original sheet.

Sharad

"Rob van Gelder" wrote in message
...
Some good suggestions there.
Just to add...

UsedRange does not necessarily start on row/column 1.


You could move the Worksheet events into the Workbook.
That way the worksheet doesnt contain code.
eg.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Sh.CodeName = "OrdEnt" Then
Beep
End If
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Sharad Naik" wrote in message
...
1) Instead of copying sheet you insert a new sheet and copy the used rage
in sheet OrdEnt to the new sheet.
e.g.:
Dim newSheet As Worksheet
Set newSheet = Worksheets.Add(After:=OrdEnt)
With Worksheets("OrdEnt").UsedRange
.Copy Destination:=newSheet.Cells(.Cells(1, 1).Row, .Cells(1,
1).Column)
End With

2) OR in the Worksheet_SelectionChange code of OrdEnt, Add following
line at the top:
If Not Me.Name = "OrdEnt" Then Exit Sub
So the code will be copied to new sheet, event will be fired but will
exit immediately with above
first line.

3) OR see below link - how to remove the code, through VBA
http://www.cpearson.com/excel/vbe.htm

Sharad


"RWN" wrote in message
...
xl2k on win2kPro.

I have a workbook that has a "Worksheet_SelectionChange" event on the
initial sheet that
works just fine.

Once entry is complete the user runs a macro (located in a separate
module) that copies
the sheet (creates another sheet in the same workbook via "OrdEnt.Copy
After:=OrdEnt").

Works fine, except;
The worksheet change event can still be fired on the newly created
(copied) sheet.
I suppose I could copy/paste the sheet to get rid of the change event
but was wondering if
there's another way?

Hints?
--
Regards;
Rob
------------------------------------------------------------------------

WorkSheet Event - Copied
 

Sharad,

I missed the With. Your technique is correct - my apologies.

Rob


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Sharad Naik" wrote in message
...
Yes moving to Workbook class is good and simplest solution for him!
So RWN, if I were you I would go with Ron's suggestion.

BTW, just for informaion : - yes UsedRange does not necessarily start at
row/column 1,
But UsedRange.Cells(1, 1).Row returns the row where it starts (and
simillarly .Column returns the Column where it starts),
hence the code I gave, copies it on new sheet same place as it was on
original sheet.

Sharad

"Rob van Gelder" wrote in message
...
Some good suggestions there.
Just to add...

UsedRange does not necessarily start on row/column 1.


You could move the Worksheet events into the Workbook.
That way the worksheet doesnt contain code.
eg.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
If Sh.CodeName = "OrdEnt" Then
Beep
End If
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Sharad Naik" wrote in message
...
1) Instead of copying sheet you insert a new sheet and copy the used
rage in sheet OrdEnt to the new sheet.
e.g.:
Dim newSheet As Worksheet
Set newSheet = Worksheets.Add(After:=OrdEnt)
With Worksheets("OrdEnt").UsedRange
.Copy Destination:=newSheet.Cells(.Cells(1, 1).Row, .Cells(1,
1).Column)
End With

2) OR in the Worksheet_SelectionChange code of OrdEnt, Add following
line at the top:
If Not Me.Name = "OrdEnt" Then Exit Sub
So the code will be copied to new sheet, event will be fired but will
exit immediately with above
first line.

3) OR see below link - how to remove the code, through VBA
http://www.cpearson.com/excel/vbe.htm

Sharad


"RWN" wrote in message
...
xl2k on win2kPro.

I have a workbook that has a "Worksheet_SelectionChange" event on the
initial sheet that
works just fine.

Once entry is complete the user runs a macro (located in a separate
module) that copies
the sheet (creates another sheet in the same workbook via "OrdEnt.Copy
After:=OrdEnt").

Works fine, except;
The worksheet change event can still be fired on the newly created
(copied) sheet.
I suppose I could copy/paste the sheet to get rid of the change event
but was wondering if
there's another way?

Hints?
--
Regards;
Rob
------------------------------------------------------------------------

WorkSheet Event - Copied
 

Thanks to both Ron & Sharad.
(used the exit based on sheet name)
Also learned something, that's what I get for being "lazy" when I copy a sheet and then
add an event!

Again, thanks.

--
Regards;
Rob
------------------------------------------------------------------------
"RWN" wrote in message ...
xl2k on win2kPro.

I have a workbook that has a "Worksheet_SelectionChange" event on the initial sheet that
works just fine.

Once entry is complete the user runs a macro (located in a separate module) that copies
the sheet (creates another sheet in the same workbook via "OrdEnt.Copy After:=OrdEnt").

Works fine, except;
The worksheet change event can still be fired on the newly created (copied) sheet.
I suppose I could copy/paste the sheet to get rid of the change event but was wondering
if
there's another way?

Hints?
--
Regards;
Rob
------------------------------------------------------------------------