Open a favourite Excel workbook via a button

[bongyc]

Dear all,

I have created a button on the standard toolbar to open a favourite Excel workbook but I have the following two questions:

1) How to change the hyperlink to another workbook. For instance, by clicking the button it will auto open from current workbook A to workbook B.

2) How to remove the button from standard toolbar if necessary ?

Many thanks in advance.

Cheers,
YC

 

[bongyc]

Dear all,

Sorry if my steps are unclear. Below is how I create a button to open a favourite Excel workbook:

1) Right click on toolbar and choose Customize
2) Click the Commands tab and then Macros under Categories
3) Drag a Custom button to toolbar
4) Choose Edit Hyperlink, Open
5) Select the workbook file that the button to open

Could anyone help on the 2 questions that I raised earlier on ?

Thank you.

Regards,
YC

 

[shodan]

So assuming that your button is already there;

1)rightclick on the toolbar and click customize. leave this dialogbox open.
2) rightclick on your button and click on assign hyperlink/edit and than just replace your hyperlink by browsing to the file
3) you can now also delete the button again.

Is this what you are looking for?

 

[Joe Was]

Sub myAddMenuBut()
'Standard Module code, like: Module1

Set MainMenu = CommandBars.Add(Name:="myMacro", Position:=msoBarFloating, Temporary:=True)
MainMenu.Visible = True

Set ctl = CommandBars("myMacro").Controls.Add(msoControlButton)
ctl.Style = msoButtonIconAndCaption
ctl.Caption = "Run Macro" 'Button label.
ctl.OnAction = "myWBOpen" 'Name of macro to run.
ctl.FaceId = 15
'Icons: 69=TV,10=ControlBoxOnSheet,11=123Bullets,12=BoxTypeBullets,13=flipPageOrientation,
'14=JustificationWidth,
End Sub


Sub myDelMenuBut()
'Standard Module code, like: Module1

CommandBars("myMacro").Delete
End Sub


Sub myWBOpen()
'Standard Module code, like: Module1
'Open file in its own application window!
Dim myWB$

myWB = CurDir & "\ATest.xls"

Set XL = CreateObject("Excel.Sheet")

XL.Application.Workbooks.Open myWB
ActiveWindow.WindowState = xlMaximized

End Sub

 

[bongyc]

Dear Joe and Shodan,

Thank you very much. It works perfectly.

Have a nice weekend ahead!

Cheers,
YC