+ Reply to Thread
Results 1 to 6 of 6

WorkSheet Event - Copied

  1. #1
    RWN
    Guest

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



  2. #2
    Sharad Naik
    Guest

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




  3. #3
    Rob van Gelder
    Guest

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

    >
    >




  4. #4
    Sharad Naik
    Guest

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

    >>
    >>

    >
    >




  5. #5
    Rob van Gelder
    Guest

    Re: 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" <[email protected]> wrote in message
    news:uga1j%[email protected]...
    > 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" <[email protected]> wrote in message
    > news:%23s%[email protected]...
    >> 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" <[email protected]> wrote in message
    >> news:%[email protected]...
    >>> 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" <[email protected]> wrote in message
    >>> news:[email protected]...
    >>>> 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
    >>>> ------------------------------------------------------------------------
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >




  6. #6
    RWN
    Guest

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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1