# Excel - Hyperlinks in list box



## Timmy

Hi,

I want to make a list box, in Excel, where the elements in the box are hyperlinked. Is this possible? If it is, how do I do it? I don't know visual basic or macros or anything.

thanks


----------



## Glaswegian

Hi Timmy

Yes it's possible. Can you give me a better idea of what you are trying to do? There may well be other options as well.

Regards


----------



## Spatcher

Do you mean like a drop-down list?


----------



## Timmy

Hi,

I want to have a list of products, contained in one cell. The name of each product will be hyperlinked to the company's product web page. I figured a drop-down list would be the best way to do this, but i'm open to other suggestions.

Thanks.


----------



## Glaswegian

Hi Timmy

Firstly, I wouldn't put all the names in one cell. It's quite easy using a listbox as you can populate the list box from a range anywhere on the same or another sheet, or directly via code. You can make the list box the same height as one cell, but you'll probably need to make it wider than just one cell.

Will your list of products be static or will it change over time?


----------



## Timmy

Hi Glaswegian,

The list would be static.


----------



## Glaswegian

Hi Timmy

Try this – there is only one small piece of code required. 

You mentioned that you wanted the product name rather than the http:// address to appear in the listbox. I would create a list of the web addresses in an unused area of your sheet. Edit each hyperlink and change the ‘Text to display’ to the relevant product. For example, you may have an address like

http://www.joinerycompany.com

in a cell. Right click on the hyperlink and choose ‘Edit Hyperlink’ – in the dialogue type in something in the ‘Text to display’ box at the top, e.g. Dining Table. This will now be the visible text.

Open the *Control Toolbox* toolbar – right click on the space to the right of your current toolbars and from the drop down list choose Control Toolbox. Now click on the ‘ListBox’ icon and draw a listbox on your sheet. You can move it to where you want and also resize it as desired.

Now right click on the listbox and choose ‘Properties’. This will open a dialogue where you can change the way the box and the text appears. Scroll down the dialogue box and look for a property called ‘ListFillRange’. Click on the line opposite this and input the range of cells that contain your list – input like this

X24:X43

Now look for the property ‘Font’ – click on this line and a small button will appear to the right side. Click on this to bring up the font box. Change your font as required and also check the ‘Underline’ box. This will make the listbox entries look more like hyperlinks (although you can miss this if you want). Now look for the property ‘ForeColor’ – click on the line and a drop down arrow will appear. Click on this to change the font colour as required.

Close the properties box. Now right click on the listbox and choose ‘View Code’. The Visual Basic Editor (VBE) will open and you should see these lines:

Private Sub ListBox1_Click()

End Sub​Now copy the following and paste it between those two lines:

Dim navURL

navURL = ListBox1.Value
ActiveWorkbook.FollowHyperlink Address:=navURL, NewWindow:=True​This is the code that tells Excel to follow the hyperlink when you click on an item in the listbox.

Now press Alt+F11 to return to Excel.

On the Control Toolbox click the blue triangle icon to exit design mode.

That’s it – all done.:smile: Any problems with this please post back.

Regards


----------



## Timmy

Hi,

I followed your instructions and set up the list boxes, but the hyperlinks do not launch from them (but the links do work fine in the list I set up in the unused part of the page).

Thank you for your help.


----------



## Glaswegian

Hi

I assumed you were only going to create one listbox - sorry. In the code I posted, the listbox is called ListBox1. If you've made more that one listbox, you need to repeat the code for each listbox and change the listbox name. In design mode (the blue triangle icon on the Control Toolbox toolbar), click on the listbox and the name will appear in the Name box, just above cell A1. Change the name accordingly.

From what you've said I reckon that's what happened - if it still doesn't work attach a sample workbook and I'll have a look.

Regards


----------



## Timmy

Hi Iain,

I'm still having problems getting this to work. It's not allowing me to change the listbox name. I've attached a sample worksheet - if you can have a look i'd be very appreciative.

Thanks,
brent


----------



## Glaswegian

Hi Brent

I can’t open rar files so I’ve attached a sample workbook. I’ve made a slight change to the format of the listboxes. The hyperlinks were not always being opened and the code was crashing so I’ve changed the listboxes to 2 columns, with the actual hyperlink address being hidden. The range of cells to show in the listbox has also been changed. What it means is that the name in the first column will show in the listbox, and when you click on the name, it’s as if you have clicked directly on the hyperlink in column 2 and the hyperlink is then followed. I’ve tested this and it works fine.

Check the Properties of each listbox to see the changes. The basic code for the Click Event of each listbox is basically the same – just one small change.

Have a go at this and any problems just post back.

Regards


----------



## Timmy

It's working now. Thanks very much for all your help.

Brent


----------



## carmelo

I am using VLOOKUP. Can I hyperlink listbox for the lookup?


----------



## Glaswegian

Hi and welcome to TSF.

You'll need to provide much more information on what you want to do.


----------



## joelle

Dear Glaswegian,
I have a very similar question as Timmy -- pls would you send me the
sample spreadsheet that you mentioned on your post #11.
Many thanks.
joelle


----------



## Glaswegian

Hi joelle and welcome to TSF.

Couldn't find my original, but here's another one that does the same thing.


----------



## joelle

Hello Glaswegian,

First off, thank you for the welcome, the very fast help, and the sample spreadsheet - I appreciate very much.

Your links work very well, but when I tried replace them with mine and I got a error message, and the line below from the code window is highlighted.
------------------------------------------------
ActiveWorkbook.FollowHyperlink Address:="http://" & navURL, NewWindow:=True
------------------------------------------------
I wonder if this has to do with my links being from the company intranet?
Here how it looks and it points to a page on our intranet site:
http://122.141.66.7/QP/forms/prmainform.asp?R_ID=AA201&singleResult=yes

But when I click directly on the link from the 2-column table, it opens okay??

Many thanks.
joelle


----------



## Glaswegian

Hi joelle

Probably to do with the *http://* part. My original hyperlinks in the sample did not have that so I had to add it in the code. Excel can resolve the addresses direct from the sheet but not in the code. Simply change this

*Address:="http://" & navURL,*

to this

*Address:=navURL,*

and see if that works.


----------



## joelle

Hello again,

Wow, I'm impressed by your very fast postback (just like you were next door) and more impressed by your revised code -- works beautifully now.

Many thanks for your generosity.

Joelle


----------



## Glaswegian

My pleasure.

Although if you were next door, we wouldn't have to post here...:laugh:


----------



## joelle

... and you wouldnt want my kinda next door


----------



## livalicious

Hi Glaswegian!!!

I have tried the help you have given to Joelle and Timmy in regards to Hyperlinks in list box but for some reasons nothing were working  

I had the same error message as Joelle and tried your suggestion to remove the http:// code but still unsuccessfull. 

What I specifically need help with is creating hyperlinks in DROP DOWN list as I have a number of intranet links to be included and using the listbox is just not that user friendly as I am trying to fit everything in one screen. 

Would appreciate if you can help! thanks!

Olivia


----------



## Glaswegian

Hi livalicious and welcome to TSF.

If you can leave this with me, I'll try and get back to you when I can.


----------



## livalicious

thanks!!!! will be waiting patiently :1angel:


----------



## Glaswegian

Hi again

See if the attached works for you. It's the same example workbook as before, only this time I've added a Combo Box for the hyperlinks - you can adjust the size and position to suit.


----------



## livalicious

Hi Iain!!!

Thanks for your file, it worked puuuuuuurfectly great!!!! ray:

Really appreciate your time for that!!

regards,
Olivia


----------



## StagColumbus

Excellent post...just to jump in, here...this allowed me to modify something else I was working on. However, while taking out the "http://" worked for most of my links as mentioned, within my list of links are a couple different files that are not on a web/intranet address?!? For example:

I have some links to intranet addresses/documents/folders. They work great.

I have a link to a file on my desktop, it does not work and the debugger comes up with the VBA code highlighted. Stop that, I went in and changed the display text for the hyperlink to that of the actual link and it works fine...on to the last file type not linking...

I have a link in this list that opens another worksheet in this workbook. I tried the solution from above and got rid of the display text. The cell now says "Told!A1" (the sheet is named "TOLD"). while the hyperlink works fine in the cell on the main worksheet, the link does not work in the two-column drop down list I placed on a userform.

Any ideas?

The intent of grouping all of these links into a list and placing them on a userform was to clean up the workspace for this main worksheet that has become operaitons central.

Thanks,
Stag


----------



## Glaswegian

Hi Stag

Can I just confirm my understanding of your problem.

A hyperlink in a listbox does not work. The link should lead to another worksheet in the same workbook - is that correct?

Can you post a sample workbook (with any sensitive data edited) or sample code?


----------



## Jasta

I think I am having a similar problem to that of Stag's. I intend to have my list items associated with hyperlinks to other worksheets in the excel document. The macro fails out highlighting the line...
ActiveWorkbook.FollowHyperlink Address:=navURL, NewWindow:=True
I assume this has to do with how the address is defined. Any input would be greatly appreciated.


----------



## hkhushnood

Hi there ,

I have a similar problem which ill be greatfull if u can help me.
I have hyperlinked few of the sheets.but i need to create a list box and in that need to set hyperlink sheets in the same worksheet.please help


----------



## brouhaha

Hi,

I am trying to mess with your code but can't seem to get it to work. I want to do the same thing, except instead of linking to a website, I want the links to go to different sheets within the workbook. I am trying to edit the combo box vba code but I'm not too familiar with vba  any help would be great.

Thanks


----------



## brouhaha

bump, any help would be appreciated. trying to finish this for a project due soon


----------



## Glaswegian

Hi

Can you provide a bit more detail?

If you are just linking to another sheet in the same workbook, using code may be a bit over the top. Will there be values in the cells - if yes, then perhaps it might be easier just to create a hyperlink directly from there to the relevant worksheet?

Edit - have a look here

http://www.mrexcel.com/forum/showthread.php?t=191754&highlight=combobox+hyperlink
http://www.mrexcel.com/forum/showthread.php?t=332722&highlight=combobox+hyperlink


----------



## brouhaha

well there be values in the cells? do you mean the cells on the worksheets? because yes there will be. basically, i want to create a combo box that will allow users to navigate between the sheets. 

so for example, if i have sheets named w7, w8, w9. i would like the combo box to show those 3 options, and when a user clicks on them, they will be taken to that specific tab. there will be data/values in all 3 tabs.

if you could tell me the easiest way to do that, i would greatly appreciate it


----------



## Glaswegian

Hi

The first link I posted has an example of exactly what you want to do - this is it here

http://www.mrexcel.com/forum/showthread.php?t=191754&highlight=combobox+hyperlink

Any problems with that then post back (although at this time of year it might be a day or two before I get back to you).


----------



## brouhaha

Glaswegian said:


> Hi
> 
> The first link I posted has an example of exactly what you want to do - this is it here
> 
> http://www.mrexcel.com/forum/showthread.php?t=191754&highlight=combobox+hyperlink
> 
> Any problems with that then post back (although at this time of year it might be a day or two before I get back to you).


hi, thanks for the replies. sorry if i'm being dull.. but i don't understand what he means by this..



> important is you "clean up" the combobox (.Text = "") so you will be able to select another option


----------



## Glaswegian

All that line is doing is clearing the value of the Combobox in the code, so that the next selection is the one actually used. If you don't clear the value, then you will only ever jump to one sheet - the same one over and over again.


----------



## brouhaha

Hi,

I must be missing something. I have a combo box and have filled in the LastFillRange section with a range that has a list of the sheet names. And then I have pasted that code into the vba section. But how does it know to jump to the corresponding sheet when it is clicked on in the combo box? Right now I'm just getting errors.

Thanks


----------



## Glaswegian

Hi 

Can you attach a sample workbook with what you have so far?

You'll need to zip the file first - I'll then have a look for you.


----------



## brouhaha

Glaswegian said:


> Hi
> 
> Can you attach a sample workbook with what you have so far?
> 
> You'll need to zip the file first - I'll then have a look for you.


hey thanks man, really appreciate it. i've attached the zip. I know i'm doing something wrong because I haven't put in any code or anything linking the text to specific tabs.


----------



## Glaswegian

Hi

You had the correct code - but you had an additional Combobox Change Event line and an End Sub line wrapped around the code - so Excel was a bit confused. :grin:

Works fine now.


----------



## brouhaha

Awesome! Thanks for walking me through this. I'm sure it took patience on your part. Have a great holidays dude!


----------



## brouhaha

ok, I can't seem to edit that post..

one more bit of help if you can. is there anyway to make the combo box show whats in the first line instead of defaulting to blank?


----------



## Glaswegian

Hi

It's because of the combobox that the first line is blank - a combobox allows users the chance to type in an entry themselves (although this can be blocked). You would have to use a Listbox. I've attached a fresh workbook with a listbox added. Bear in mind that although an entry shows you still have to physically click on the listbox to change sheets. I guess it's up to you to decide which suits your needs best.


----------



## carol124

*Re: Excel - Hyperlinks in COMBO box*

I have a sheet that contains all the product names in a combo box....each time i select a product i want it to navigate to that product sheet where its description and pricing detail is stored


----------



## Glaswegian

Hi and welcome

There are plenty of samples in this thread - one of them will likely suit your needs.


----------



## gafeler

Hi I have looked through all of the samples on this thread and I have a pretty good understanding of whats going on. Everything seemed to be working when I had about four however some of them just stopped working as I added more combo boxes. I kept all of the names straight but the more I added the more problems I had. Can Some one please take a look and see what I am doing wrong?
Thanks


----------



## Glaswegian

Hi and welcome

I've had a quick look but don't see anything obvious. I can't connect (of course) to the links but I did test with a message box and the address appeared OK - I take it these are links to an internal company location? Have you tested the links yourself? No problems with the network perhaps?


----------



## Gregory A

Dear Glaswegian,
I have a very similar question as Timmy -- pls would you send me the
sample spreadsheet that you mentioned on your post #11.
i have tried using:
Private Sub External_Links_Change()
Dim navURL

navURL = External_Links.Value
ActiveWorkbook.FollowHyperlink Address:=navURL, NewWindow:=True
End Sub

But it wont work for any hyperlinks (my hyperlinks are both Internet links and a local intranet links) where i have changed the "Text to display" wording.


----------



## Glaswegian

Hi and welcome.

A quick look at your code suggests you are perhaps missing the 'http' prefix:


Code:


ActiveWorkbook.FollowHyperlink Address:="http://" & navURL, NewWindow:=True

Does that help?


----------



## Gregory A

No it has not worked, i managed to adapted the below code to work for the internet based hyperlinks but not for the local intranet based hyperlinks:
Private Sub External_Links_Change()
Select Case External_Links
Case "Microsoft"
ActiveWorkbook.FollowHyperlink Address:="http://" & Sheets("Sheet1").Range("O64").Text
Case "Google Map"
ActiveWorkbook.FollowHyperlink Address:="http://" & Sheets("Sheet1").Range("O65").Text
Case "ABR Link"
ActiveWorkbook.FollowHyperlink Address:="http://" & Sheets("Sheet1").Range("O66").Text
Case "Qantas"
ActiveWorkbook.FollowHyperlink Address:="http://" & Sheets("Sheet1").Range("O67").Text
Case "Rex"
ActiveWorkbook.FollowHyperlink Address:="http://" & Sheets("Sheet1").Range("O68").Text
Case Else
End Select
End Sub


----------



## Glaswegian

Based on the Corporate intranet where I work, some servers use specific names while others use IP addresses. Depending on which server I want to use, I would connect like this

*\\ourserver1\Folder\file.xls*

or
*
\\99.999.99.9\Folder\file.xls*

Perhaps you need to check how your internal servers are addressed.


----------



## Gregory A

Hi.
Thank you very much that worked perfectly, 
A little query, why does the link have to be so precise being the almost opposite to the internet links which can be just "http://".

A little question, in the line below:
Case "Microsoft"
ActiveWorkbook.FollowHyperlink Address:="http://" & Sheets("Sheet1").Range("O64").Text
Once i have replaced the "http://" with the full intranet address of the file i want to link to i have found that the cell address after Range being "O64" can point to any cell as long as it is a empty cell.


----------



## Glaswegian

Networking is not something I'm very familiar with, but your intranet is internal - not the web. So to locate a specific file on a specific server you need the precise address.

For your second question, you are simply linking directly to a file, using the full address, so the text in the cell is not really required.


----------



## kj_kiwi2002

still having fan error pop up.

Run-time error '1004'

Cannot open the specified file.


----------



## smbsieg

Hello - I'm trying to create a dropdown box, that will direct users to bookmarks within the same excel sheet. The list in the dropdown references a list elsewhere in the same excel sheet. The list is hyperlinked to bookmarks within the same excel sheet. I've been trying to gleen the correct vb from the posts, but need one that goes to a bookmark within the same sheet rather than to a hyperlink. 

So far, I have: 
Private Sub ListBox1_Click()

Dim navURL

navURL = ListBox1.Value
ActiveWorkbook.FollowHyperlink Address:=navURL, NewWindow:=True

End Sub

I think my problem lies with the references to Hyperlink and URL, but I don't know the correct code. Any help would be appreciated.


----------



## RSpecianjr

Hey smbsieg,

Just to clarify, you are trying to link options in a listbox to different places in a single workbook.

You shouldn't need to use hyperlinks to do this. With the use of named ranges its possible to have the name "Apple" refer to "Sheet1!A1". Once you have that, its a pretty straightforward line of VBA code:

private sub listbox1_click()
with listbox1
on error resume next
application.goto reference:=.text
on error goto 0
.value = ""
end with
end sub

Named ranges are pretty easy to setup. Select the range you would like to have the reference to. Then, in the upper left hand corner there is a "Name Box" which typically displays the cell address, ie A1 etc. With your range selected just type the reference name in there. 

So, if you want "B5" to be referenced by "Ultimate Frisbee" just select cell B5 then type "Ultimate Frisbee" into the name box and press enter. Voila! Easy as pie. 

Hope this helps,

Robert Specian Jr.


----------



## frenchy84

Glaswegian, you seem to be the man for this....

In as much detail as possible and this is probably a very simple one for you I, like pretty much everyone else who has posted want's to generate a list of hyperlinks. The difference I have is that I just want it to be linked to more excel sheets within the same workbook, I'm talking in the region of a couple of hundred sheets in one work book and rather than tabing through page by page I can have one list and at a click go to whatever page I desire.

The list can be generated on the 1st sheet and then the drop down list box can be used on every other page linking to the list on page one.

I'm pretty sure you can help but the question is would you mind?

Many thanks,


----------



## Glaswegian

Hi and welcome

Rather than me re-inventing the wheel, have a look at Tom Urtis's post here (scroll down)

http://www.mrexcel.com/forum/showthread.php?t=204584&highlight=combobox+sheet+menu

His solution will place a combobox on the menu bar and it will list all visible sheets in the workbook - no need for you to create a separate list yourself.


----------



## frenchy84

Your a good man.

Many thanks


----------



## frenchy84

Hey mate,

I have a slight problem, I followed as directed by Tom, problem I have is that every excel sheet I have open now it still includes the dropdown. Is this normal?

I have even gone to the extent of clearing all the script entirely and deleting the sheet (I have a back up) 

Any suggestions?

Cheers


----------



## Glaswegian

Hi

Yes, that is the way it's done. The control is part of the Menu Bar and therefore available from any sheet when the workbook is open.

Can I assume from your question that you did not really want that to happen?


----------



## frenchy84

Hiya,

yep, you've assumed correctly mate. Any solution?


----------



## Glaswegian

OK, but what exactly do you want? Is it just a box on the first sheet that then lists all the sheets in the workbook? If I can amend Tom's code then that would be the ideal way.


----------



## frenchy84

Morning Glaswegian, 

What I want to be able to do is either have a dropdown list on the task bar where you can go to any sheet within that workbook (not have the task bar option in every excel document you open) or a drop down list within a cell where you can select which sheet you want to go to and have this in each sheet.

I also need to know how to remove the current menu (created by Toms code) in the task bar which is now a permanent fixture in every book I open.

Many thanks once again


----------



## Glaswegian

Hi

I'll get back to soon - just a bit busy today.


----------



## Glaswegian

Hi

I think I was heading off at a tangent earlier...

You need the box to disappear when you close that one particular workbook. Therefore, this is the code that starts the removal


Code:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "ResetMenu"
ThisWorkbook.Save
End Sub

Make sure this code is present in ThisWorkbook Module. When the workbook is closed it calls the routine to delete the combobox - this one


Code:


Private Sub ResetMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Sheet selector").Delete
Err.Clear
End Sub

which should be in a normal Module.

Let me know if I've understood correctly.


----------



## ladymemi

Hi Glaswegian, I to am trying to do something similar to the two post, but I am having the same problme as Timmy when it comes to the hyperlinks working. I only have one listbox and what I am trying to do is create a drop down box or list box which ever will work and in that box I want hperlinks to other Excel worksheets in another folder that I have. I copied the VB code and when I click on the links it give me the "End -- Debug" so I saved it, closed it, got back in it & now it's telling me my macros are disabled and gives me the the reasons below. But I did what it suggested and my macro security is set to high not very high in the first place. HELP PLEASE!!!!!
ou may encounter this error for the following reasons:
*Macro security is set to:*
Very High and the application encounters a signed macro, but the macro was automatically disabled. Use the following procedure to enable the macro:
Select the Tools menu option and then select Macro and Security. In the resulting Security dialog, set the security level to High by clicking the High radio button.
Close the file and any other instances of the application currently running on the computer (close all applications that also use the application you are currently running).
Open the file again and examine the certificate of trust details and set the Always trust macros from this publisher box if you trust the certificate issued by the publisher.
Click the Enable button to allow the macro to run.
High and the application encounters a signed macro, but you selected Disable when prompted by the macro warning dialog when opening the file. Use the following procedure to enable the macro:
Close the file and any other instances of the application currently running on the computer (close all applications that also use the application you are currently running).
Open the file again and examine the certificate of trust details and set the Always trust macros from this publisher box if you trust the certificate issued by the publisher.
Click the Enable button to allow the macro to run.
High or Very High and the macro was not signed and was automatically disabled by the application. Use the following procedure to enable the macro, if you have verified you can trust the source of the unsigned macro:
Select the Tools menu option and then select Macro and Security. In the resulting Security dialog, set the security level to Medium by clicking the Medium radio button.
Close the file and any other instances of the application currently running on the computer.
Open the file again and click the Enable button when prompted to allow for the unsigned macro to run.
When you have accepted the macro, return the macro security level to its previous setting.
Another instance of your application has a lock on the security settings and is disallowing any changes to security settings.
It is recommended to close all Office related applications or other instances of the same application when you attempt to make changes to your security settings.
Visual Basic for Applications (VBA) is not installed on your computer.
If VBA is not installed, it is not possible to accept certificates of trust attached to VBA macros. Therefore, any VBA macro attempting to run, even with an attached certificate of trust, cannot be run because the necessary macro interpreter is not installed on the system. Two scenarios can cause this error:
Your administrator chose not to install VBA
The version of Office you are running does not install VBA by default
If you are running an instance of Office that does not provide VBA as an installable feature, you will need to upgrade your version of Office to run VBA macros.
The certificate of trust is invalid - (when this occurs, you cannot select the Enable button).
The certificate is no longer trusted, was revoked by the issuing authority, or is damaged. There are several other possibilities which you may need to explore as to why the certificate is no longer recognized as valid by your system. It is recommended that you not trust or run a macro that has an invalid certificate.


----------



## ladymemi

OK, well never mind I fixed that I just changed it to Med. but now it is still giving me the runtime error " can not open specified file....


----------

