Intellisense

K

Kramer

Hello,

This is probably a basic question, but can anyone tell me why the VBA
intellisense prompt (Excel 97) works on some objects and not on others?
For example, I get the drop-down menu after the period on this,
Range("A1:B2"), but not on these - Sheets(0), Worksheets(1). I'm sure
they used to work.

Thanks,

Dave
 
D

Dave Peterson

Sheets(0) could be a worksheet, a chartsheet, xlm macro sheet, ...

So there's not a good way for excel to know what to use.

I don't have a good (or any explanation) why worksheets(0) won't show the
intellisense stuff, though.

I do know that if I want the intellisense (and I usually do), I'll do:

dim wks as worksheet
set wks = worksheets(1)

wks.(and get the intellisense to pop up)
 
B

Bob Phillips

What is sheets(0) andworksheets(0) Dave? Is it the one you intend to add ;-)

Bob
 
K

Kramer

Bob


I meant to say sheets(1) and worksheets(1), all I want to do is
activate or select them

Dave
 
K

Kramer

Dave,

Thanks, using the wks variable works. But just out of curiosity, does
using worksheets(1) instead of wks work for you?

Dave
 
K

Kramer

Dave,

Thanks, using the wks variable works. But just out of curiosity, does
using worksheets(1) instead of wks work for you?

Dave
 
K

Kramer

Bob,

I meant to say sheets(1) and worksheets(1). They're existing
worksheets that I want to select or activate.

Dave
 
B

Bob Phillips

No it deosn't, it doesn't work for anyone, that is why Dave defines wks.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Dave,

I know, I was just mildly ribbing the other Dave.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

You are correct, sir.

(Ed McMahon voice!)



Bob said:
No it deosn't, it doesn't work for anyone, that is why Dave defines wks.

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
H

Harlan Grove

Dave Peterson wrote...
....
I don't have a good (or any explanation) why worksheets(0) won't show the
intellisense stuff, though.
Click to expand...
....

There's an obvious explanation: the Excel programmers screwed up. Step
into any VBA macro, and add ActiveWorkbook to the Watch Window, then
expand it. You'll see that its Worksheets property is of type Sheets
rather than of type Worksheets. This was a bug, whether it happened in
XL5 or XL97. It's been around so long I imagine Microsoft now considers
it a feature.
 
K

Kramer

Hello,

Thanks to everyone for their replies. I'm a bit of a bit of a newby to
VBA programming (as you've probably guessed), I was obviously mistaken
in saying "it used to work". Thanks again,

Dave
 
Top