VBA, Userform, Search Function show result for Referance number in text box, would this to also select corisponding row in list box.

andrewmckat

New Member
Joined
Jun 14, 2020
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hi all,

Fairly new to VBA.

I have a userform that allows you to enter Clients Information into text boxes and add this data to a table in excel. You can then search by Customer Reference no. to show results in textbox they were entered to update etc. I also have a list box that shows all available clients.

When I Search for a client Reference no. I would also like it to select the correct row in the list box.

note that it is also set up so that you can click on the client in the listbox and the information will be shown it text box just like if you were to search.
I have attached screenshots of the userform and also codes used for search and Listbox

Regards

Andy
 

Attachments

  • Screenshot (1).png
    Screenshot (1).png
    90.7 KB · Views: 14
  • Screenshot (2).png
    Screenshot (2).png
    138.1 KB · Views: 12
  • Screenshot (3).png
    Screenshot (3).png
    144.4 KB · Views: 11

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,181
Office Version
  1. 2019
Platform
  1. Windows
Hi
welcome to forum

What you want is most likely doable but with a complex project, always helpful if you can place a copy of your workbook with dummy data in a dropbox & provide a link to it here.


Dave
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,805
The way that I would do it would be to search the ListBox for the matching reference number and take the text box entries from the ListBox.

But with the code that you posted, it looks like you need to set the .Selected property of the listbox in CmdSearch_Click.
The last line should be ClientList.Selected(i+2).Value = True, although I also suspect that you might want - 2 instead of +
 

andrewmckat

New Member
Joined
Jun 14, 2020
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
link to excel workbook with userform
 

andrewmckat

New Member
Joined
Jun 14, 2020
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Hi
welcome to forum

What you want is most likely doable but with a complex project, always helpful if you can place a copy of your workbook with dummy data in a dropbox & provide a link to it here.


Dave
Hi Dave,

Thanks for the advice I have posted a link in the comments now.

andy
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,181
Office Version
  1. 2019
Platform
  1. Windows
Hi Dave,

Thanks for the advice I have posted a link in the comments now.

andy

seen it thanks - file will hopefully, make it easier for forum to assist you

Dave
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,181
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi,

Found a few moments to have a look at your project - As you have set it up so that you can click on the client in the listbox and the information will be shown textboxes it occurs to me that all you need do is to search your listbox & select the required record.

Try updated Search code below

Code:
Private Sub CmdSearch_Click()
    Dim i As Long
    Dim Search As String
    
    Search = Me.TxtSearch.Text
    
    With Me.ClientList
        If Len(Search) > 0 Then
            For i = 0 To .ListCount - 1
                If Format(.List(i, 11), "000") = Search Then
                    .TopIndex = i
                    .ListIndex = i
                    Exit Sub
                End If
            Next i
            MsgBox Search & Chr(10) & "Customer Number Not Found", 48, "Not Found"
        Else
            .ListIndex = -1
            .TopIndex = 0
        End If
    End With
End Sub

Note I assume that you are searching Column 11 (columns start at 0 in listbox) for a three-digit numeric value – In this case, I have applied format to ensure leading zero (0) is not omitted from List values being tested against entered search value. e.g. 095

For above idea to work very likely that your UserForm_Initialize event will need some updating

Replace existing code with following

Code:
Private Sub UserForm_Initialize()
    Dim wsClientList As Worksheet
    Dim lastrow As Long, lastcolumn As Long
    
    Set wsClientList = ThisWorkbook.Worksheets("Client List")
    
    If wsClientList.ListObjects("ClientList") <> ClientList Then
        MsgBox "Table to be Named ' ClientList '! on 'Client List' Sheet", vbExclamation
    End If
    
    With wsClientList.Range("A1").CurrentRegion
        lastrow = .Rows.Count - 1
        lastcolumn = .Columns.Count
    End With
    
    
    Call SortByForename
    
    With Me.ClientList
        .ColumnCount = lastcolumn
        .ColumnHeads = True
        .RowSource = "'" & wsClientList.Name & "'!" & wsClientList.Cells(2, 1).Resize(lastrow, lastcolumn).Address
    End With
    
    
    Label27.Caption = Format(Date, "ddd d mmm yyyy")
    
    ComboBox1.AddItem ("")
    ComboBox1.AddItem ("Active")
    ComboBox1.AddItem ("Archived")
    ComboBox1.AddItem ("Suspended")
End Sub

Updates not fully tested but hopefully enough for you to work with & goes in right direction to do what you want - others here may have alternative suggestions.

Finally, a comment regarding your variable declarations

Code:
Dim R2, R3, R4, R5, R6, R7, R8, R9, R10, R11, R13, R14 As String

Only the last variable in the row is of Data Type String ALL the others are variants

When declaring variables in VBA you must explicitly declare each one with its required data type otherwise, they default to Variant.

Code:
Dim R2 As String, R3 As String, R4 As String, R5 As String etc etc

Hope Helpful

Dave
 

andrewmckat

New Member
Joined
Jun 14, 2020
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
t
Hi,

Found a few moments to have a look at your project - As you have set it up so that you can click on the client in the listbox and the information will be shown textboxes it occurs to me that all you need do is to search your listbox & select the required record.

Try updated Search code below

Code:
Private Sub CmdSearch_Click()
    Dim i As Long
    Dim Search As String
   
    Search = Me.TxtSearch.Text
   
    With Me.ClientList
        If Len(Search) > 0 Then
            For i = 0 To .ListCount - 1
                If Format(.List(i, 11), "000") = Search Then
                    .TopIndex = i
                    .ListIndex = i
                    Exit Sub
                End If
            Next i
            MsgBox Search & Chr(10) & "Customer Number Not Found", 48, "Not Found"
        Else
            .ListIndex = -1
            .TopIndex = 0
        End If
    End With
End Sub

Note I assume that you are searching Column 11 (columns start at 0 in listbox) for a three-digit numeric value – In this case, I have applied format to ensure leading zero (0) is not omitted from List values being tested against entered search value. e.g. 095

For above idea to work very likely that your UserForm_Initialize event will need some updating

Replace existing code with following

Code:
Private Sub UserForm_Initialize()
    Dim wsClientList As Worksheet
    Dim lastrow As Long, lastcolumn As Long
   
    Set wsClientList = ThisWorkbook.Worksheets("Client List")
   
    If wsClientList.ListObjects("ClientList") <> ClientList Then
        MsgBox "Table to be Named ' ClientList '! on 'Client List' Sheet", vbExclamation
    End If
   
    With wsClientList.Range("A1").CurrentRegion
        lastrow = .Rows.Count - 1
        lastcolumn = .Columns.Count
    End With
   
   
    Call SortByForename
   
    With Me.ClientList
        .ColumnCount = lastcolumn
        .ColumnHeads = True
        .RowSource = "'" & wsClientList.Name & "'!" & wsClientList.Cells(2, 1).Resize(lastrow, lastcolumn).Address
    End With
   
   
    Label27.Caption = Format(Date, "ddd d mmm yyyy")
   
    ComboBox1.AddItem ("")
    ComboBox1.AddItem ("Active")
    ComboBox1.AddItem ("Archived")
    ComboBox1.AddItem ("Suspended")
End Sub

Updates not fully tested but hopefully enough for you to work with & goes in right direction to do what you want - others here may have alternative suggestions.

Finally, a comment regarding your variable declarations

Code:
Dim R2, R3, R4, R5, R6, R7, R8, R9, R10, R11, R13, R14 As String

Only the last variable in the row is of Data Type String ALL the others are variants

When declaring variables in VBA you must explicitly declare each one with its required data type otherwise, they default to Variant.

Code:
Dim R2 As String, R3 As String, R4 As String, R5 As String etc etc

Hope Helpful

Dave

Thank you. this works the way that I wanted it to.

now that I see it it makes sence.

Andy
 

Watch MrExcel Video

Forum statistics

Threads
1,114,543
Messages
5,548,640
Members
410,862
Latest member
uskudar
Top