Skip to content

How to Create a List with my Excel Hyperlinks Using Macros?

18 de February de 2022

Microsoft Excel has become the most used tool worldwide in the workplace, since it facilitates the preparation of different calculations, tables or graphs, which are an excellent help, especially in the company’s accounting area. Thanks to Microsoft Excel, any type of calculation can be carried out in an easier and faster way, this allows you to save time and money.

Next, this article will help us clarify some doubts regarding the spreadsheet functions of Excel. In addition, it will show us some practical suggestions to facilitate the realization of the same. Specifically we will talk about hyperlinks and how to use them through macros.

What is the macro I should use to create this list of hyperlinks?

Mainly we must know how to create a macro. In turn, you can investigate the different codes that exist based on what you want. Since through these codes it is given the information required for it to work correctly.

For example, it is necessary to define what is going to be done, either a list with all hyperlinks, which we have done in a worksheet, or on the contrary, we want a list of the hyperlinks that are in a workbook. This information is vital to know, since two different codes are needed for this. Therefore, it is essential to know about the elaboration of macros and the different functions that they perform.

How to perform hyperlink macro application in Excel?

To be able to perform a macro it is essential that we have the Programmer option enabled. Since it is the one that opens all the necessary options to use the Visual Basic Application (VBA). It is important to note that this window is not shown by default, so it is essential to search for it to enable it. To do so we must follow the following steps:

  1. Find the File tab.
  2. Go to Options
  3. Select: Customize Ribbon.
  4. Go to Main Tabs
  5. We proceed to click on the programmer box.

Returning to the Excel sheet, the Developer tab will appear in the menu bar. Note that this tab will not be removed unless Microsoft Office is disabled or reinstalled. Then, we can perform the following procedure described below:

Steps to open and run it

  1. Press the keys ALT + F11, to open the VBA id.
  2. Go to the menu and click on Insert where a series of options will be displayed, select and Insert module.
  3. Once the module is open, proceed to paste the macro code and click minimize.
  4. Go to the user interface and select the macros option in the Developer tab.
  5. Then a dialog window will open where a list will appear. In it we proceed to select the one that bears the name of the procedure that we previously pasted in the VBA module. Once selected, click on the execute option.

macro dialog box

Next, a new sheet will open automatically, describing all the information about the list of existing hyperlinks within the Excel workbook. Within this new sheet, all the details that were indicated at the time of performing the macro. Once this procedure is done, this new sheet will be updated every time a new hyperlink is made.

How to close it correctly?

It is important to keep in mind that when you close the sheet or the book, the way of saving it changes. This happens because having a macro included, it must be saved as a macro-enabled workbook. That is, a file *. xlsm. Since otherwise we can lose the macro with all the information that has been included in the book.

In order to save the document we must click on the file tab and select the save as option. When doing so, a window will appear indicating that we cannot save this file normally. Then the option to change the file extension is given, which we will proceed to change and save.

This is what will allow the VBA code to be saved in the workbook, in which the macro was previously executed, and in this way we will access it without problems.

What should I do to list all my Excel hyperlinks quickly?

location of the visual basic application

First of all, it is necessary to have knowledge about VBA, which will allow us to execute the different codes necessary to be able to carry out some type of command. If we want to make a list of the hyperlinks in an Excel sheet, we would proceed to carry out the following procedure:

  1. We proceed to create all the desired hyperlinks, for this it is essential to know how to create a hyperlink in Excel.
  2. It is essential that in our toolbar we have the Programmer option enabled. Because through this the VBA options are obtained, in addition to the option to record the macros.
  3. Then we proceed to go to the VBA window where we must click on the insert tab.
  4. The module option is selected, and a dialog box will open.
  5. Must be copy the macro code in this blank box.
  6. Then we click the play button that appears at the top of the toolbar in the VBA.
  7. We minimize and confirm that the procedure has been carried out correctly.

Now, if we want to have a list of an Excel workbook, we proceed to carry out these steps, changing only the code of the module. In other words, the macro would be different, or else then the macro can be executed on each sheet, it is valid but a bit tedious when it comes to saving time in doing it.