CommandBar code does NOT work if Excel is Already Open :(

[sbriscoe]

Hi Everyone
I am having a problem getting my CommandBar code working when there i
already an Excel Application running.

When I run my program and there are no other Excel processes running
my Tool Bar changes display properly (I am hiding the Save and Ne
buttons on the toolbar).

But - when I already have an Excel application up, then when I run th
application - it looks like it is using the Tool Bar from the existin
Excel and my Save and New buttons reappear

Is there anyway to fix this?

Thanks so much in advance.
Staci

 

[TheDuck]

Stacie,

You need to put some code in the ThisWorkbook object of you
spreadsheet that amends the toolbar.

The two events you need to look are:

Workbook_Activate

and

Workbook_Deactivate

In _Activate you place some code (or a call to a sub) to amend th
toolbar as required. In _Deactivate you place code (or a call to
different sub) to return the toolbar to its default state.

Where have you placed the code that amends the toolbar currently?

TheDuc

 

[sbriscoe]

Hi.
Here is the code that I am using:

theWorksheet.Activate();

ToolBar = ExcelApp.CommandBars["Standard"];
ToolBar.Controls["Save"].Visible = false;
ToolBar.Controls["Open"].Visible = false;
ToolBar.Controls["New"].Visible = false;

theWorksheet.Deactivate();


If Excel is already running on my machine - then the Save/New and Open
buttons are STILL visible.

If Excel is not already running - then the buttons are gone.

I am not sure how to solve this problem....

 

[Jon Peltier]

How do you assign the Excel application to the ExcelApp object variable?
If Excel is already open, you need to use GetObject to find the existing
instance.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[sbriscoe]

Hi
I am just creating a new instance of the Excel application to use...

ExcelApp = new Excel.Application();

How would I get reference to the current Excel application in C#

 

[Jon Peltier]

How come everyone's asking about C# these days? I don't know how to use
C#, but I'll tell you the VB/VBA way, and hopefully you can figure out
how to use it.

Creating a new Excel instance is done this way:

Set ExcelApp = CreateObject("Excel.Application")

Accessing the currently open Excel instance is done similarly:

Set ExcelApp = GetObject(,"Excel.Application")

If there are multiple instances, I don't know how to specify which one
to use.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______