PDA

View Full Version : Solved: Can I put a toolbar in spreadsheet file?



clhare
02-18-2008, 09:57 AM
I have some macros that I want to make available to any users of a spreadsheet. If I save the macros in the actual .xls file, is it possible to also put a toolbar in that file to make the macros easier to use by anyone working in this spreadsheet?

I don't see an option that allows me to select where the toolbar is stored and it looks like the one I created has been automatically stored in my Personal Workbook (as it appears to be available whether I'm in that particular spreadsheet or not) even tho the macros linked to the toolbar are not in my Personal Workbook.

Bob Phillips
02-18-2008, 10:09 AM
As an example



Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub


To add this, go to the VB IDE (ALT-F11 from Excel), and in
the explorer pane, select your workbook. Then select the
ThisWorkbook object (it's in Microsoft Excel Objects which
might need expanding). Double-click the ThisWorkbook and
a code window will open up. Copy this code into there,
changing the caption and action to suit.

This is part of the workbook, and will only exist with the
workbook, but will be available to anyone who opens the
'workbook.

clhare
02-18-2008, 10:48 AM
I've added the macro into ThisWorkbook and updated it for my toolbar, but when I open the workbook I don't see it. Do I need to do something else? I checked the toolbar listing and I don't see it.

Bob Phillips
02-18-2008, 10:57 AM
Nope that code does it all.

clhare
02-18-2008, 11:02 AM
I just checked it again and see that I actually ended up putting it in the Personal Workbook. I tried to move it to the actual worksheet file, but even though the file is open, it won't let me open the project for it. I get "Project unviewable". What am I doing wrong?

Bob Phillips
02-18-2008, 11:15 AM
Difficult to say. Is it locked, shared?

clhare
02-18-2008, 11:35 AM
It was shared! I disabled that and was able to add the macros. Instead of the images, I'd like to just use text on the toolbar. I removed the code for faceid, but that leaves me with nothing showing up on the toolbar.

Also, will the toolbar and macros still be available if the template is returned to "shared" status?

Bob Phillips
02-18-2008, 11:43 AM
As well as removing faceid add this line for each control



.Style = msoButtonCaption


You might want to add a separator between them to aid clarity.

As to shared workbooks, I can't say. I never use them as they are more trouble than they are worth. You will have to try it and see.

clhare
02-18-2008, 11:59 AM
Thank you so much for all your help. I've learned so much today!!