Linking custom toolbar to personal.xls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi! I've created a custom toolbar (using Dave Peterson's code) and saved it
as an add-in. I set up several macros that were assigned to the toolbar.
Everything was working just fine until I took the macros from a "regular"
file and put them into my personal.xls. Now the buttons only give me the cute
message. I think I need to modify this code...

With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 71 + iCtr
.TooltipText = TipText(iCtr)
End With

....changing ThisWorkbook.Name to reference personal.xls, but I'm apparently
not doing it right. Please help...
 
What did you put in the MacNames array?

Did you use the names of the macros that were in the personal.xls workbook?

What cute messages do you see?
 
Here is the relevant code...

MacNames = Array("pursuits", _
"newwins", _
"SAS_Open", _
"Other_Open")
CapNamess = Array("Pursuits", _
"NewWins", _
"SAS_Open", _
"Other_Open")
TipText = Array("Pursuits tip", _
"NewWins tip", _
"SAS_Open tip", _
"Other_Open tip")

The cute little message just says what I told it to say. (Pursuits or
NewWins).

~~~~~~~~~~~~~~~~~~~~~~~~~
 
I probably should have given you all of the code...

Option Explicit

Public Const ToolBarName As String = "MyToolbarName"
'===========================================
Sub Auto_Open()
Call CreateMenubar
End Sub

'===========================================
Sub Auto_Close()
Call RemoveMenubar
End Sub

'===========================================
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub

'===========================================
Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNamess As Variant
Dim TipText As Variant

Call RemoveMenubar

MacNames = Array("pursuits", _
"newwins", _
"SAS_Open", _
"Other_Open")
CapNamess = Array("pursuits", _
"newwins", _
"SAS_Open", _
"Other_Open")
TipText = Array("pursuits tip", _
"newwins tip", _
"SAS_Open tip", _
"Other_Open tip")
With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For iCtr = LBound(MacNames) To UBound(MacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 71 + iCtr
.TooltipText = TipText(iCtr)
End With
Next iCtr
End With
End Sub

'===========================================
Sub pursuits()
MsgBox "Pursuits"
End Sub

'===========================================
Sub newwins()
MsgBox "NewWins"
End Sub
'===========================================
Sub SAS_Open()
MsgBox "SAS_Open"
End Sub

'===========================================
Sub Other_Open()
MsgBox "Other_Open"
End Sub

~~~~~~~~~~~~~~~~~~~~
 
It's time to replace:

Sub pursuits()
MsgBox "Pursuits"
End Sub

With the code that does real work.

===
Is there a chance you have these "cute" macros in the same module as the code
that you pasted? And you have the "real" macros in a different module?

If yes, then delete those "cute" macros.
 
Dave,

Thank you very much (for the original code and for the assistance)! You'd
think that after getting as far as I have I would have seen something that
obvious. Oh well..."Now serving number 83."
 
Back
Top