Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    45

    Unanswered: listbox select item

    Hi
    Wind: XP
    Excel: 2k
    I have two questions:

    I have created a listbox in excel, which contains values from

    Private Sub UserForm_Initialize()
    ListBox1.ColumnCount = 4
    ListBox1.RowSource = "b11:j17"
    End Sub

    from sheet1. While it loads all the data from rang("b11:j17"), I would like it to load only those line that have an ‘X” in ‘Open’, which is the 'C' column. The first question is how can I do this?


    The values from the listbox come from sheet1 and the listbox looks exactly like the example bellow where ‘closed and ‘open’ are column and the rest are values in those:
    A B C
    closed open
    one x
    two x
    three x
    four x
    five x
    six x

    Then, I want to user to select only one value from the listbox from the listbox and perform a task. However, when the user double click on the value, say the first one:

    one x

    the task that corresponds to that selection does not correspond to the line on sheet1. the only way I can make sure it does is by selecting the row before I open the listbox. Is there a way to fix this?

    Here is the code:

    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim newval As Date
    If Sheet1.CommandButton1.Caption = "Start" Then

    Sheet1.CommandButton1.Caption = "Stop Old"
    Range("J2").Interior.ColorIndex = xlNone
    'ActiveCell.Value = Time
    Range("J2").Value = ""
    Range("H2") = Now
    Range("I" & CStr(ActiveCell.Row)).Activate
    Call StartTimer
    Call MyMainMacro
    UserForm2.Hide

    ElseIf Sheet1.CommandButton1.Caption = "Stop Old" Then
    ActiveCell.Value = Time
    If Range("J2").Cells.Interior.ColorIndex = 6 Then
    Range("J" & CStr(ActiveCell.Row)).Cells.Interior.ColorIndex = 6
    End If
    Call StopTimer
    Call StopIt
    UserForm2.Hide
    UserForm1.Show
    Sheet1.CommandButton1.Caption = "Start"
    End If
    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    To load only specific items into the list box you will need to load (the required) lines of data from the sheet into an array in code and assign the array to the listbox List property ie. Listbox.List()=ArrayName()

    The data from the select item can be abtained as follows

    Listbox1.value - This is the value in the defined 'bound column' property, or

    Listbox.List(row,column) - Where 'row' is the selected row (given by Lisbox1.ListIndex property) and column is the column containing the data required.

    Using mulitlpe selection is a little more complicated if required, but all the above, and information on multiple selection properties, is available in the VBA Help.

    Trust this helps.

    MTB

  3. #3
    Join Date
    Mar 2003
    Posts
    45

    range("A" & CStr(....

    Hi,

    when I click on the selected row in the list box, i want to activate the cell A(i) that the row corresponds and that is selected in the listbox. How do I transalate
    range("A1").activate
    where 1 is = i the loop bellow?

    the line 'Range("A" & CStr(i)).Activate' does not work. How can I make it work?

    Dim i As Integer
    Dim ans As String
    For i = 0 To ListBox1.ListCount - 1
    If ListBox1.ListIndex = i Then
    Range("A" & CStr(i)).Activate
    End If
    Next

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi alexxx15

    This code in will select the 'A' Cell of the required row when a new item is selected in the list

    [code]
    Private Sub ListBox1_Click()
    Dim i As Integer

    With ListBox1
    For i = 0 To .ListCount - 1
    If .ListIndex = i Then Range("A" & i + 1).Select
    Next i
    End With
    End Sub
    [code/]

    MTB

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    Just looking at it again (engage brain etc....), it is even simpler (based on your example) this will do the same !

    Code:
    Private Sub ListBox1_Click()
        Range("A" & ListBox1.ListIndex + 1).Select
    End Sub
    Although I obviously do not know what you are ultimatly doing (ie you are not using a list box to select a cell for no reason!) but this will do what you have asked?

    MTB

Posting Permissions

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