Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Keizer, Oregon, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drop Down in Excel (Excel 2000)

    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.

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down in Excel (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

    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.

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Keizer, Oregon, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down in Excel (Excel 2000)

    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).Follo w 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

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down in Excel (Excel 2000)

    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:

    <pre>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
    </pre>


  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down in Excel (Excel 2000)

    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.

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Location
    Keizer, Oregon, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down in Excel (Excel 2000)

    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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Drop Down in Excel (Excel 2000)

    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

  8. #8
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down in Excel (Excel 2000)

    Can you post a snippet of the sheet?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Drop Down in Excel (Excel 2000)

    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
    Attached Files Attached Files

  10. #10
    New Lounger
    Join Date
    Oct 2002
    Location
    Keizer, Oregon, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down in Excel (Excel 2000)

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

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Drop Down in Excel (Excel 2000)

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

    You just have a combobox on the worksheet.

    Steve

  12. #12
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down in Excel (Excel 2000)

    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.

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down in Excel (Excel 2000)

    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •