Linking custom toolbar to personal.xls

[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...

 

[Dave Peterson]

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?

 

[Guest]

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).

~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Guest]

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

~~~~~~~~~~~~~~~~~~~~

 

[Dave Peterson]

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.

 

[Guest]

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."