Pre-selecting Listbox value from cell value in Userform

**peterchclarke** · 11-19-2019, 05:48 AM

Good Morning All!

I am loading a userform that gets populated with a number of fields from a table depending on the currently selected row.

I would like to use a listbox in the userform to give the user options to change a cell in that row. Effectively using a flat database and a basic front end to edit the data.

I am struggling to get the listbox to be pre-selected with a value.

I am populating the listbox with a dynamic named range to enable more options to be added in the future.

Dim rRange As Range
Dim rCell As Range
Dim lCount As Long

Set rRange = Range("STATUStbl")

    For Each rCell In rRange
        ListBox1.AddItem rCell.Value
    Next

the above code successfully populates the listbox, the below code doesn't successfully autoselect the value in column 2 of the table!

     
With ListBox1
        For lCount = 0 To .ListCount - 1
            If (.Column(2, lCount)) = Cells(ActiveWindow.RangeSelection.Row, 2) Then
                    .Selected(lCount) = True
            Else
                    .Selected(lCount) = False
            End If
        Next
End With

Please see attached, and please help!

**AlphaFrog** · 11-19-2019, 07:59 AM

Private Sub UserForm_Initialize()

Dim v As Variant
'Dim rRange As Range
'Dim rCell As Range
'Dim lCount As Long

'Set rRange = Range("STATUStbl")
'
'    For Each rCell In rRange
'        ListBox1.AddItem rCell.Value
'    Next
    
With ListBox1

        .List = Range("STATUStbl").Value
        v = Application.Match(ActiveCell.EntireRow.Cells(1, 2).Value, .List, 0)
        If Not IsError(v) Then .Selected(v - 1) = True
'        For lCount = 0 To .ListCount - 1
'            If (.Column(2, lCount)) = Cells(ActiveWindow.RangeSelection.Row, 2) Then
'                    .Selected(lCount) = True
'            Else
'                    .Selected(lCount) = False
'            End If
'        Next
End With
    

'Position the resize icon
lblResizer.Left = Me.InsideWidth - lblResizer.Width
lblResizer.Top = Me.InsideHeight - lblResizer.Height
minHeight = 354.75
minWidth = 450


End Sub

**peterchclarke** · 11-19-2019, 08:18 AM

AlphaFrog,

Can't thank you enough. I'm not looking for a lesson, but can you see where I was coming from and can you tell me why it wasn't working??????

Best, Pete

**AlphaFrog** · 11-19-2019, 08:26 AM

You're welcome.

If (.Column(0, lCount)) = Cells(ActiveWindow.RangeSelection.Row, 2) Then

Pre-selecting Listbox value from cell value in Userform

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Pre-selecting Listbox value from cell value in Userform

Re: Pre-selecting Listbox value from cell value in Userform

Re: Pre-selecting Listbox value from cell value in Userform

Re: Pre-selecting Listbox value from cell value in Userform

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] De-selecting on Listbox Query (When There Are Two Listboxes On Userform)

[SOLVED] Listbox selecting another first item after modification on sheet trough userform

Userform Selecting listbox items erroneously

userform listbox

Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox entry.

Listbox - automatically selecting an option based on a cell's value

Bookmarks

Bookmarks

Posting Permissions