disabling tool bar

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NathanG - 14 Feb 2007 14:49 GMT

Hi,

As part of a system a new tool bar was created in excel to allow the user to
use the system. The process which takes place is that a user creates a new
spreadsheet from access based on the criteria selected. The tool bar appears
automatically. The problem is that when a new workbook is opened totally
unrelated to the system the tool bar still appears. I have played around with
VBA code a little to see if I could add a line that only adds the tool bar
for the active spreadsheet for not excel as a whole.

I have used onopen and onclose (or macros to that effect) before that remove
toolbars and when the user closes the spreadsheet all the tool bars etc are
added back in as normal. I don't recall the macros being shared with all
excel spreadsheets subsequently opened.

How can I ensure the custom tool bar only appears on the spreadsheets I want
it to?

Many thanks

Nathan

Reply to this Message

Tom Ogilvy - 14 Feb 2007 15:10 GMT

Create it when the workbook is opened. Delete it when the workbook is
closed.

Signature

Regards,
Tom Ogilvy

> Hi,
>
[quoted text clipped - 23 lines]
>
> Nathan

Reply to this Message

Joel - 14 Feb 2007 15:14 GMT

I good tip for new excel programmers is to record new macro and then see what
code is generated by excel. I tried this with the tool bar and got the
following line of code

Application.CommandBars("Chart").Visible = True

where chart is one of the standard tool bars.

You can add this code into a macro that automatically starts
by calling the macro

Sub auto_open()

> Hi,
>
[quoted text clipped - 17 lines]
>
> Nathan

Reply to this Message

Ron de Bruin - 14 Feb 2007 15:20 GMT

Hi NathanG

See Dave's tip on Debra's site
http://www.contextures.com/xlToolbar02.html

You can also use this two events in the thisworkbook module if you only want to see the
toolbar when the workbook is active

Private Sub Workbook_Activate()
'name of your macro
End Sub

Private Sub Workbook_Deactivate()
'name of your macro
End Sub

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Hi,
>
[quoted text clipped - 17 lines]
>
> Nathan

Reply to this Message