Solved: Hyperlink on menu button

[NewDaddy]

Hi All

I was wondering if it was possible to add a hyperlink, using vba, to a toolbar button/button in drop down menu.

I would think it is possible, but I haven't found any examples on the intranet or any help in the help files and cannot work it out for myself.

I have created a custom menu (using vba) and within the sub menus would like to add some file links.
These would be created by looping though a spreadsheet which provides the path and filename.

Can anyone help/give any suggestions?

Thanks
J

[lucas]

so you need your menu item to run a macro that creates a hyperlink?
for how to use hyperlinks in code:
http://vbaexpress.com/kb/getarticle.php?kb_id=905

[lucas]

I found that by searching the kb.....
you could also search this forum which will return results for hyperlink..

[rory]

You can't, as far as I know, add a hyperlink to a menu item, but you don't need to - just have the menu item open the file you are interested in.

[lucas]

I have created a custom menu (using vba) and within the sub menus would like to add some file links.
These would be created by looping though a spreadsheet which provides the path and filename.

This is the part I don't understand.

[rory]

I'm assuming that something like the Recently Used File list was wanted, but as hyperlinks rather than just menu items, which I don't think is possible. Easier to just add standard menu items, assign the file name as a tag or parameter, and then have a macro assigned to the OnAction property that opens the specified file.

[NewDaddy]

This is the part I don't understand.

Hi All

Sorry. Dropped off the face of the earth for a few days. Hell that was a good session!!

What I have done is;
I have 12 open file routines (OFile1, OFile2 OFile 3 etc) I use these 'names' as the lookup value to get the file name & path. The lookup range is a sheet within the file listing each file and its respective file path.

I then use a sub menu routine with assigs each of these OFile routines to a toolbar for use.

I originally 'hard' coded the file name and path but as the file path has changed it was a pain to go through and amend the paths. This way all I need to do is go to one location to change as necessary.

What I thought would be a better way was instead of using OFile routines for each file I wanted to add to the toolbar/menu I could loop through the sheet listing the files and their paths adding an buttonitem to the menu/toolbar and assign a hyperlink.

I hope I have explained it better this time. Anyway it would be great to have it that way, and good to see how it would work.

Cheers
J

[Bob Phillips]

[vba]

Public Sub BuildList()
Dim i As Long
Dim iLastRow As Long
Dim oCB As CommandBarControl
Dim oCBCtl As CommandBarControl

With Worksheets("Filenames")

Set oCB = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, temporary:=True)
oCB.Caption = "Filenames"

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1
Set oCBCtl = oCB.Controls.Add(Type:=msoControlButton)
oCBCtl.Caption = .Cells(i, "A").Value
oCBCtl.Parameter = .Cells(i, "A").Value
oCBCtl.OnAction = "OpenFile"
Next i

End With

End Sub

Public Sub Openfile()
With Application.CommandBars.ActionControl
Workbooks.Open Filename:=.Parameter
End With
End Sub
[/vba]

[rory]

As I said, you can't add a hyperlink to a menu item, but you don't need to. You just add the file name as the Tag property (or Caption) of the menu item. Your routine can then look at the Tag/Caption of the CommandBars.ActionControl to get the file name, look it up and open the relevant file. If you look up ActionControl in help, there's a pretty good example.

[NewDaddy]

Thank you XLD,

Rory

Sorry, I didn't understand your 1st post. This is a big part of my problem. A lot of things I see in help files and on the internet I read but cannot absorb or understand what is being explained to me.

I am very much a kinesthetic learner! Again thanks for your help and I will now go away and try xld's solution and look up ActionControl.

J

[TonyJollans]

Set HyperButton = CommandBars("Worksheet Menu Bar").Controls.Add(ID:=1576)
HyperButton.HyperlinkType = msoCommandBarButtonHyperlinkOpen
HyperButton.TooltipText = "http://vbaexpress.com/forum/showthread.php?t=14819"

[rory]

I stand pretty much corrected!
Thanks, Tony - that's good to know.

[TonyJollans]

My pleasure! Us Wordies know a trick or two And apologies for the rather abrupt post - it wasn't really meant to come out like that; I knew Word could do it but had to check Excel and I was in a bit of a rush.

[rory]

No worries - I do it myself often enough! Always nice to learn something new.

[NewDaddy]

Again, thanks to xld.
I have put his suggestion to work and meets my needs perfectly.

As ever a excellent, fast and professional response to my query.

Thanks all
J