How to create a list with my Excel hyperlinks using Macros?

Microsoft Excel se ha convertido en la herramienta más usada a nivel mundial en el ámbito laboral, ya que facilita la elaboración de diferentes cálculos, tablas o gráficas las cuales son una excelente ayuda, sobre todo en el área contable de la empresa. Gracias a Microsoft Excel se puede llevar de una manera más fácil y rápida cualquier tipo de cálculos, esto permite que se ahorre tiempo y dinero.

Next, this article will help us clarify some doubts regarding the functions that Excel spreadsheets have . In addition, it will show us some practical suggestions to make it easier for us to carry them out. Specifically we will talk about hyperlinks and how to use them through macros.

Index(  )

  1. What is the macro I should use to create this list of hyperlinks?
  2. How to perform hyperlink macro application in Excel?
    1. Steps to open and run it
    2. How to close it correctly?
  3. What should I do to list all my Excel hyperlinks quickly?

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 the hyperlinks, which we have made 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.

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 making the macro will appear . 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?

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. The macro codeshould be copied into this blank box.
  6. Luego damos clic al botón de play que aparece en la parte superior de la barra de herramientas en la 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.


Leave a Comment