Hyperlinked Names In DropDown Box

johnsonmeb · Feb 26, 2012

Hello,

I have a spreadsheet with the same dropdown list in each cell of one column. I would like each drop down list to behave in the following manner:

When a user selects the appropriate name from the drop down list the name turns into a "blue colored" active hyperlink to an external website. The selected name should stay the same, so the actual address parameters of the hyperlink associated with the name would be hidden.

When this name is clicked on it opens up the browser and goes to that particular website which is unique to the selected name.

A simple data validation list would be preferred, but it does not appear to work unless you put the actual hyperlink in an adjacent cell.

It appears that an ActiveX combo box with appropriate VBA code may be the correct approach.

The key is that the name in the combo box must behave as a hyperlink but not actually change because that particular name is used in other calculations on the spreadsheet.


Thanks for any help.

bertie · Feb 26, 2012

Hi and welcome to the forum.

You could do this using a worksheet change event (VBA).

Open a new Excel Workbook.

In column A of sheet1 create a data validation list using: Google, MSN, Yahoo.

Right cllick the Sheet1 tab and select view code.
Copy and paste the code below:

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
 
   [COLOR=green]'assumed hyperlinks in column A[/COLOR]
   [COLOR=darkblue]If[/COLOR] Target.Column <> 1 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]If[/COLOR] Target.Cells.Count > 1 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
   CreateHyperlink Target
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CreateHyperlink([COLOR=darkblue]ByVal[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range)
   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] U[COLOR=darkblue]Case[/COLOR](rng.Value)
      [COLOR=darkblue]Case[/COLOR] "GOOGLE"
         rng.Hyperlinks.Add Anchor:=Selection, Address:= _
        "[URL]http://www.google.com[/URL]", TextToDisplay:="Google"
      [COLOR=darkblue]Case[/COLOR] "MSN"
         rng.Hyperlinks.Add Anchor:=Selection, Address:= _
         "[URL]http://www.msn.com[/URL]", TextToDisplay:="MSN"
      Case [COLOR=darkblue]Else[/COLOR]   [COLOR=green]'Yahoo[/COLOR]
          rng.Hyperlinks.Add Anchor:=Selection, Address:= _
        "[URL]http://www.yahoo.com[/URL]", TextToDisplay:="Yahoo"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Go back to Sheet1 and test the code.
I have used a Select Case statement for ease of editing.

johnsonmeb · Feb 27, 2012

Thanks for your help!

I did the appropriate customization, and this worked perfectly.

How could I modify the code to make the cell blank if none of the cases applied?

I would assume that this would be handled in the "Case Else" section at the end making the cell value null in this scenario.

Thanks once again.

bertie · Feb 27, 2012

Yep, this would be handled in the Case Else statement. I have also toggled Application.EnableEvents while writing to the worksheet, and used an error trap to reset. Just in case.

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CreateHyperlink([COLOR=darkblue]ByVal[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range)
 
 [COLOR=red]  On Error GoTo exitHere[/COLOR]
[COLOR=red]  Application.EnableEvents = False[/COLOR]
 
   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] U[COLOR=darkblue]Case[/COLOR](rng.Value)
      [COLOR=darkblue]Case[/COLOR] "GOOGLE"
         rng.Hyperlinks.Add Anchor:=Selection, Address:= _
        "[URL]http://www.google.com[/URL]", TextToDisplay:="Google"
      [COLOR=darkblue]Case[/COLOR] "MSN"
         rng.Hyperlinks.Add Anchor:=Selection, Address:= _
         "[URL]http://www.msn.com[/URL]", TextToDisplay:="MSN"
      [COLOR=darkblue]Case[/COLOR] "YAHOO"
          rng.Hyperlinks.Add Anchor:=Selection, Address:= _
        "[URL]http://www.yahoo.com[/URL]", TextToDisplay:="Yahoo"
[COLOR=blue]     Case Else[/COLOR]
[COLOR=blue]        With rng[/COLOR]
[COLOR=blue]           .Hyperlinks.Delete[/COLOR]
[COLOR=blue]           .Value = ""[/COLOR]
[COLOR=blue]        End With[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
[COLOR=red]exitHere:[/COLOR]
[COLOR=red]  Application.EnableEvents = True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

johnsonmeb · Feb 27, 2012

Thanks for the modification. It works perfectly!

Hyperlinked Names In DropDown Box

johnsonmeb

New Member

Excel Facts

bertie

Well-known Member

johnsonmeb

New Member

bertie

Well-known Member

johnsonmeb

New Member

Similar threads

Forum statistics

Share this page

Hyperlinked Names In DropDown Box

johnsonmeb

New Member

Excel Facts

bertie

Well-known Member

johnsonmeb

New Member

bertie

Well-known Member

johnsonmeb

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock