Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Custom Toolbars Usable by any User

  Asked By: Danielle    Date: Jan 28    Category: MS Office    Views: 535
  

I have created a custom toolbar containing buttons attached to vb code.
This toolbar is specific to a particular spreadsheet. How do I save
this toolbar to the spreadsheet so that it works on another users pc ?
I do not understand where custom toolbars are saved.

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Kristen Chavez     Answered On: Jan 28

office.microsoft.com/.../HA010548161033.aspx

In my Excel 2003, it seems to have gone to Excel11.xlb. Not inherently
shareable, unfortunately.

However, you could create it dynamically. E.g. this is me creating a test
one:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/03/2008 by xxx
'

'
Application.CommandBars.Add(Name:="Freddie").Visible = True
Application.CommandBars("Freddie").Controls.Add Type:=msoControlButton,
ID _
:=106, Before:=1
Application.CommandBars("Freddie").Controls.Add Type:=msoControlButton,
ID _
:=3, Before:=2
End Sub

You could perhaps make this conditional on whether it already exists or not.

 
Answer #2    Answered By: Jennie Harris     Answered On: Jan 28

but how do I find out whether a the toolbar  exists ?

 
Answer #3    Answered By: Melissa King     Answered On: Jan 28

To find out if the Command Bar exists, try:
For Each bar In Application.CommandBars
If bar.name = "My New Bar" Then
MsgBox "Menu Bar Exists: " & bar.Left _
& ", Width: " & bar.Width
If Not bar.Visible Then
bar.Visible = True
bar.Enabled = True
End If
Exit For
End If
Next
If the Tool Bar exists, but is not visible, it turns it on.

To make sure the most recent Command bar is available, I define it in
the Auto_Open sub of the file
'------------------------------------------------------
CMDbarName = "My New Bar"
Application.CommandBars(CMDbarName).Delete
'-------------------------------------------------------
' Create Command BAR
'-------------------------------------------------------
Set cComm = Application.CommandBars.Add
With cComm
.name = CMDbarName
.Position = msoBarTop
.RowIndex = msoBarRowFirst + 20
.Left = 600
.Visible = True
End With
'*******************************************************
'-------------------------------------------------------
' Open Tool Layouts
'-------------------------------------------------------
With cComm.Controls.Add(Type:=msoControlButton)
.Caption = "Open Document"
.Style = msoButtonIcon
.FaceId = 23
.OnAction = "Open_New_Document"
End With

 
Answer #4    Answered By: Clayton Richardson     Answered On: Jan 28

Never tried, but it'll be similar to other collections presumably. E.g.

> Application.CommandBars("Freddie").Controls.Add

tells me that there is a CommandBars collection. A couple of ways to go
about these things ...

- you can scan the collection and look at the names to see if you find the
one you want

- you can put "on error resume next" / "on error goto 0" statements around a
statement that Sets a variable equal to the particular command bar. If the
variable ends up with nothing in it, then it doesn't exist. E.g. (just had
a play):

Option Explicit

Private Sub CommandButton1_Click()
Dim FreddieBar As CommandBar: Set FreddieBar = Nothing
On Error Resume Next
Set FreddieBar = Application.CommandBars("Freddie")
On Error GoTo 0
If FreddieBar Is Nothing Then
Call MsgBox("doesn't exist")
Else
Call MsgBox("does exist")
Application.CommandBars("Freddie").Delete
End If

Application.CommandBars.Add(Name:="Freddie").Visible = True

Set FreddieBar = Nothing
On Error Resume Next
Set FreddieBar = Application.CommandBars("Freddie")
On Error GoTo 0
If FreddieBar Is Nothing Then
Call MsgBox("doesn't exist")
Else
Call MsgBox("does exist")
Application.CommandBars("Freddie").Delete
End If
End Sub

 
Didn't find what you were looking for? Find more on Custom Toolbars Usable by any User Or get search suggestion and latest updates.




Tagged: