Assign hyperlink to new commandbar control
 

I trust this has not been asked recently and I've mised it in my search

I am creating a new popup on the main Excel toolbar through an auto-open
macro.

Whenit comes to the "on action" for the command bar buttons on this popup i
want to be able to assign a hyperlink just as I would if I were using the
"customize" process, right-clicking on the object and selecting "assign
hyperlink". The hyperlink is required to take user to a file located on our
network.

I'm running up blind alleys trying to find out how to do this and would
appreciate any help

Regards

Michael Bond

Copy of sample code :
Sub auto_open()

Set fbMenuBar = CommandBars.ActiveMenuBar

Count = 0
For Each c In fbMenuBar.Controls
Count = Count + 1
Next

Set fbMenu = fbMenuBar.Controls.Add(Type:=msoControlPopup, Befo=Count-1,
Temporary:=True)
fbMenu.Caption = "Football"

Set ctrl1 = fbMenu.Controls.Add(Type:=msoControlButton, ID:=1)
ctrl1.Caption = "Fixtures"
ctrl1.TooltipText = "Fixtures"
ctrl1.Style = msoButtonCaption
ctrl1.OnAction = ?????

End Sub

Assign hyperlink to new commandbar control
 

Michael,

You need to assign a macro, which can open the file:

Sub GetFile()
Workbooks.Open FileName:="Drive:\Folder 1\Folder2\File Name.xls"
End Sub

HTH,
Bernie
MS Excel MVP


"mabond" wrote in message
...

I trust this has not been asked recently and I've mised it in my search

I am creating a new popup on the main Excel toolbar through an auto-open
macro.

Whenit comes to the "on action" for the command bar buttons on this popup i
want to be able to assign a hyperlink just as I would if I were using the
"customize" process, right-clicking on the object and selecting "assign
hyperlink". The hyperlink is required to take user to a file located on our
network.

I'm running up blind alleys trying to find out how to do this and would
appreciate any help

Regards

Michael Bond

Copy of sample code :
Sub auto_open()

Set fbMenuBar = CommandBars.ActiveMenuBar

Count = 0
For Each c In fbMenuBar.Controls
Count = Count + 1
Next

Set fbMenu = fbMenuBar.Controls.Add(Type:=msoControlPopup, Befo=Count-1,
Temporary:=True)
fbMenu.Caption = "Football"

Set ctrl1 = fbMenu.Controls.Add(Type:=msoControlButton, ID:=1)
ctrl1.Caption = "Fixtures"
ctrl1.TooltipText = "Fixtures"
ctrl1.Style = msoButtonCaption
ctrl1.OnAction = ?????

End Sub

Assign hyperlink to new commandbar control
 

Bernie

I was worried that might be the response.

The commandbar.controls which will be added through the "auto-open" will
point to different files depending on the date the user opens the file (and
there may be more than 1).

Macro(s) to open the file(s) selected will not exist at the time the
application is designed. Other processing (not applicable to the user) will,
from time to time, create the files and the user will only be able to see on
the menu those files that have a date later than "now".

In order to assign macros to the "On Action" event I will have to get the
app to write the code at run time which will be assigned to the control. I
wanted to use a hyperlink rather than a macro for two reasons 1, I haven't
arrived at the point in my learning of using code to write a code module at
run time 2. I thought it would be neater.

But if that is the only way forward I'll press on and expand my learning.

Thanks for the help

Regards

Michael Bond

"Bernie Deitrick" wrote:

Michael,

You need to assign a macro, which can open the file:

Sub GetFile()
Workbooks.Open FileName:="Drive:\Folder 1\Folder2\File Name.xls"
End Sub

HTH,
Bernie
MS Excel MVP


"mabond" wrote in message
...

I trust this has not been asked recently and I've mised it in my search

I am creating a new popup on the main Excel toolbar through an auto-open
macro.

Whenit comes to the "on action" for the command bar buttons on this popup i
want to be able to assign a hyperlink just as I would if I were using the
"customize" process, right-clicking on the object and selecting "assign
hyperlink". The hyperlink is required to take user to a file located on our
network.

I'm running up blind alleys trying to find out how to do this and would
appreciate any help

Regards

Michael Bond

Copy of sample code :
Sub auto_open()

Set fbMenuBar = CommandBars.ActiveMenuBar

Count = 0
For Each c In fbMenuBar.Controls
Count = Count + 1
Next

Set fbMenu = fbMenuBar.Controls.Add(Type:=msoControlPopup, Befo=Count-1,
Temporary:=True)
fbMenu.Caption = "Football"

Set ctrl1 = fbMenu.Controls.Add(Type:=msoControlButton, ID:=1)
ctrl1.Caption = "Fixtures"
ctrl1.TooltipText = "Fixtures"
ctrl1.Style = msoButtonCaption
ctrl1.OnAction = ?????

End Sub

Assign hyperlink to new commandbar control
 

Bernie

Forget my earlier reply .... wasn't thinking straight. I'll write a sub
routine and pass the caption of the control to the sub-routine as the file to
be opened .... couldn't be simpler

Thanks for pointing me in the right direction

Michael


"Bernie Deitrick" wrote:

Michael,

You need to assign a macro, which can open the file:

Sub GetFile()
Workbooks.Open FileName:="Drive:\Folder 1\Folder2\File Name.xls"
End Sub

HTH,
Bernie
MS Excel MVP


"mabond" wrote in message
...

I trust this has not been asked recently and I've mised it in my search

I am creating a new popup on the main Excel toolbar through an auto-open
macro.

Whenit comes to the "on action" for the command bar buttons on this popup i
want to be able to assign a hyperlink just as I would if I were using the
"customize" process, right-clicking on the object and selecting "assign
hyperlink". The hyperlink is required to take user to a file located on our
network.

I'm running up blind alleys trying to find out how to do this and would
appreciate any help

Regards

Michael Bond

Copy of sample code :
Sub auto_open()

Set fbMenuBar = CommandBars.ActiveMenuBar

Count = 0
For Each c In fbMenuBar.Controls
Count = Count + 1
Next

Set fbMenu = fbMenuBar.Controls.Add(Type:=msoControlPopup, Befo=Count-1,
Temporary:=True)
fbMenu.Caption = "Football"

Set ctrl1 = fbMenu.Controls.Add(Type:=msoControlButton, ID:=1)
ctrl1.Caption = "Fixtures"
ctrl1.TooltipText = "Fixtures"
ctrl1.Style = msoButtonCaption
ctrl1.OnAction = ?????

End Sub