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: 65
  • Screenshot (2).png
    Screenshot (2).png
    138.1 KB · Views: 63
  • Screenshot (3).png
    Screenshot (3).png
    144.4 KB · Views: 65

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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 +
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top