Solved: Adding a new item in menu bar

[arrun]

Hi have written some udf-s and bundled them in a Add-in. Now I want, once user install that add-in from Tool-menu, a new item should be added in Menu-bar and user will use my udf-s from that newly added menu item only. Can you pls tell me how to do that?

Rgds,

[Bob Phillips]

Here is an example of a building a commandbar on the fly
when you open a workbook.

[vba]
Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

On Error Resume Next
Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMenu").Delete
On Error GoTo 0

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls.Add( _
Type:=msoControlPopup, _
temporary:=True)
oCtl.Caption = "myMenu"
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
End With
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
End With
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

On Error Resume Next
Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMenu").Delete
End Sub
[/vba]

To add this, go to the VB IDE (ALT-F11 from Excel), and in
the explorer pane, select your workbook. Then select the
ThisWorkbook object (it's in Microsoft Excel Objects which
might need expanding). Double-click the ThisWorkbook and
a code window will open up. Copy this code into there,
changing the caption and action to suit.

This is part of the workbook, and will only exist with the
workbook, but will be available to anyone who opens the
workbook.

[arrun]

Thank you so much for this reply. But perhaps it is not what I am looking for. Here you used "Private Sub Workbook_Open()" which means, I have to feed that program in user's system and then, all time user (not me) opens his workbook, the things, whatever written under that program, will happen.

However what I want is, as soon as user installs my add-in in his excel, automatically an item (which address my add-in) will be in menu-bar [in his system] and it will be there as long as my add-in is kept installed in his system.

Hope I could explain what I want.

Any further suggestion?

Regards,

[Bob Phillips]

You add that code to your addin workbook.

[GTO]

@XLD:

Have you seen problems with Workbook_AddinInstall / Workbook_AddinUninstall events?

(I realize that the temporary(s) would be changed to False).

Thank you so much,

Mark

[arrun]

Still not working . Here as you suggested, I wrote followings and saved as XLA file with name "addin_A"

[VBA]
Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

On Error Resume Next
Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMenu").Delete
On Error GoTo 0

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls.Add( _
Type:=msoControlPopup, _
temporary:=True)
oCtl.Caption = "myMenu"
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
End With
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
End With
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

On Error Resume Next
Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMenu").Delete
End Sub

' This is my function
Function summ(a As Double)
summ = a
End Function
[/VBA]

Here the function "summ" is my udf.

What should I do?

[Bob Phillips]

I hope that you put the workbook code in ThisWorkbook, and summ in a standard code module.

[arrun]

Yes now it is working fine.

[austenr]

Bob, is it possible to modify your code to show a word document when you click the menu item? How would you code the .OnAction to accomodate this?

[Bob Phillips]

Show a Word document in what way Austen? Do you mean open it in an instance of Word? How would it know which doc?

[austenr]

Well I wanted to do something like show instructions on how to work this WB I've been working on for a month now. So If you have a better idea, I'd be glad to hear it.

[Bob Phillips]

What's wrong with a help file?

[austenr]

Nothing just dont know how to code it

[Bob Phillips]

Well, I have three help file applications, so if you send me the details in Word, I will knock you up a help file.

[austenr]

Thanks, Ill post them in a day or two havent written them totally yet. Thanks Bob

[austenr]

Bob,

Here is the verbiage I need in the help menu files. Feel free to format it any way you want. Thanks again.

Can I change the verbiage after you build it if need be?

[Bob Phillips]

Here you are.

I have the source files, so you add/remove from it at will.

[austenr]

Thanks much. How do I add it to the help menu?

[Bob Phillips]

The simplest way is to add a simple button that opens that file. The code would be

[vba]

Option Explicit
Option Private Module

Private Const mmModuule As String = "mhHTMLHelp"

Declare Function HtmlHelp Lib "hhctrl.ocx" Alias "HtmlHelpA" ( _
ByVal hWnd As Long, _
ByVal lpHelpFile As String, _
ByVal wCommand As Long, _
ByVal dwData As Long) As Long

Const HH_DISPLAY_TOPIC = &H0
Const HH_SET_WIN_TYPE = &H4
Const HH_GET_WIN_TYPE = &H5
Const HH_GET_WIN_HANDLE = &H6
Const HH_DISPLAY_TEXT_POPUP = &HE ' Display string resource ID or text in a pop-up window.
Const HH_HELP_CONTEXT = &HF ' Display mapped numeric value in dwData.
Const HH_TP_HELP_CONTEXTMENU = &H10 ' Text pop-up help, similar to WinHelp's HELP_CONTEXTMENU.
Const HH_TP_HELP_WM_HELP = &H11 ' text pop-up help, similar to WinHelp's HELP_WM_HELP.

'---------------------------------------------------------------------------
Public Sub ColgateOpenHelp(ByVal ContextId As Long)
'---------------------------------------------------------------------------
' Function: Opens the HTML help file
'---------------------------------------------------------------------------
Dim hwndHelp As Long
'The return value is the window handle of the created help window.
Dim hwndHH
hwndHH = HtmlHelp(0, ThisWorkbook.Path & "\Colgate.chm", HH_HELP_CONTEXT, ContextId)
End Sub

'---------------------------------------------------------------------------
Public Sub StartHelp()
'---------------------------------------------------------------------------
ColgateOpenHelp 1000
End Sub
[/vba]

[austenr]

i opened your file the first time and it opened fine. however the second time i tried to open it from the forum my computer asked me if I wanted to save it as a .php file. Is that right? Also where does the file need to be placed? Sorry never done this before.