Create a Button in Excel to Follow a Hyperlink
Using a macro button to follow a hyperlink is a great way to speed up processes and have external information at your fingertips.
A button that links to a web page can be a very useful tool when you are working in Excel. If you periodically go to the same web page while working in a workbook, it may make sense just to create a button to get you to that website much like you would use your bookmarks on a browser.
Here I will walk through several steps to add a hyperlink to a button in Excel.
Locate the Developer Tab
First, click on the developer tab. If you don't have the developer tab in your Excel ribbon, you can figure out how to add it here.
The developer tab opens the door for Excel users to a wide variety of programming options in the Visual Basic format. One option that is needed here is the ability to add a command button that can execute a program that opens a link.
Inserting a Button
After the developer tab is visible, click on the insert option, then click the "command button." This is in the upper left-hand corner of the active x control section. After the button is selected, drag the cursor with the left mouse button to trace a square or rectangle. This operation will dictate how large your button will appear.
Command Button Selection
There are other options for attaching macros to buttons. For instance, shapes and icons can be used as buttons to active Visual Basic programs.
Find Button Properties
Right-click on the button and select properties.
Properties
Select the command button properties to make changes to the button. There are a variety of changes that can be made to a button including changing the text that appears,
Edit Caption
Type what you want to display on the button in the caption section. Then, close out the properties window.
The caption property controls what is displayed on the face of the button. Several other button attributes can be changed in the properties area.
Add Code to the Button
Double-click on the button so that the visual basic window appears and type the following information in that window with no spaces between lines:
Private Sub CommandButton1_Click() ActiveWorkbook.FollowHyperlink _ Address:="https://www.roghnu.com End Sub
Note that “CommandButton1” is the name of the object and may be different for you depending on what you name your button. Additionally, you will want to choose the website that you would like to open in the place of http://www.roghnu.com. Click on the save button and save your workbook as a macro-enabled workbook. Exit the visual basic window.
Visual Basis Application
The same code above can be used to link your button to a website. Simply change the URL in the code.
Get out of Design Mode
Exit the design mode by selecting the design button in the controls section and deselect the button by clicking in any cell. Each time your button is selected your web browser will automatically open to display the web address that was saved in the Visual Basic code.
Before you test the button, you will need to leave design mode.
Save the Workbook as Macro-Enabled
Lastly, after testing the macro, understand that Excel workbooks that contain macros must be saved as macro-enabled workbooks under the file extension .xlsm. If the workbook is not saved under this file extension, the macro that you created will not function at all. To save, select the File tab then Save As. Select the location to save it and create a file name. Next, find the Excel Macro-Enabled Workbook selection from the “Save as type” dropdown followed by clicking the save button.
The above illustration shows an Excel file saved as a macro-enabled workbook so that macros can run when the workbook is reopened.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2019 Joshua Crowder
Comments
RJ on August 22, 2020:
i follow the Instruction, but nothing happens