Consulting

Results 1 to 13 of 13

Thread: Combo Box Index select

  1. #1

    Combo Box Index select

    I have a userform that loads symbols in a combo box from column A that do not have a date listed in column D. When I select the symbol from the combo box, I would like to select the row corresponding to my selection. Can someone assist with a code that can achieve this.

    Thank you kindly
    Attached Files Attached Files

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Private Sub cbName_Change()
        
        If cbName.ListIndex > -1 Then
            With Sheets("sheet1").Range("a5").CurrentRegion
                .AutoFilter
                .AutoFilter 1, cbName.Value
                .AutoFilter 4, "="
            End With
        End If
    
    
    End Sub

  3. #3
    Hi Mana....The code is filtering. Can you code it to only select the row?

    Thank you for you kind help

  4. #4
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Option Explicit
    
    Private Sub UserForm_Initialize()
        Dim r As Range, c As Range
        Dim a()
        Dim n As Long
        
        On Error Resume Next
        Set r = Sheets("sheet1").Range("a6").CurrentRegion.Columns(4).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        
        If r Is Nothing Then Exit Sub
        
        ReDim a(r.Count - 1, 1)
        
        For Each c In r
            a(n, 0) = c.Offset(, -3).Value
            a(n, 1) = c.Address(, , , True)
             n = n + 1
        Next
        
        With cbName
            .ColumnCount = 2
            .BoundColumn = 1
            .TextAlign = fmTextAlignLeft
            .ColumnWidths = "30;0"
            .List() = a()
        End With
        
    End Sub
    
    
    '
    Private Sub cbName_Change()
    
        If cbName.ListIndex > -1 Then
            Application.Goto Range(cbName.List(cbName.ListIndex, 1))
        End If
            
    End Sub

  5. #5
    Thank you Mana...Is there a way for it to select the cell in column A once it finds the selection?

  6. #6
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
        ReDim a(r.Count - 1, 1)
        
        For Each c In r.Offset(, -3)
            a(n, 0) = c.Value
            a(n, 1) = c.Address(, , , True)
             n = n + 1
        Next

  7. #7
    I have data in rows 1 - 4 and a header in Row 5, How can I account for this. Because I have 4 spaces in the combo box preceding the list

  8. #8
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
        On Error Resume Next
        Set r = Sheets("sheet1").Range("a6")
        Set r = Range(r, r.End(xlDown))
        Set r = r.Columns(4).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0

  9. #9
    Perfect...Thank you very much for you patience, understanding, and expertise.

    Cheers

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    What did you learn from this thread ?

  11. #11
    Digesting the variables. I'm using F8 to step through the code to understand what's happening.

  12. #12
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I didn't see any VBA code from your side.

  13. #13
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    As Mana showed you: the userform is 100% redundant.

    Private Sub Workbook_Open()
        Sheet1.Cells(6, 1).CurrentRegion.AutoFilter 4, "="
    End Sub

Posting Permissions

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