Menu

P

P. Dileepan

Hi,

I have a macro with user form. I want this macro to be
available as a add-in. When the user opens this xla file
I want the macro to be available for execution as a button
in the command menu. How can I do this.

Thank you.

-- Dileepan
 
B

Bob Phillips

Hi Dileepan,

here is some code that goes in ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.CommandBars("Formatting").Controls("myButton").Delete
On Error GoTo 0

End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar

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

Set oCB = Application.CommandBars("Formatting")

With oCB
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.BeginGroup = True
.Caption = "my Button"
.FaceId = 23
.Style = msoButtonIconAndCaption
.OnAction = "myMacro"
End With
End With


End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Sorry, that should have been

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.CommandBars("Formatting").Controls("myButton").Delete
On Error GoTo 0

End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar

On Error Resume Next
Application.CommandBars("Formatting").Controls("my Button").Delete
On Error GoTo 0

Set oCB = Application.CommandBars("Formatting")

With oCB
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.BeginGroup = True
.Caption = "my Button"
.FaceId = 23
.Style = msoButtonIconAndCaption
.OnAction = "myMacro"
End With
End With


End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
P

P. Dileepan

Thanks for the code. But I am miussing something.
Please bear with me. I am a novice with VBA.

I copies the code as a Sub in a module. In place
of "MyMacro" for .action, I put the user form name. When
I open the workbook I do not see any command button. I
am missing something I am sure. Please help!

Thank you,

-- Dileepan
 
D

Dave Peterson

And watch the spelling of myButton/my button (without a space vs. with a space).
 
D

Dave Peterson

Keep it as myMacro.

And have the myMacro code show that user form:

Option Explicit
sub myMacro()
userform1.show
end sub

And make sure you put Bob's code under the ThisWorkbook module--not in a General
module.
 
P

P. Dileepan

Thank you for your patience with me. It works fine now.
The help available in this forum is excellent. I
appreaciate all the folks who helped me.

with best regards,

-- Dileepan
 
Top