ActiveSheet & Apropriate Reference

[James C.]

Hello,

I am trying to use the following bit of code and I am having trouble. I
believe I am missing a reference in the tools menu because when I type
ActiveSheet. nothing appears afterwards. If I had the right library loaded
then it should appear. Does anyone have any ideas of how I can find out which
library to use?

ActiveSheet.PivotTables("FocusMktList").RefreshTable

These are the libraries that I have loaded
- Visual Basic For Applications
- Microsoft Excel 9.0
- OLE Automation
- Mcirosoft Office 9.0
- Microsoft Forms 2.0 Library

[Tom Ogilvy]

What you observe is correct. Activesheet is ambiguous - like selection. To
illustrate from the immediate window:

? typename(Activesheet)
Worksheet
? typename(Activesheet)
Chart

The feature you speak of does not try to resolve the object within the
current context.

--
Regards,
Tom Ogilvy


"James C." wrote:

> Hello,
>
> I am trying to use the following bit of code and I am having trouble. I
> believe I am missing a reference in the tools menu because when I type
> ActiveSheet. nothing appears afterwards. If I had the right library loaded
> then it should appear. Does anyone have any ideas of how I can find out which
> library to use?
>
> ActiveSheet.PivotTables("FocusMktList").RefreshTable
>
> These are the libraries that I have loaded
> - Visual Basic For Applications
> - Microsoft Excel 9.0
> - OLE Automation
> - Mcirosoft Office 9.0
> - Microsoft Forms 2.0 Library
>

[James C.]

Not sure I understand. I have this type of code throughtout my module. I have
never had any problems running it until just recently. I ran this on a newer
version of excel, the module crashed and gives me the following error.

"Unable to get the PivotTables property of the Worksheet class"

I get this on everysingle one of my ActiveSheet...... lines?

Any ideas.



"Tom Ogilvy" wrote:

> What you observe is correct. Activesheet is ambiguous - like selection. To
> illustrate from the immediate window:
>
> ? typename(Activesheet)
> Worksheet
> ? typename(Activesheet)
> Chart
>
> The feature you speak of does not try to resolve the object within the
> current context.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "James C." wrote:
>
> > Hello,
> >
> > I am trying to use the following bit of code and I am having trouble. I
> > believe I am missing a reference in the tools menu because when I type
> > ActiveSheet. nothing appears afterwards. If I had the right library loaded
> > then it should appear. Does anyone have any ideas of how I can find out which
> > library to use?
> >
> > ActiveSheet.PivotTables("FocusMktList").RefreshTable
> >
> > These are the libraries that I have loaded
> > - Visual Basic For Applications
> > - Microsoft Excel 9.0
> > - OLE Automation
> > - Mcirosoft Office 9.0
> > - Microsoft Forms 2.0 Library
> >

[Tom Ogilvy]

I was talking about your test for the proper library by checking activesheet.

Pivottable objects are in the Excel object library.

I suspect you have created a variable or a procedure with the name
activesheet. This is masking the true activesheet object.

--
Regards,
Tom Ogilvy




"James C." wrote:

> Not sure I understand. I have this type of code throughtout my module. I have
> never had any problems running it until just recently. I ran this on a newer
> version of excel, the module crashed and gives me the following error.
>
> "Unable to get the PivotTables property of the Worksheet class"
>
> I get this on everysingle one of my ActiveSheet...... lines?
>
> Any ideas.
>
>
>
> "Tom Ogilvy" wrote:
>
> > What you observe is correct. Activesheet is ambiguous - like selection. To
> > illustrate from the immediate window:
> >
> > ? typename(Activesheet)
> > Worksheet
> > ? typename(Activesheet)
> > Chart
> >
> > The feature you speak of does not try to resolve the object within the
> > current context.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "James C." wrote:
> >
> > > Hello,
> > >
> > > I am trying to use the following bit of code and I am having trouble. I
> > > believe I am missing a reference in the tools menu because when I type
> > > ActiveSheet. nothing appears afterwards. If I had the right library loaded
> > > then it should appear. Does anyone have any ideas of how I can find out which
> > > library to use?
> > >
> > > ActiveSheet.PivotTables("FocusMktList").RefreshTable
> > >
> > > These are the libraries that I have loaded
> > > - Visual Basic For Applications
> > > - Microsoft Excel 9.0
> > > - OLE Automation
> > > - Mcirosoft Office 9.0
> > > - Microsoft Forms 2.0 Library
> > >

[Chip Pearson]

You don't get Intellisense support because ActiveSheet isn't
always a worksheet. It might be a chart sheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"James C." <JamesC@discussions.microsoft.com> wrote in message
news:96D7B13E-BB51-4017-9096-223D7D8751CB@microsoft.com...
> Hello,
>
> I am trying to use the following bit of code and I am having
> trouble. I
> believe I am missing a reference in the tools menu because when
> I type
> ActiveSheet. nothing appears afterwards. If I had the right
> library loaded
> then it should appear. Does anyone have any ideas of how I can
> find out which
> library to use?
>
> ActiveSheet.PivotTables("FocusMktList").RefreshTable
>
> These are the libraries that I have loaded
> - Visual Basic For Applications
> - Microsoft Excel 9.0
> - OLE Automation
> - Mcirosoft Office 9.0
> - Microsoft Forms 2.0 Library
>