Solved: Adding a new item in menu bar

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,

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.

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,

You add that code to your addin workbook.

@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

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?

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

Yes now it is working fine. :D

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?

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

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.

What's wrong with a help file?

Nothing just dont know how to code it

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

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

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?

Here you are.

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

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

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]

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.

also where does the file have to reside?

In the same directory as the workbook that builds the buttons. You can move it, b ut adjust the code to suit.

Ok still confused. Im uploading my WB for you to look at. I added the .chm file into the VBA project properties in the VBE editor. Not sure how to proceed from there. Could you tidy it up for me?

Also is the file attached to the WB if I do that. So if I send this to someone else they will see the help files? Thanks

You need to save the help file in the same directory as the workbook.

Add that code I gave you.

Link the help button to that code.

So the user has to have the help file and the workbook. Say both of them on their desktop?

Yes, both wherever they will be stored.

ok got it thank again.

Hmmm... the code you gave me for the button Bob, won't compile. I get a "only comments are allowed after End Sub, End Function or End Property" on the lines in red.

[VBA]Option Explicit

Sub Button138_Click()
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]

[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 Button138_Click()
'---------------------------------------------------------------------------
ColgateOpenHelp 1000

End Sub
[/vba]

Edit: Woops, sorry xld, I didn't see that you had posted.
Edit#2 Please correct me if I'm wrong with what I described below, as it did work that way when I tested it.
Edit#3 xld.. After looking again I do see that the StartHelp Sub that I used below is not needed, in favor of the code you gave.


I believe you need to put this part of the code in it's own module.

[vba] Option Private Module
Option Explicit

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] And only this for your Button
[vba]Sub Button138_Click()

StartHelp

End Sub[/vba]

Here is an updated file

Thanks.