Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: Dbl-clicking on an item in a listbox

    Hi,

    I have written this code for an Excel Listbox where when I double-click on any item, I want to select a particular cell in the spreadsheet. Here sheet1 contains the values bellow and only the values that have an 'x' in column B are loaded in the list box. Now the problem is that since the list in the listbox correpond to an index, when I double-click on the item in the listbox, I want the item selected to cell activate the cell that has the same value. that is, in the case bellow, the listbox only contains one, three and five. if I double-click on the item 'three' in the list box, then i want the cell A3 to be selected. right now when I double click on the item in the listbox it activates only the index number the item in the listbox correponds to: Can anybody help me?

    Sheet1 has values:

    A B
    one x
    two
    three x
    four
    five x
    six

    Here is the code I wrote:

    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Integer
    Dim ans As String

    For i = 0 To ListBox1.ListCount
    If ListBox1.ListIndex = i Then
    Range("A" + CStr(ListBox1.ListIndex)).Activate
    End If
    Next
    End Sub


    Private Sub UserForm_Initialize()
    Dim cell As Range
    Range("A1").Activate
    For Each cell In Range("A1:A10")
    If Range("B" & CStr(ActiveCell.Row)).Value = "x" Then
    UserForm1.ListBox1.AddItem (cell.Value)
    End If
    Range("A" & CStr(ActiveCell.Row)).Offset(1, 0).Activate
    Next cell
    End Sub

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

    Double_Click Listbox

    Hi Alexxx12

    I thought I had posted a reply to to this question before !? It must be my age.

    However, with a little more info and time, try this code.

    Code:
    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Integer
    Dim ans As String
    
    With ListBox1
        Range("A" & .List(.ListIndex, 1)).Activate
    End With
    End Sub
    
    
    Private Sub UserForm_Initialize()
    Dim cell As Range
    Dim ArrayList()
    Dim i As Integer
    
    i = -1
    For Each cell In Range("A1:A10")
        If Range("B" & cell.Row).Value = "x" Then
            i = i + 1
        End If
    Next cell
    If i = -1 Then
        MsgBox "No data found."
        Exit Sub
    End If
    ReDim listarray(i, 1)
    
    i = 0
    Range("A1").Activate
    For Each cell In Range("A1:A10")
        If Range("B" & cell.Row).Value = "x" Then
            listarray(i, 0) = cell.Value
            listarray(i, 1) = cell.Row
            i = i + 1
        End If
        'Range("A" & CStr(ActiveCell.Row)).Offset(1, 0).Activate
        
    Next cell
    
    ListBox1.List() = listarray()
    
    End Sub
    MTB

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

    LostBox_DoubleClick

    Hi again

    Just noticed some deleberate(?) mistakes, but it still worked !!

    Code:
     Option Explicit
    
    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Integer
    Dim ans As String
    
    With ListBox1
        Range("A" & .List(.ListIndex, 1)).Activate
    End With
    End Sub
    
    
    Private Sub UserForm_Initialize()
    Dim cell As Range
    Dim ListArray() As String
    Dim i As Integer
    
    i = -1
    For Each cell In Range("A1:A10")
        If Range("B" & cell.Row).Value = "x" Then
            i = i + 1
        End If
    Next cell
    
    If i = -1 Then
        MsgBox "No data found."
        Exit Sub
    End If
     
    ReDim ListArray(i, 1)
    
    i = 0
    Range("A1").Activate
    
    For Each cell In Range("A1:A10")
        If Range("B" & cell.Row).Value = "x" Then
            ListArray(i, 0) = cell.Value
            ListArray(i, 1) = cell.Row
            i = i + 1
        End If
    Next cell
    
    ListBox1.List() = ListArray()
    
    
    End Sub
    The real reason for posting again is to note that the listbox ColumnCount property will need setting to 2 for this to work. Also the column widths will need setting as you require (colum 2 = 0 if you want to hide the row number column).

    MTB

  4. #4
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139
    Hi,

    This is great! That is exactly what I was looking for. However, I have one last question: How do I refresh the listbox everytime I open it? Right now, I have to close the spreadsheet or workbook and then open it again to refresh the listbox. Is there a vb command that allows this?

    Regards,

    Alexxx12

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

    I do not know what user interface you have designed, but I assume you are 'closing'
    the form with Me.Hide. If so, then the Initialise and Activate events will not fire when the Userform.Show method is used to reshow the form (I don't know why when they do in Access and VB6 !?).

    Therefore, if this is the case use the 'Unload Me' statement to remove the form from memory. It will the fire the Initialize and Activate events when reloaded.

    Good luck!

    BTW, does anyone out there know why the activate event does not fire when the form receives the focus when re-shown?? At present the Activate event seems redundant!


    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
  •