Custom Button on Toolbar

S

SOS

Hi All,

I have created a custom button to which I have attached a small macr
which shows a UserForm that I fill in to populate a certain row in m
spreadsheet.

Unfortunately the custom button appears as soon as I open Excel and m
position is that I only want this button to appear in the toolbar whe
that specific workbook is open and not to show when I open any othe
workbook. Can anyone point me in the right direction as to how t
accomplish this?

TIA

Seamu
 
P

papou

Hello Seamus
Tools, Customize, Tool bars tab, select your tool bar and click on "Attach".
(please note this option may vary since I hold a french version)
On the next window, click on "Copy" and OK, close the other Window and save
your workbook.
Next time your custom tool bar will only show with your workbook.

HTH
Cordially
Pascal
 
S

SOS

Hi pascal,

Thanks for the reply but it is not a *toolbar* I want to appear bu
just a -Custom Button- which I have added to (it so happens to be) th
Formatting Toolbar and I only want this button to appear when I ope
that specific file.

Regard
 
P

papou

Well in which case I cannot think of an alternative without VBA since this
button is in fact memorised in your .XLB file (which contains menus and Tool
bars parameters)
HTH
Cordially
Pascal
 
S

SOS

Thanks for the reply. I have since found a suggestion that I put som
code into Open_Workbook to create a custom item and then have th
Workbook_Before_Close section delete it again. I post the code in cas
anyone else is interested

In the module put the following code:

Sub create_menubar()
Dim mac_name As Variant
Dim cap_names As Variant
Dim tip_text As Variant

Call remove_menubar

mac_name = "Mileage_Form_Show"
cap_name = "Click Me"
tip_text = "Mileage Macro"

With Application.CommandBars.Add
.Name = " "
.Left = 750
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & mac_name
.Caption = cap_name
.Style = msoButtonIcon
.FaceId = 941
.TooltipText = tip_text
End With
End With
End Sub

Sub remove_menubar()
On Error Resume Next
Application.CommandBars(" ").Delete
On Error GoTo 0
End Sub

In the Workbook_Open section put:

Private Sub Workbook_Open()
Call create_menubar
End Sub

and in the close section:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call remove_menubar
End Sub

Regards

Seamu
 
T

tedsec - ExcelForums.com

-I am creating a worksheet that has a custom toolbar and will b
distributed to many users on several platforms (including Mac OS X
Mac OS 9.x, and Windows). These users will then use this file as
template to create other forms and reports. I attached the toolba
as listed above and ran into this problem

I come to a fresh machine and load a worksheet with this custo
toolbar, let's say the worksheet is "Form.xls" When it loads for th
first time, the custom toolbar appears and the macros are save
under

Form.xls!Module1.MacroNam
Say I do a Save As and change the name to "Report.xls" Now the Macro
for that toolbar are saved under

Report.xls!Module1.MacroNam

When I re-open "Report.xls" and try to use the toolbar, there is a
error saying that it can't find the macros anymore because they'v
been saved in the "Report.xls" file. Now, let's say I quit Excel an
open a brand new, blank workbook. I can still access my custo
toolbar, and its Macros are still listed as

Report.xls!Module1.MacroNam

Since the toolbar and Macros still appear even when after the Repor
file is closed, that leads me to believe that when I first ran th
toolbar it was loaded somewhere into a preferences file. Is ther
any way I can set up my original workbook so that when my users sav
multiple copies, the Macros are somehow retained

-tedse
 
Top