Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box and Hyperlinks (E2000)

    Afternoon All,

    I have several combo boxes that are working fine. By that I mean, as the user makes a selection in one box, it narrows down the choices in the next box(s)...and so on...no problem. However, the last cboBox is nothing more than Hyperlinks. I can't get the hyperlinks to work from within the cboBox. Does anyone have any suggestions?

    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box and Hyperlinks (E2000)

    Hello Roberta,

    I presume that you mean that your combo box is filled from a range of cells containing hyperlinks.

    If the combo box was created from the Forms toolbar, I don't know how to do what you want, but I'm sure one of our resident Excel gurus will.

    If the combo box was created from the Control Toolbox, you can write code in the On Click event of this combo box. Say it is named ComboBox1 in a desperate spurt of creativity, then you could use this:

    Private Sub ComboBox1_Click()
    Me.Range(Me.ComboBox1.ListFillRange).Cells(Me.Comb oBox1.ListIndex).Hyperlinks(1).Follow
    End Sub

  3. #3
    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: Combo Box and Hyperlinks (E2000)

    You would do it the same way as for the other one.
    Create a macro based on the inputRange and CellLink for the combobox:

    Sub GoToHyperlink()
    ActiveSheet.Hyperlinks(Range("InputRange").Cells(R ange("CellLink").Value).Value).Follow
    End Sub

    Assign the macro to the combobox.

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box and Hyperlinks (E2000)

    Thanks, Steve. I was trying to think of ways to get at properties of a Forms combo box in code, but named ranges will work fine.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box and Hyperlinks (E2000)

    Good Morning Hans,

    You're correct -- I created the combo boxes from the Control Toolbox. I modified your code to adapted to my names but got the following errors.

    Code: Me.Range(Me.cboLinks.ListFillRange).Cells(Me.cboLi nks.ListIndex).Hyperlinks(1).Follow
    Error: Method or Data member not found ---with "ListFillRange" highlighted...any ideas?

    Thanks for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box and Hyperlinks (E2000)

    Roberta,

    Perhaps we can solve it in a simple way. Have you linked the combo box to a cell that returns the value selected by the user? If not, you can set this as follows:

    Switch to design mode
    Right click the combo box and select Properties.
    Type the address of a cell in the LinkedCell property.

    Let's say you use A10 for this purpose. Replace the line

    Me.Range(Me.cboLinks.ListFillRange).Cells(Me.cboLi nks.ListIndex).Hyperlinks(1).Follow

    by

    Me.Range("A10").Hyperlinks(1).Follow

    This is less flexible - if you change the LinkedCell property of the combo box, you will have to change the code too - but also a lot simpler.

  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: Combo Box and Hyperlinks (E2000)

    I got it to work with:

    <pre>Me.Hyperlinks(Me.Range(Me.ComboBox1.LinkedCel l).Value).Follow</pre>


    I didn't have to link to theListFillRange since (I assumed) the boundcolumn is 1 so the LinkedCell is the "name of the hyperlink".

    Also (in excel 97, at least) you will have to add a line like:
    <pre>ActiveCell.Select</pre>

    Before the hyperlink command to remove the focus from the combobox. XL97 is very picky about this in many circumstances.

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box and Hyperlinks (E2000)

    Hi Steve,

    Thanks for your help. I am working with E2000...sorry if I left that out originally. I tried the code you suggested with mods for me accordingly.

    code: Me.Hyperlinks(Me.Range(Me.cboLinks.ListIndex).Valu e).Follow
    error: Method or data member not found w/Range highlighted.

    Not sure what I'm doing wrong.

    Maybe I should be more clear.
    cboDept (cbo 1) contains Names of Departments
    cboForms (cbo 2) contains Names of various Forms
    cboLinks (cbo 3) contains Hyperlinks

    On the spreadsheet, there are many different Ranges:
    Executive
    HR
    Foremost, etc*****************these would be dept

    OT Forms
    New Hire Forms
    HRForms, etc*******************these would be Forms for HR...but the Range Name is HR

    Executive
    Information
    Expense Sheet, etc.....these would be Forms for Executive , but the Range Name would be Executive

    Welcome
    W2, etc*************************these would be Hyperlinks for HR, but Range Name is HRLinks.

    And so on..

    As the user selects a Department (cbo 1), the search narrows down as to what he/she can select from as far as what Forms will be accessible (cboForms (cbo 2). When the user selects a form (cbo 2) then cboLinks (cbo 3) allow the user to make a selection of Hyperlinks ...... which isn't working.

    Perhaps I've made things more clear...maybe I've done something wrong which is causing yours and Hans' suggestions to NOT work. Any suggestions?

    Thanks again.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  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: Combo Box and Hyperlinks (E2000)

    Is the BoundColumn Property = 1? DO you have a linkedcell that gives the value selected?

    I don't have a LISTINDEX property in XL97 so I used the linkedcell to test.
    Did you try using my suggestion of LINKEDCELL vs LISTINDEX?

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box and Hyperlinks (E2000)

    Steve,

    Yes. I used your suggestion exactly. LinkedCell. And yes the Bound Column is 1.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box and Hyperlinks (E2000)

    Roberta,

    Perhaps one of the following will work:

    ActiveSheet.Hyperlinks(Range("A10").Value).Follow

    or

    ActiveSheet.Range("A10").Hyperlinks(1).Follow

    Replace A10 by the cell acting as linked cell for the combo box. These instructions avoid any mention of the combo box itself or of the worksheet object as "Me",

  12. #12
    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: Combo Box and Hyperlinks (E2000)

    What happened? same error or nothing?

    My links did not work until I added:
    ActiveCell.select to the beginning of the code (to unselect the object), did you try this?

    Is the Value in the linked cell the hyperlink?

    Steve

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box and Hyperlinks (E2000)

    Hans & Steve,

    Well...I think we're getting somewhere at least. [img]/forums/images/smilies/smile.gif[/img]

    I used the last suggestion of Hans' and it at least works....however, no matter what my choice of hyperlinks is, it only returns the hyperlink in that specific cell, in my case E2. Even if I make other choices, the only hyperlink that opens is the one in E2.

    Open to other ideas.

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  14. #14
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box and Hyperlinks (E2000)

    Steve,

    yes, I tried your code and got "method or no data member found" w/Range highlighted and didn't put ActiveCell Select...but I'm not using E97, I'm using E2000.

    All suggestions have failed until Hans' last suggestions. Both work...but will only get the hyperlink that is referenced in the code i.e. E2 or A10 whichever the case maybe.

    Thanks again.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  15. #15
    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: Combo Box and Hyperlinks (E2000)

    Does E2/A10 or whatever the cell link to the combobox, CHANGE to the appropriate hyperlink name when you change the combobox.

    The idea is that the combobox puts a new item in the cell and then it follows the hyperlink that is named that.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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