Userform textbox value to row where a search string was found

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
Good day

Some help required again…

I have a userform where I input a number and search for that number. Once found the info on the userform textboxes will be populated with values from cells on the worksheet.

E.G. I search for 5555 (numbers vary) and info is displayed on userform in textboxes.

Rich (BB code):
TextBox1.Value = B4
TextBox2.Value = C4
TextBox3.Value = D4
TextBox4.Value = E4
TextBox5.Value = F4
TextBox6.Value = G4
TextBox7.Value = H4
TextBox8.Value = I4
'Note that info will not come from row 4 all the time. In this instance my search string of 5555 appears in row 4.

Textboxes are numbered Textbox1 to Textbox88 and info starts from Column B Row 3 and runs to Column CK.

Once this info is displayed I want to be able to change the values in the specific row where info was pulled from with the current textbox values… so let’s say I want to update the 5555 to 5566 then the entire row should be updated also. Info is updated with a command button.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
How are you finding the correct row?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
Hi,
as a suggestion, something like following to return record to your Form maybe

VBA Code:
Private Sub CommandButton1_Click()
    Dim FoundCell As Range
    Dim Search As String
    Dim SearchColumn As Variant
    Dim c As Integer
    Dim ws As Worksheet
    
    Set ws = Worksheets("Sheet1")
    
    Search = Me.TextBox1.Value
    
    SearchColumn = "A"
    
    Set FoundCell = ws.Columns(SearchColumn).Find(Search, LookIn:=xlValues, lookat:=xlWhole)
    If Not FoundCell Is Nothing Then
        
        For c = 1 To 8
            
            Me.Controls("TextBox" & c).Value = FoundCell.Offset(, c - 1).Text
            Next c
            
            
        Else
            
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
        
End Sub

Dave
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
How are you finding the correct row?

Hi Fluff

Code below... I realise it is messy but was done a few years ago and I am trying to clean up the code

VBA Code:
Private Sub SrchEmp_Click()
Sheet1.Unprotect Password:="1234"
    Dim fnd As Range ' this will be the same as your entry in TextBox1
    Dim tbl As Range ' this will be the table to search
    Set tbl = Sheet1.Range("F3").CurrentRegion
     'run the search
    Set fnd = tbl.Find(What:=TextBox5.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)
     'if the item is not found the user is informed and the textbox cleared
    If fnd Is Nothing Then
        MsgBox "Employee not found on Database!", 16, "Message from Employee Update"
        TextBox5.Value = ""
        Exit Sub
        'if the item is found the details will be copied to the labels
    Else: fnd.Activate
    End If

    'now put the information stored adjacent to the found item into label1,etc
    TextBox1.Value = fnd.Offset(0, -4).Value
    TextBox2.Value = fnd.Offset(0, -3).Value
    TextBox3.Value = fnd.Offset(0, -2).Value
    TextBox4.Value = fnd.Offset(0, -1).Value
    TextBox5.Value = fnd.Offset(0, 0).Value
    TextBox6.Value = fnd.Offset(0, 1).Value
    TextBox7.Value = fnd.Offset(0, 2).Value
    TextBox8.Value = fnd.Offset(0, 3).Value
    TextBox9.Value = fnd.Offset(0, 4).Value
    TextBox10.Value = fnd.Offset(0, 5).Value
    TextBox11.Value = fnd.Offset(0, 6).Value
    TextBox12.Value = fnd.Offset(0, 7).Value
    TextBox13.Value = fnd.Offset(0, 8).Value
    TextBox14.Value = fnd.Offset(0, 9).Value
    TextBox15.Value = fnd.Offset(0, 10).Value
    TextBox16.Value = fnd.Offset(0, 11).Value
    TextBox17.Value = fnd.Offset(0, 12).Value
    TextBox18.Value = fnd.Offset(0, 13).Value
    TextBox19.Value = fnd.Offset(0, 14).Value
    TextBox20.Value = fnd.Offset(0, 15).Value
    TextBox21.Value = fnd.Offset(0, 16).Value
    TextBox22.Value = fnd.Offset(0, 17).Value
    TextBox23.Value = fnd.Offset(0, 18).Value
    TextBox24.Value = fnd.Offset(0, 19).Value
    TextBox25.Value = fnd.Offset(0, 20).Value
    TextBox26.Value = fnd.Offset(0, 21).Value
    TextBox27.Value = fnd.Offset(0, 22).Value
    TextBox28.Value = fnd.Offset(0, 23).Value
    TextBox29.Value = fnd.Offset(0, 24).Value
    TextBox30.Value = fnd.Offset(0, 25).Value
    TextBox31.Value = fnd.Offset(0, 26).Value
    TextBox32.Value = fnd.Offset(0, 27).Value
    TextBox33.Value = fnd.Offset(0, 28).Value
    TextBox34.Value = fnd.Offset(0, 29).Value
    TextBox35.Value = fnd.Offset(0, 30).Value
    TextBox36.Value = fnd.Offset(0, 31).Value
    TextBox37.Value = fnd.Offset(0, 32).Value
    TextBox38.Value = fnd.Offset(0, 33).Value
    TextBox39.Value = fnd.Offset(0, 34).Value
    TextBox40.Value = fnd.Offset(0, 35).Value
    TextBox41.Value = fnd.Offset(0, 36).Value
    TextBox42.Value = fnd.Offset(0, 37).Value
    TextBox43.Value = fnd.Offset(0, 38).Value
    TextBox44.Value = fnd.Offset(0, 39).Value
    TextBox45.Value = fnd.Offset(0, 40).Value
    TextBox46.Value = fnd.Offset(0, 41).Value
    TextBox47.Value = fnd.Offset(0, 42).Value
    TextBox48.Value = fnd.Offset(0, 43).Value
    TextBox49.Value = fnd.Offset(0, 44).Value
    TextBox50.Value = fnd.Offset(0, 45).Value
    TextBox51.Value = fnd.Offset(0, 46).Value
    TextBox52.Value = fnd.Offset(0, 47).Value
    TextBox53.Value = fnd.Offset(0, 48).Value
    TextBox54.Value = fnd.Offset(0, 49).Value
    TextBox55.Value = fnd.Offset(0, 50).Value
    TextBox56.Value = fnd.Offset(0, 51).Value
    TextBox57.Value = fnd.Offset(0, 52).Value
    TextBox58.Value = fnd.Offset(0, 53).Value
    TextBox59.Value = fnd.Offset(0, 54).Value
    TextBox60.Value = fnd.Offset(0, 55).Value
    TextBox61.Value = fnd.Offset(0, 56).Value
    TextBox62.Value = fnd.Offset(0, 57).Value
    TextBox63.Value = fnd.Offset(0, 58).Value
    TextBox64.Value = fnd.Offset(0, 59).Value
    TextBox65.Value = fnd.Offset(0, 60).Value
    TextBox66.Value = fnd.Offset(0, 61).Value
    TextBox67.Value = fnd.Offset(0, 62).Value
    TextBox68.Value = fnd.Offset(0, 63).Value
    TextBox69.Value = fnd.Offset(0, 64).Value
    TextBox70.Value = fnd.Offset(0, 65).Value
    TextBox71.Value = fnd.Offset(0, 66).Value
    TextBox72.Value = fnd.Offset(0, 67).Value
    TextBox73.Value = fnd.Offset(0, 68).Value
    TextBox74.Value = fnd.Offset(0, 69).Value
    TextBox75.Value = fnd.Offset(0, 70).Value
    TextBox76.Value = fnd.Offset(0, 71).Value
    TextBox77.Value = fnd.Offset(0, 72).Value
    TextBox78.Value = fnd.Offset(0, 73).Value
    TextBox79.Value = fnd.Offset(0, 74).Value
    TextBox80.Value = fnd.Offset(0, 75).Value
    TextBox81.Value = fnd.Offset(0, 76).Value
    TextBox82.Value = fnd.Offset(0, 77).Value
    TextBox83.Value = fnd.Offset(0, 78).Value
    TextBox84.Value = fnd.Offset(0, 79).Value
    TextBox85.Value = fnd.Offset(0, 80).Value
    TextBox86.Value = fnd.Offset(0, 81).Value
    TextBox87.Value = fnd.Offset(0, 82).Value
    TextBox88.Value = fnd.Offset(0, 83).Value
    Sheet1.Protect Password:="1234"
    

    
    
  
    
End Sub
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123

ADVERTISEMENT

Hi,
as a suggestion, something like following to return record to your Form maybe

VBA Code:
Private Sub CommandButton1_Click()
    Dim FoundCell As Range
    Dim Search As String
    Dim SearchColumn As Variant
    Dim c As Integer
    Dim ws As Worksheet
   
    Set ws = Worksheets("Sheet1")
   
    Search = Me.TextBox1.Value
   
    SearchColumn = "A"
   
    Set FoundCell = ws.Columns(SearchColumn).Find(Search, LookIn:=xlValues, lookat:=xlWhole)
    If Not FoundCell Is Nothing Then
       
        For c = 1 To 8
           
            Me.Controls("TextBox" & c).Value = FoundCell.Offset(, c - 1).Text
            Next c
           
           
        Else
           
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
       
End Sub

Dave
Will try it quickly...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
You can replace all the textboxes with
VBA Code:
   Dim i As Long
   For i = 1 To 88
      Me.Controls("TextBox" & i).Value = Fnd.Offset(, i - 5).Value
   Next i
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123

ADVERTISEMENT

You can replace all the textboxes with
VBA Code:
   Dim i As Long
   For i = 1 To 88
      Me.Controls("TextBox" & i).Value = Fnd.Offset(, i - 5).Value
   Next i
That works perfect for pulling the info from sheet to the userform. Appreciated. Now as per my first question how would my code look like to pass the found values back to the row?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Remove this line from your code & place it at the top of the module, before any code.
And then put this in your "update" button.
VBA Code:
   If Fnd Is Nothing Then
      MsgBox "No found row"
      Exit Sub
   End If
   For i = 1 To 88
      Fnd.Offset(, i - 5).Value = Me.Controls("TextBox" & i).Value
   Next i
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
That works perfect for pulling the info from sheet to the userform. Appreciated. Now as per my first question how would my code look like to pass the found values back to the row?

Just modify the code to allow it to return the textbox values

VBA Code:
Private Sub CommandButton1_Click()
    Dim FoundCell As Range
    Dim Search As String
    Dim SearchColumn As Variant
    Dim c As Integer
    Dim ws As Worksheet
   
    Set ws = Worksheets("Sheet1")
   
    Search = Me.TextBox1.Value
   
    SearchColumn = "A"
   
    Set FoundCell = ws.Columns(SearchColumn).Find(Search, LookIn:=xlValues, lookat:=xlWhole)
    If Not FoundCell Is Nothing Then
       
        With Me.CommandButton1
           
            For c = 1 To 8
                If .Caption = "Update" Then
                    FoundCell.Offset(, c - 1).Value = Me.Controls("TextBox" & c).Value
                Else
                    Me.Controls("TextBox" & c).Value = FoundCell.Offset(, c - 1).Text
                End If
                Next c
               
                .Caption = IIf(.Caption = "Update", "Find", "Update")
            End With
        Else
           
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
       
End Sub

This changes the button caption to allow code to toggle between getting data from worksheet & returning it.
Solution assumes that search value will not be changed

Dave
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
Remove this line from your code & place it at the top of the module, before any code.
And then put this in your "update" button.
VBA Code:
   If Fnd Is Nothing Then
      MsgBox "No found row"
      Exit Sub
   End If
   For i = 1 To 88
      Fnd.Offset(, i - 5).Value = Me.Controls("TextBox" & i).Value
   Next i
Gives me variable not defined for Fnd. i placed this code in my update button and the search code you provided went to my search button.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,573
Messages
5,637,135
Members
416,959
Latest member
Mohzein

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
Top