Drop Down in Excel (Excel 2000)

Topic

New Reply

I am attempting to put a drop down list in excel where the user can click on a name in the list and be taken by hyperlink to either the bookmark or the web site in Excel. I have no problem asigning a hyperlink to a cell address, and I have no problem creating the drop down list with VBA with the proper names listed but cannot seem to figure out the code for the selected names. The drop down is on the Excel sheet and is not associate with a userform.

Reply | Quote

Replies

I wasn’t sure whether you were aiming to achieve it via a linked cell, or directly in VBA.

For a linked cell, just use the ListRange & LinkedCell properties – they seemed to work fine

Alternatively, in VBA I used Listbox.Value which worked fine also.

Reply | Quote

I can get the drop down to work but when I click on the choice in the drop down it will not hyperlink me with the object. I think VBA is the only way to have the hyperlink to execute on the click from the drop down box. But if you have another way, I would love to hear it. Given the size of the data, I want to go to the data. Not the other way around which seems the primary purpose of the standard dropdown in Excel. Here is the code I have been working with.

Private Sub UserForm_Initialize()
ComboBox1.AddItem “Ankle”
ComboBox1.AddItem “Arm”
ComboBox1.AddItem “Cervical Back”
End Sub

Private Sub ComboBox1_Change()

ComboBox1.DropDown

Select Case ComboBox1.Value
Case 0 ‘Left Top
CommandButton1.Caption = “Ankle”

Worksheets(“July 2001 Surgical Estimates”).Range(“v3”).Hyperlinks(1).Follow ‘tried this
Worksheets(1).Range(“v3”).Hyperlinks.Item(1).Follow NewWindow:=False, AddHistory:=True ‘ tried this
Range(“b793”).Select ‘tried this

Case 1
CommandButton1.Caption = “Arm”
Range(“b793”).Select

Case 2
CommandButton1.Caption = “Cervical Back”
Range(“b793”).Select

End Select
End Sub

Reply | Quote

I struggled to see how your hyperlinks were being set. Could I just clarify my understanding

Your combo box contains a list of names (e.g. “Ankle”) which are text strings (not hyperlinks)
Based on the choice you wish to link to some other place
You can identify the choice – but are now wanting to jump to elsewhere.

Your problem seems to be to link using a hyperlink. If one existed in a cell such as A1 then
range(“a1”).hyperlinks(1).follow
statement would have worked.

To create a hyperlink programatically you’d need a
ActiveSheet.Hyperlinks.Add
statement to have created it in the first place.

I didn’t see one in your code. It would be easy enough to add via VBA (if you knew the target address), or, depending on your application, in advance.

If it were added in advance, it has to live in a cell, which your combo box routine would then have to ‘follow’

I’m getting into assumption space here but, I assume you’re thinking of the the names in the combo box as links – at best they will be the link’s “TextToDisplay” value and will need to be used in a lookup of the hyperlinks object for a match, then followed by a follow.

Reply | Quote

I continue to struggle with this. I am kind of surprised since this seems to be such a standard feature in Java but few folks use Excel as a reference sheet to jump around in. The hyperlinks in this case are actually bookmarks. Since bookmarks are treated much as URL’s are in creating links for Excel documents, I figured I could use the same or similar language to use the drop down box.

“Ankle” is bookmarked for cell B793.
“Arm” is bookmarked for cell B259
“Cervical_Back” is bookmarked for B435
etc.

There are bunch others but I am can replicate the formula once I have statement which works. Maybe I should put the ActiveSheet.Hyperlinks.Add in the UserForm_Initialize() module to add the names and the hyperlinks to the combo box.

I tried the following:

Select Case ComboBox1.Value
Case 0 ‘ First case
CommandButton1.Caption = “Ankle”
Range(“b793”).Hyperlinks(“Ankle”).Follow
Range(“b793”).Select
‘ other choices to follow

end select
end sub

I cannot seem to get the drop box to take me to cell B793 when I click on ankle in the dropdown box. I must be missing something obvious.

Reply | Quote

This might seem trivial/obvious, but I see no code to change the bound column of the combo box to zero (“0”).

If that is not done, the default is one (1) and the combobox.values are the SELECTION value (ankle, arm, etc) and NOT the INDEX value (0,1,2,…). Your “case” works on the index.

Have you checked what the combobox1.values are while debugging?

Steve

Reply | Quote

Can you post a snippet of the sheet?

Reply | Quote

Is this what you are after?

I did not create a name for everything, only for the Pulldown list (to display) and the address list.

The address is where you want to go for the various item in the pulldown. I did not use the add method, but used the named range, I find it easier to use and update

Steve

Reply | Quote

This is close. I will work with it. Here is a simplified version of the worksheet.

Reply | Quote

I might be obtuse, but where is the UserForm that is supposed to be initialized?

You just have a combobox on the worksheet.

Steve

Reply | Quote

Sorry to take so long to look at it.
I think that you’re attempting to do something taht isn’t the way hyperlinks work
In your combo box Case statement you code, for Ankle:

Range(“b793”).Hyperlinks(“Ankle”).Follow
Range(“b793”).Select

Instead, I think you should code

Range(“W3”).Hyperlinks(1).Follow

Where “W3” is the cell address of your hyperlink in the example you gave.

Reply | Quote

You might try testing the target location in the worksheet change event for subjects you want and then perform your action based on the results. Such as:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 1 Then
    If Target.Value = "arm" Then
        MsgBox ("You selected the topic " & Target.Value)
    ElseIf Target.Value = "leg" Then
        MsgBox ("You selected the topic " & Target.Value)
    End If
End If
End Sub

Reply | Quote

Like the others, I do not think all of the items are in place to do what you want. There is no form to initialize and I could not find a command button to change the caption. In the attached, I have put data validation, list option in cell B2, where the user can choose options (you can change the range if it is not what you want). The VBA tests the value in B2, where you can put whatever code you like depending on the user’s choice.

Reply | Quote