Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listbox Properties

    A few items here:

    I have a list box that contains a series of issues with varying priorities. The list box is pulled from column I on a worksheet, while the priority resides in column D.

    I have Four text labels, Critical, High, Medium, and Low. What I would like to do, is make the list box responsive. I.E. when a user clicks on a 'Critical' issue, the 'Critical' label (lblCritical) will be unhidden.

    I figure I can use whatever feedback you provide to make the notes and other details of the issue appear as the issue is selected as well.. Any help you can provide is appreciated!!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties

    Do you mean, when the user clicks somewhere on the spreadsheet within a segment deidcated to a critical issue, or do you mean, when the user chooses Critical from the list-box? Or do you mean something else?

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties

    When the user clicks on an item in the list box, on a userform. [img]/forums/images/smilies/smile.gif[/img]

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties

    Suppose you have a UserForm with a listbox named lstStatus, and a label named lblDynamic. Right-click the user form and choose Show Code. In the drop-down above and to the left, choose lstStatus; a code block for Private Sub lstStatus_Click() should appear. Into that code block, put the following code:

    lblDynamic.Caption = lstStatus.Value

    Run the user form and choose an item in the list box. The label should now have the same text as the item you selected.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties

    Bah...

    I need a way to determine what row the item selected in the list box is connected to... any ideas?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties

    NE1?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties

    Gifmeister I am not sure whether my solution will be what you are searching for or not. I use a drop down in a series of cells in column b which look up a list in column o. In column d I have a lookup that finds the price of what is selected in the drop down, by looking up column p. I have attached an example, you need to look at the breakdown sheet.
    Attached Files Attached Files

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties

    MikeB,

    Thanks for the feedback, I didn't see any pull-down boxes in your sheet... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    What i'm looking for is a programattic method for obtaining the row number of an item selected in a listbox, (the contents of which are based on a specific column.)

    Any help anyone?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties

    Would this work? I suppose i'll try:

    Public i As Integer
    Sub UserForm_Initialize()
    i = 3
    lblcritical.Visible = False
    lblhigh.Visible = False
    lblmedium.Visible = False
    lbllow.Visible = False
    End Sub

    Private Sub lstStatus_click()
    i = lstStatus.Row
    If lstStatus.Value = Range("K" & i).Value Then 'K is the issue definitions
    tbnotes.Value = Range("L" & i).Value
    tbrequestor.Value = Range("G" & i).Value
    tbdate.Value = Range("B" & i).Value
    tbtime.Value = Range("C" & i).Value
    tbtimeinqueue.Value = Range("E" & i).Value
    End If
    If Range("D" & i).Value = "Critical" Then lblcritical.Show
    If Range("D" & i).Value = "High" Then lblhigh.Show
    If Range("D" & i).Value = "Medium" Then lblmedium.Show
    If Range("D" & i).Value = "Low" Then lbllow.Show
    End If
    End Sub

    Okay.. running now..
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties

    Ok you are looking for a programmatical solution, but just to put the record straight, cells 1 - 50 in column B are list boxes. When you click on one a dropdown appears, which finds the list from another column. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties

    Drk,

    ListBoxes have a ListIndex property, so that .ListBox1.ListIndex = the position of the selected item in the source data. The first item if selected would return 0 the second 1 etc. So the row number can be computed from the source row and the ListIndex. Just add the value of ListIndex to the row number of the data.

    Hope that can help.

  12. #12
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties in another direction

    MikeB

    I downloaded your example into XL2000 and found it interesting, however, I can't figure out how to replicate it for a similar project.

    Is this a forms or an activeX control? I've tried it in the edit mode and right-clicking to get the properties to no avail and there are no macros or VBE codes associated with it. I understand how it should work with reference to the source list and all that, but, please <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23> tell me how you get any particular cell to contain the dropdown.

    My twist on your example is to use it with a variety of categories. I figure that I could have the list look up the cell to the left to determine the appropriate category. The source list itself would have a 3rd column associated with it for the categories. To use your example, if the dropdown was in the "Wine" section of the spreadsheet form, the list would only return a list of wines. Ditto for wooden cases and so on. If you have any additional examples in this direction, I would appreciate seeing that, but more importantly, I'd like to know how you do the dropdown directly from the cell(s).

    Many thanks in advance.

    AJF

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox Properties in another direction

    AFJ asked:
    > please tell me how you get any particular cell to contain the dropdown

    AFJ, Mike used Data Validation; a wonderful feature of Excel. Just click on a cell in coulum B and use the Data | Validation menu to see how to set it up. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Properties in another direction

    Sam, thanks. I never revisited Validation since exploring it years ago. Now I'll play with it with what I know now.

    The more you learn.... Thanks again

    AJF

Posting Permissions

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