Perplexed...

[rci]

Hi all...


odd thing... I have a worksheet_change event that fires fine... but
interestingly, in the code when I say:

Sheets("Sheet2").Select
Range("A5").Select

It will select the sheet, but will NOT select A5.

I wonder if the change function is doing this to prevent an infinite loop...

Hmmm...

How to get around this... (BTW, sheet2 does NOT have a change event)

Thx,

SMS

[Jim Thomlinson]

When you refer to one sheet in code from anohter sheet you need to make the
full delclaration... try this

Sheets("Sheet5").Range("A5").Select


HTH

"rci" wrote:

> Hi all...
>
>
> odd thing... I have a worksheet_change event that fires fine... but
> interestingly, in the code when I say:
>
> Sheets("Sheet2").Select
> Range("A5").Select
>
> It will select the sheet, but will NOT select A5.
>
> I wonder if the change function is doing this to prevent an infinite loop...
>
> Hmmm...
>
> How to get around this... (BTW, sheet2 does NOT have a change event)
>
> Thx,
>
> SMS
>
>

[Dave Peterson]

When you're in a General module, unqualified ranges (like Range("a5") refer to
the activesheet.

When you're in the worksheet module, this unqualified range refers to the sheet
that owns the code.

So if your worksheet_change even were for "Sheet1", your code is the same as:

sheets("sheet2").select
sheets("sheet1").range("a5").select

And you can't select a range unless that worksheet is active.

You could fully qualify the range like Jim wrote:

sheets("sheet2").select
Sheets("Sheet2").Range("A5").Select

or you could save some typing with the with/end with structure:

with sheets("sheet2")
.select
.range("a5").select
end with

That being said, you can do most things without selecting the worksheet/range at
all.

with sheets("sheet2")
with .range("a5")
.value = "this is a test"
.numberformat="General"
end with
end with




rci wrote:
>
> Hi all...
>
> odd thing... I have a worksheet_change event that fires fine... but
> interestingly, in the code when I say:
>
> Sheets("Sheet2").Select
> Range("A5").Select
>
> It will select the sheet, but will NOT select A5.
>
> I wonder if the change function is doing this to prevent an infinite loop...
>
> Hmmm...
>
> How to get around this... (BTW, sheet2 does NOT have a change event)
>
> Thx,
>
> SMS

--

Dave Peterson

[Nate Oliver]

Hello,

Not sure when I started to be able to replicate that error, but I can with
XP. Try the following:

Application.Goto Sheets("Sheet2").Range("A5")

Do not try:

Sheets("Sheet2").Range("A5").Select

If Sheet2 is not active, this will also give you an error.

Regards,
Nate Oliver

[rci]

Thanks so much... I just moved all the code to a module, and I'm much
happier now :-)

SMS


Dave Peterson <[email protected]> wrote:
: When you're in a General module, unqualified ranges (like Range("a5") refer to
: the activesheet.

: When you're in the worksheet module, this unqualified range refers to the sheet
: that owns the code.

: So if your worksheet_change even were for "Sheet1", your code is the same as:

: sheets("sheet2").select
: sheets("sheet1").range("a5").select

: And you can't select a range unless that worksheet is active.

: You could fully qualify the range like Jim wrote:

: sheets("sheet2").select
: Sheets("Sheet2").Range("A5").Select

: or you could save some typing with the with/end with structure:

: with sheets("sheet2")
: .select
: .range("a5").select
: end with

: That being said, you can do most things without selecting the worksheet/range at
: all.

: with sheets("sheet2")
: with .range("a5")
: .value = "this is a test"
: .numberformat="General"
: end with
: end with


:

: rci wrote:
:>
:> Hi all...
:>
:> odd thing... I have a worksheet_change event that fires fine... but
:> interestingly, in the code when I say:
:>
:> Sheets("Sheet2").Select
:> Range("A5").Select
:>
:> It will select the sheet, but will NOT select A5.
:>
:> I wonder if the change function is doing this to prevent an infinite loop...
:>
:> Hmmm...
:>
:> How to get around this... (BTW, sheet2 does NOT have a change event)
:>
:> Thx,
:>
:> SMS

: --

: Dave Peterson