Create a toolbar that only shows for a specific workbook.

traversd · Mar 26th, 2002, 02:18 AM

Hi,

Does anyone know how to creat a toolbar with a button (to activate a macro) on that will only display for a specific workbook and then remove it'self when that workbook is closed ?

Any help appreciated....

Dave Hawley · Mar 26th, 2002, 02:21 AM

Hi

If it is a Custom toolbar then you will see the code for this here:
http://www.ozgrid.com/VBA/ExcelWorkbookEvents.htm

traversd · Mar 26th, 2002, 02:27 AM

Thanks Dave,

That lot looks complicated and will take a while to work through but i expect it's just what I'm looking for !!

Thanks Dave.

Dave Hawley · Mar 26th, 2002, 02:44 AM

Here is a much simplified version that does not hide all other toolbars. Your Custom Toolbar must be attached to your Workbook to use this.

Dim IsClosed As Boolean

Private Sub Workbook_Activate()
Application.CommandBars("MINE").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
IsClosed = Not Cancel
End Sub

Private Sub Workbook_Deactivate()
If IsClosed = True Then
Application.CommandBars("MINE").Delete
Else
Application.CommandBars("MINE").Visible = False
End If
End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 02:18 AM	#1
traversd Board Regular Join Date: Mar 2002 Posts: 82	Hi, Does anyone know how to creat a toolbar with a button (to activate a macro) on that will only display for a specific workbook and then remove it'self when that workbook is closed ? Any help appreciated....

Mar 26th, 2002, 02:21 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi If it is a Custom toolbar then you will see the code for this here: http://www.ozgrid.com/VBA/ExcelWorkbookEvents.htm

Mar 26th, 2002, 02:27 AM	#3
traversd Board Regular Join Date: Mar 2002 Posts: 82	Thanks Dave, That lot looks complicated and will take a while to work through but i expect it's just what I'm looking for !! Thanks Dave.

Mar 26th, 2002, 02:44 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Here is a much simplified version that does not hide all other toolbars. Your Custom Toolbar must be attached to your Workbook to use this. Dim IsClosed As Boolean Private Sub Workbook_Activate() Application.CommandBars("MINE").Visible = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) IsClosed = Not Cancel End Sub Private Sub Workbook_Deactivate() If IsClosed = True Then Application.CommandBars("MINE").Delete Else Application.CommandBars("MINE").Visible = False End If End Sub