WorkSheet Event - Copied

[RWN]

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?

 

[Sharad Naik]

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

 

[Rob van Gelder]

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

 

[Sharad Naik]

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]

Sharad,

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

Rob

 

[RWN]

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.