I have a lot of code in Sheet 1, and at all works fine. However I need Sheet
2 to be the same. I copied sheet 1 to sheet 2, then I copied the code for
the worksheet change event from sheet 1 to sheet, but the code isn't running.

Why is this? The code does not referr to a sheet name. In fact I made the
first line of the code

debug.print "Got this far"

But even this simple line dosn't execute. What have I hone wrong?

I selected worksheet and the change event from the top of the code window, I
didn't copy that over from Sheet 1 in case that made any difference.

Hi Keith,

One possibility is that Events have been inadvertently disabled.

In the immediate window (Ctrl-G), try:

Applicatiion.EnableEvents:=True

and hit the enter key.


If this does not resolve your problem, post the problematic code.


---
Regards,
Norman



"Keith" <(E-Mail Removed)> wrote in message
news:17CCFEA4-DB95-4C8A-8A5B-(E-Mail Removed)...
>I have a lot of code in Sheet 1, and at all works fine. However I need
>Sheet
> 2 to be the same. I copied sheet 1 to sheet 2, then I copied the code for
> the worksheet change event from sheet 1 to sheet, but the code isn't
> running.
>
> Why is this? The code does not referr to a sheet name. In fact I made
> the
> first line of the code
>
> debug.print "Got this far"
>
> But even this simple line dosn't execute. What have I hone wrong?
>
> I selected worksheet and the change event from the top of the code window,
> I
> didn't copy that over from Sheet 1 in case that made any difference.

Hi Keith,

A typo crept in!

> Applicatiion.EnableEvents:=True

should read:

Application.EnableEvents:=True


---
Regards,
Norman

Kieth, I helps those who want to help you if you would post the code that
causes the problem. Then they don't have to guess about what you are trying
to describe.

"Keith" wrote:

> I have a lot of code in Sheet 1, and at all works fine. However I need Sheet
> 2 to be the same. I copied sheet 1 to sheet 2, then I copied the code for
> the worksheet change event from sheet 1 to sheet, but the code isn't running.
>
> Why is this? The code does not referr to a sheet name. In fact I made the
> first line of the code
>
> debug.print "Got this far"
>
> But even this simple line dosn't execute. What have I hone wrong?
>
> I selected worksheet and the change event from the top of the code window, I
> didn't copy that over from Sheet 1 in case that made any difference.

Just some added information:

If you copied the sheet (and not just the data on the sheet), the event code
would be copied as well. Perhaps you should try this for similar situations
in the future.

to refer to the sheet containing the code, use the ME qualifier rather than
explicitely using the sheet name. This would make you code a bit more
robust.

When referring to the sheet with the code, Rather than

set rng = sheet1.Range("A1")
or
set rng = Worksheets("Sheet1").Range("A1")

use
set rng = me.Range("A1")

as an example.
--
Regards,
Tom Ogilvy


"Keith" wrote:

> I have a lot of code in Sheet 1, and at all works fine. However I need Sheet
> 2 to be the same. I copied sheet 1 to sheet 2, then I copied the code for
> the worksheet change event from sheet 1 to sheet, but the code isn't running.
>
> Why is this? The code does not referr to a sheet name. In fact I made the
> first line of the code
>
> debug.print "Got this far"
>
> But even this simple line dosn't execute. What have I hone wrong?
>
> I selected worksheet and the change event from the top of the code window, I
> didn't copy that over from Sheet 1 in case that made any difference.

Thanks to everyone for their help and sugestions. After looking at the first
response from Norman I noticed that the original code was no longer working
either.

Using Me is a good idea but in this case it would not have helped since I
was using ActiveSheet in my code and not a sheet name.

I have started again from the point where the original sheet is working
again and instead of copying everything I copied the code only then in sheet
2 allowed Excel to create the subroutine header and end sub and simply pasted
the code in-between.

This worked fine.

Once again thanks for all your help.

Keith


"Tom Ogilvy" wrote:

> Just some added information:
>
> If you copied the sheet (and not just the data on the sheet), the event code
> would be copied as well. Perhaps you should try this for similar situations
> in the future.
>
> to refer to the sheet containing the code, use the ME qualifier rather than
> explicitely using the sheet name. This would make you code a bit more
> robust.
>
> When referring to the sheet with the code, Rather than
>
> set rng = sheet1.Range("A1")
> or
> set rng = Worksheets("Sheet1").Range("A1")
>
> use
> set rng = me.Range("A1")
>
> as an example.
> --
> Regards,
> Tom Ogilvy
>
>
> "Keith" wrote:
>
> > I have a lot of code in Sheet 1, and at all works fine. However I need Sheet
> > 2 to be the same. I copied sheet 1 to sheet 2, then I copied the code for
> > the worksheet change event from sheet 1 to sheet, but the code isn't running.
> >
> > Why is this? The code does not referr to a sheet name. In fact I made the
> > first line of the code
> >
> > debug.print "Got this far"
> >
> > But even this simple line dosn't execute. What have I hone wrong?
> >
> > I selected worksheet and the change event from the top of the code window, I
> > didn't copy that over from Sheet 1 in case that made any difference.