PDA

View Full Version : Solved: Adding a new item in menu bar



arrun
11-28-2008, 01:02 AM
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
11-28-2008, 02:57 AM
Here is an example of a building a commandbar on the fly
when you open a workbook.


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


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
11-28-2008, 03:29 AM
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
11-28-2008, 03:44 AM
You add that code to your addin workbook.

GTO
11-28-2008, 03:53 AM
@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
11-28-2008, 03:56 AM
Still not working :( . Here as you suggested, I wrote followings and saved as XLA file with name "addin_A"


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


Here the function "summ" is my udf.

What should I do?

Bob Phillips
11-28-2008, 04:33 AM
I hope that you put the workbook code in ThisWorkbook, and summ in a standard code module.

arrun
11-28-2008, 04:39 AM
Yes now it is working fine. :D

austenr
09-09-2010, 10:23 AM
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
09-09-2010, 11:07 AM
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
09-09-2010, 11:22 AM
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
09-09-2010, 11:44 AM
What's wrong with a help file?

austenr
09-09-2010, 11:46 AM
Nothing just dont know how to code it

Bob Phillips
09-09-2010, 11:48 AM
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
09-09-2010, 12:18 PM
Thanks, Ill post them in a day or two havent written them totally yet. Thanks Bob

austenr
09-10-2010, 06:17 AM
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
09-10-2010, 07:10 AM
Here you are.

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

austenr
09-10-2010, 07:14 AM
Thanks much. How do I add it to the help menu?

Bob Phillips
09-10-2010, 07:32 AM
The simplest way is to add a simple button that opens that file. The code would be



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

austenr
09-10-2010, 07:35 AM
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.

austenr
09-10-2010, 07:38 AM
also where does the file have to reside?

Bob Phillips
09-10-2010, 07:50 AM
In the same directory as the workbook that builds the buttons. You can move it, b ut adjust the code to suit.

austenr
09-10-2010, 08:00 AM
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

Bob Phillips
09-10-2010, 09:17 AM
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.

austenr
09-10-2010, 09:20 AM
So the user has to have the help file and the workbook. Say both of them on their desktop?

Bob Phillips
09-10-2010, 09:24 AM
Yes, both wherever they will be stored.

austenr
09-10-2010, 09:26 AM
ok got it thank again.

austenr
09-11-2010, 04:09 PM
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.

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

Bob Phillips
09-12-2010, 01:34 AM
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

frank_m
09-12-2010, 01:47 AM
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.

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

StartHelp

End Sub

Bob Phillips
09-13-2010, 07:34 AM
Here is an updated file

austenr
09-13-2010, 07:36 AM
Thanks.