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

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("my Button").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)

"P. Dileepan" wrote in message
...
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

Sorry, that should have been

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.CommandBars("Formatting").Controls("my Button").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)

"Bob Phillips" wrote in message
...
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("my Button").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)

"P. Dileepan" wrote in message
...
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

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


-----Original Message-----
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

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. Dileepan" wrote:

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

-----Original Message-----
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

--

Dave Peterson

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

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





Bob Phillips wrote:

Sorry, that should have been

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.CommandBars("Formatting").Controls("my Button").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)

"Bob Phillips" wrote in message
...
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("my Button").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)

"P. Dileepan" wrote in message
...
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



--

Dave Peterson

I thought I had fixed that one, oh well. Thanks Dave

Bob

"Dave Peterson" wrote in message
...
And watch the spelling of myButton/my button (without a space vs. with a
space).





Bob Phillips wrote:

Sorry, that should have been

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.CommandBars("Formatting").Controls("my Button").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)

"Bob Phillips" wrote in message
...
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("my Button").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)

"P. Dileepan" wrote in message
...
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



--

Dave Peterson