Posted   by 

Enable Auto Hyperlink Excel

Have you ever had an Excel spreadsheet full of web addresses that were not clickable? Pressing F2 and ENTER is good enough when you only have a few, but when you have dozens, hundreds or even thousands, this can become a very time consuming task.

Fortunately there are a couple of approaches which will save you tons of time.

In other files I usually just copy the table in, but in this I need the link to be live rather than a regularly updated copy in. On opening, it is asking users whether to Update Links, and then whether to Enable Content, which is fine. Except that it confuses the sales team. I would appreciate a way of getting the file to open without asking.

Note that neither of these two methods do any validating to ensure the result is actually a valid hyperlink.

Method #1: Use an Excel formula to create hyperlinks

  1. You would need to correct the hyperlink address to the updated file name so that the file becomes detectable. Follow the steps below to edit the address. Step 1: Copy the address of the new file from the file's explorer. Step 2: Head to Excel. Step 3: Right-click on the hyperlink, and click 'Edit hyperlink' in the menu.
  2. Mar 30, 2014 Hi - I have a large worksheet with a number of hyperlinks for quick navigation around it, but they don't work when I open the sheet within Excel for iPad. It seems to understand that the cell does indeed hold a Hyperlink, as the word 'Hyperlink' appears in the cell context menu, but under this menu the 'Open' option is greyed out so they don't.
  3. Step 1: Open MS Excel and Click on the File tab. Step 2: Now click on the Options. Step 3: After clicking on Options, one pop-up will be open with the header Excel Options, click on the Save. Step 4: Now AutoSave setting will be open where a user can configure AutoSave settings, tick mark on the AutoSave checkbox, configure the time at which.

This first method uses a formula to create a hyperlink enable version of the cell in an adjacent cell:

=Hyperlink(a1, a1)

The first parameter is the cell address that contains the URL and the second refers to the text you want to have appear in the cell. Note that if your URL does not start with 'http://', you will need to add it as follows:

=Hyperlink('http://' & a1, a1)

Of course you could create a more complex formula that could automatically detect whether http:// is required but those above are easier to remember.

Method #2: Using VBA to convert text into hyperlinks (advanced)

The second way is to use VBA and then run the macro. When executed, it will go through the selected range of cells and turn them all into hyperlinks. Note that this method only works if the workbook is in single user mode, not multi-user shared mode.

Enable Auto Hyperlink Excel

Creating the Macro

  • Open your Excel workbook
  • Open the macro editor by pressing ALT+F11.
  • In the View and select Project Explorer.
  • Look for the folder called Microsoft Excel Objects. Right-click on it, select Insert and then Module.
  • Paste the above code into the project module you have selected.
  • Press ALT+F11 to return to your Excel workbook or click on its button in the Windows taskbar.

A good place to put such macros is actually in your personal workbook so that it is available regardless of the workbook you are editing. You can Google that to find out how.

To run the Macro

  • To execute the macro, select the text links you want to convert to clickable hyperlinks.
  • Press ALT+F8 to open the Macro selector window and click on the macro you just created.
  • Your links should now be clickable.

BONUS: Using VBA to remove Hyperlinks

Another macro you might find useful helps you remove the selected hyperlinks, leaving just the text:

Related Posts:

-->

Original KB number: 310049

Symptoms

Assume that you set Internet Explorer as your default browser in Windows. When you select a hyperlink in Microsoft Outlook, you receive one of the following error messages, depending on your Outlook version:

This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator.

Your organization's policies are preventing us from completing this action for you. For more info, please contact your help desk.

Resolution

To resolve this problem, follow these methods in order. Check whether the problem is resolved after each method.

Method 1: Reset Internet Explorer settings

Internet Explorer 11 and Internet Explorer 10

Warning

If you are using Windows 10, Windows 8.1 or Windows 8, proceed to method 2.

  1. Start Internet Explorer.

  2. Select Tools > Internet Options.

  3. Select Advanced > Reset.

  4. In the Reset Internet Explorer Settings window, select Delete personal settings > Reset.

  5. Select Close when it is completed.

  6. Close and restart Internet Explorer.

  7. Select Tools > Internet Options.

  8. Select Programs > Set programs.

  9. Select Set your default programs.

  10. In the Programs list, select Microsoft Outlook > Set this program as default.

  11. Scroll back up to the top of the default programs list and highlight Internet Explorer and select Set this program as default.

  12. Select OK, and close the Default Programs dialog box.

Internet Explorer 9

  1. Start Internet Explorer.

  2. Select Tools > Internet Options.

  3. Select Advanced > Reset.

  4. In the Reset Internet Explorer Settings window, select the Delete personal settings > Reset.

  5. Select Close when it is completed.

  6. Restart Internet Explorer.

  7. Select Tools > Internet Options.

  8. Select Programs > Set programs.

  9. Select Set your default programs.

  10. In the Programs list, select Microsoft Outlook > Set this program as default.

  11. Select OK, and then close the Default Programs dialog box.

  12. In the Internet Options dialog box, select the Programs tab, and then select Make default under Default web browser.

  13. Select OK.

Internet Explorer 8 and Internet Explorer 7

  1. Start Internet Explorer.
  2. On the Tools menu, select Internet Options.
  3. Select Advanced > Reset.
  4. Select Programs > Set programs.
  5. Select Set Default Programs.
  6. Select Microsoft Outlook > Set this program as default.
  7. Select OK, and then close the Default Programs dialog box.
  8. In the Internet Options dialog box, select the Programs tab, and then select Make default under Default web browser.
  9. Select OK.

Internet Explorer 6

  1. Start Internet Explorer.

  2. On the Tools menu, select Internet Options.

  3. Select Programs > Reset Web Settings.

  4. Under Internet programs, make sure that the correct email program is selected.

  5. Select the Internet Explorer should check to see whether it is the default browser check box.

  6. Select Apply > OK.

    Note

    You may receive the following message when Internet Explorer starts:
    Internet Explorer is not currently your default browser. Would you like to make it your default browser?

    If you receive this message, select Yes.

Method 2 - Export and import the registry key from another computer

This section is intended for advanced computer users. For help with advanced troubleshooting, ask your system administrator or contact Microsoft Support.

Important

Follow the steps in this section carefully. Serious problems might occur if you modify the registry incorrectly. Before you modify it, back up the registry for restoration in case problems occur.

Note

Use this method only if the earlier instructions for each browser do not work to resolve this problem.

Step 1: Export the registry key from another computer

  1. On a computer that does not encounter this problem, select Start > Run.

  2. In the Open box, type regedit, and then select OK.

  3. Locate, and then select the registry subkey: HKEY_LOCAL_MACHINESoftwareClasseshtmlfileshellopencommand.

  4. On the File or Registry menu (depending on your operating system), select Export.

  5. Note the location where the file will be saved. The registry file can be saved directly to the hard disk or floppy disk.

  6. Type a unique file name, and then select Save.

  7. Exit Registry Editor.

Step 2: Import the registry key

  1. Copy the exported registry key to the desktop on the problem computer.

  2. Double-click the .reg file.

    You may receive the following message:

    Windows 10, Windows 8.1, and Windows 8

    Adding information can unintentionally change or delete values and cause components to stop working correctly. If you do not trust the source of this information in C:Users<yourlogon>Desktopregkey.reg, do not add it to the registry.

    Windows 7

    Are you sure you want to add the information in drive: location file was copied tofile name.reg to the registry?

  3. Select Yes > OK.

Step 3: Verify that the String (Default) value of the HKEY_CLASSES_ROOT .html key is htmlfile

  1. Select Start > Run.

  2. In the Open box, type regedit, and then select OK.

  3. Locate, and then select the registry subkey: HKEY_CLASSES_ROOT .html.

  4. Make sure that the value of the String (Default) is 'htmlfile'. If it's not htmlfile, right-click (Default), select Modify, input htmlfile in the Value data box, and then select OK.

  5. Exit Registry Editor.