Disappearing buttons

Topic

New Reply

Hello everybody

I’ve recently created VBA macros to be used by people at the office in excel 97.
I’ve also added buttons to the existing toolbars to make the access to these macros easier.

There is only one problem, after a while the buttons I’ve added seem to disappear into thin air without anyone playing around with the toolbars.
Has anyone ever encountered this problem and did you solve it ?

I would appreciate a quick answer.

Thanks
Have a nice day

Reply | Quote

Replies

You can easily reset a built-in toolbar to its default state. If you’ve added any custom tools to the toolbar, they are removed. This can be done by choosing View >> Toolbars >> Customize, choose the toolbar tab, select the toolbar you want to reset and press the reset button. Are you sure your users haven’t done that?
Macros that are resetting commandbars may also cause this problem.
If you want to store a toolbar in a workbook file, you need to click the Attach button to bring up the Attach Toolbars dialog box (after choosing View >> Toolbars >> Customize to bring up the customize dialog box, of course). To attach a toolbar, select it in the left listbox and click the Copy button.

Hope this helps.

Reply | Quote

I’m sure that nobody reset the toolbars, as far as macros reseting the toolbars, I’ll have to check though I doubt it.
The buttons I created were not attached to any worksheet as such, they were added to the existing toolbar in excel.

Reply | Quote

I’m under the impression that default toolbars are not machine specific but machine/user specific – I believe this to be the case for windows 95/98/ME. Is it possible that when the toolbar reverts to default the user has logged onto windows by a different name from which the modified toolbars were set up?

Brooke

Reply | Quote

Some macros that temporarily put buttons on the Excel toolbars will remove them by just resetting the toolbar to its default (all macro coders are not guaranteed to do things right). Therefore, I find that it is usually not a good practice to put buttons that you want to stay around on Excel’s toolbars. I create custom tool bars and put them there. It is still possible for macros to hide all tool bars, and then only restore the default Excel bars, but that is more rare and the custom toolbar is still available in the view menu and can be more easily restored.

Reply | Quote

I read this solution somewhere but can’t find the source now. I had a similar problem with my own toolbar customizations disappearing, and some buttons having nothing to do with macros. The default toolbar, Excel.xlb, is stored in each user’s personal settings folder. (The folder name varies with what version of Windows you have.) Each time I make a change to my toolbars I save a copy of Excel.xlb to another name (Jim’s toolbar.xlb or whatever) in the
C:Program FilesMicrosoft OfficeOfficeXLStart folder. It loads after Excel.xlb so I get my changes. I’m not sure this is much help but it might give you a clue.

Reply | Quote

Thanks Jim for the infor about the .XLB file. I did not know that. If you find any solution for that specefic problem, give me some news about it

The computers are not configured to be used a multi-user. They have one configuration so I would be suprised that the problem is caused by another user login in to a specefic computer.

Reply | Quote

Now that Jim mentioned the xlb file, I remember something that might cause your problem. Excel’s approach to storing toolbars can cause problems. In your case, you attached a new toolbar button to an existing toolbar. When your user exits Excel, the toolbar is saved in the user’s xlb file. If he or she opens another workbook for which the toolbar was altered, then by exiting Excel, this last toolbar is saved in the user’s xlb file and the next time you open Excel, your toolbar button is gone.
The best way to prevent this is to create the toolbar on the fly every time the workbook is opened that contains the macros you want to be available, or by writing an add-in that does this and delete your toolbar button when your application closes. With this method, the toolbar is never stored in the xlb file.

If you put the AddToolBarButton procedure under the Workbook_Open event of the Thisworkbook Micorsoft Excel Object of the VBAproject (in the VBE) then this procedure will be launched when opening the workbook, creating a toolbarbutton on the Standard toolbarbutton toolbar, with FaceId = 348, (you can experiment with this to choose your own icon). The onaction method should contain the name of the macro you want to be fired when you click the button. The caption property contains the tooltip.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteToolBarButton
End Sub

Private Sub Workbook_Open()
AddToolBarButton
End Sub

The procedures below should be placed in a module. (Add a module by choosing Insert >> Module in the VBE).

Sub AddToolBarButton()
Dim NewButton As CommandBarButton
Set NewButton = Application.CommandBars(3).Controls.Add(Type:=msoControlButton)
With NewButton
.FaceId = 348
.OnAction = “MyMacro”
.Caption = “MyMacro”
End With
End Sub

Add the DeleteToolBarButton procedure to the WorkBook_BeforeClose event.

Sub DeleteToolBarButton()
Application.CommandBars(3).Controls(“MyMacro”).Delete
End Sub

Put your macro in this module too:

Sub MyMacro()
….
End Sub

Reply | Quote

It is quite possible to open a different .xlb file whilst XL is running, if you do this the toolbar/button set associated with that .xlb file will be displayed. This is quite useful if you often use a particular set of buttons for certain types of applications. If you close XL at this stage your normal .xlb will be overwritten, therefore make sure you have a copy of your this as Standard.xlb.

In XL97 there is more information on this topic under toolbars/About toolbars, and toolbars/multiple configurations.

Custom lists are also stored in .xlb files (plus something else that I cannot recall -this was discussed in the old lounge).

Reply | Quote