Custom Excel toolbar loses macro names 4

[BazJ]

I have created a toolbar, not using code, and placed it into a shared area of the LAN. I can open the file and the toolbar works fine ...... but when another user on a diffrent PC opens the file, the attached macro name for each button is either blank or correct but points to an older file name.

I can input the correct names on the second PC and all appears to work fine, but with so many users I cannot afford the luxury of visting everyones terminal.

I have tried attaching the custom toolbar to the file and it appears to make little difference.

Any ideas why the names keep changing and are not permanently attached.

BazJ

 

[asjeff]

I had this problem with a custom toolbar that I created to the Personal.xls that was for distribution to my users. I found that Excel, unlike Word, will not automatically overwrite old toolbars with the same name. Even if I deleted the personal.xls completely the toolbar still seemed to remain. The way I got round it was to create it by code, but ensuring that at the beginning of the procedure was a commandline that deleted the old toolbar and added it again once that was done. In this way I ensured that the old toolbar was always removed and the fresh one added.

I attach a sample of the code to help you on your way:

Dim cbrAccounts As CommandBar
Dim cbcOpenBill As CommandBarButton
Dim cbcOpenCredit As CommandBarButton


Sub AutoExec()

Application.CommandBars("Accounts Forms&quot.Delete
Set cbrAccounts = Application.CommandBars.Add
cbrAccounts.Name = "Accounts Forms"
cbrAccounts.Position = msoBarTop

With cbrAccounts.Controls
Set cbcOpenBill = .Add(msoControlButton)
Set cbcOpenCredit = .Add(msoControlButton)

cbcOpenBill.Caption = "Bill Template"
cbcOpenCredit.Caption = "Credit Note"

cbcOpenBill.DescriptionText = "Click to open the Bill template"
cbcOpenCredit.DescriptionText = "Click to open the Credit Note template"

cbcOpenBill.Style = msoButtonCaption
cbcOpenCredit.Style = msoButtonCaption

cbcOpenCredit.BeginGroup = True

'call required procedures when buttons clicked
cbcOpenBill.OnAction = "OpenBill"
cbcOpenCredit.OnAction = "OpenCreditNote"

End With

cbrAccounts.Visible = True

End Sub


Try the above - I think that you'll find this cures your problem.

Asjeff

 

[BazJ]

Very many thanx. A top tip.

BazJ

 

[beniez]

Baz

Just a word: as you found Asjeff's tip useful, you might like to award him a star in recognition. It's a great way of saying thanks - I know I appreciate getting them!

Ben

 

[rmikesmith]

I second AsJeff's take on this problem. For custom toolbars, I always create and dispose of them via code. Like AsJeff, my toolbar creation sub starts with a deletion of this same toolbar. I recommend using an

On Error Resume Next

statement just before the one deleting the custom toolbar, in order to prevent an error occurring if the custom toolbar doesn't exist.

Regards,
M. Smith

 

[neiljabba]

Hello all


I take it from what I am reading that the best way to make sure that a toolbar is available for all users using the workbook is to create it in code. Is that right or do I have an alternaitve as Id like to assign a toolbar to different workbooks for different users.

If I do this:
1. how can I add in non custom buttons such as view comment to my custom toolbar on creation.
2. where am I best creating the toolbar on auto open or?

Many thanks

Neil