Results 1 to 3 of 3

Thread: find selected combobox item's row

  1. #1

    find selected combobox item's row

    I have the code below. I've used it in another workbook userform successfully but it errors on
    strFullAddress = .Address
    Anyone see what the problem is? I'm losing it!

    Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 13 Then  
            Dim rngCell As Range
             Dim strFullAddress As String
            Set rngCell = Cells.Find(What:=UserForm1.ComboBox1.Value, After:=Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            With rngCell
                strFullAddress = .Address    
            End With
            a = Sheets("sheet1").Range(strFullAddress).Row
            TextBox1.Text = Sheets("sheet1").Range("a" & a).Value
            ComboBox2.Text = Sheets("sheet1").Range("c" & a).Value
            ComboBox3.Text = Sheets("sheet1").Range("d" & a).Value
            ComboBox4.Text = Sheets("sheet1").Range("e" & a).Value
            ComboBox5.Text = Sheets("sheet1").Range("f" & a).Value
            ComboBox6.Text = Sheets("sheet1").Range("h" & a).Value
            ComboBox7.Text = Sheets("sheet1").Range("j" & a).Value
           TextBox3.Text = Sheets("sheet1").Range("l" & a).Value
       End If
    End Sub
    Last edited by Aussiebear; 01-31-2025 at 03:07 AM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Maybe try

    Set rngCell = Cells.Find(What:=UserForm1.ComboBox1.Value, After:=Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        If Not rngCell Is Nothing Then
        With rngCell
            strFullAddress = .Address
        End With
    Else
        Msgbox "oops!"
    End If
    Last edited by Aussiebear; 01-31-2025 at 03:08 AM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3

    solved

    that helped. I also had to reference the sheet as I had the wrong sheet in view. Thanks!

Posting Permissions

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