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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Whilst I said to move a line of code I forgot to show which line :oops:
This line
VBA Code:
Dim fnd As Range ' this will be the same as your entry in TextBox1
needs to be removed from the search sub & placed at the very top of the module, before any code.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
Whilst I said to move a line of code I forgot to show which line :oops:
This line
VBA Code:
Dim fnd As Range ' this will be the same as your entry in TextBox1
needs to be removed from the search sub & placed at the very top of the module, before any code.

Hi Fluff.. done and search works... however on the update side gives me variable not defined on line

VBA Code:
   For i = 1 To 88

Not sure at this stage but if I am correct (note I am trying to learn myself...lol) I should

VBA Code:
Dim i as Integer

??
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
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

Hi Dave

Thanks...this works as well for search and return value to form... I have utilised this code in one of my other forms 🍻
 

dmt32

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

Thanks...this works as well for search and return value to form... I have utilised this code in one of my other forms 🍻

do be aware that the search value itself should not be amended - If this is required, some adjustment to code will be required.

Many thanks for feedback - glad we were able to assist you

Dave
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123

ADVERTISEMENT

Just an update

I did following code change:

VBA Code:
Dim i as Long

And all works for searching, updating...

But now something somewhere as thrown out my add employee userform code which is as follows:

VBA Code:
Private Sub AddBx_Click()
    Dim NewRow As Integer
    Dim ctrl As Control
    Dim i As Integer, j As Integer
    Application.ScreenUpdating = False
    NewRow = Range("B" & Rows.Count).End(xlUp).Row + 1
    If Len(AddEmp.TextBox1.value) = 0 Then
        MsgBox "No Empty fields are allowed for Employee Personal Details! Please enter all relevant information.", vbOKCancel + vbCritical, "Message from Add Employee"
        AddEmp.TextBox1.SetFocus
        Me.TextBox1.BackColor = vbRed
        Exit Sub
    End If
    If Len(AddEmp.TextBox2.value) = 0 Then
        MsgBox "No Empty fields are allowed for Employee Personal Details! Please enter all relevant information.", vbOKCancel + vbCritical, "Message from Add Employee"
        AddEmp.TextBox2.SetFocus
        Me.TextBox2.BackColor = vbRed
        Exit Sub
    End If
    If Len(AddEmp.TextBox3.value) = 0 Then
        MsgBox "No Empty fields are allowed for Employee Personal Details! Please enter all relevant information.", vbOKCancel + vbCritical, "Message from Add Employee"
        AddEmp.TextBox3.SetFocus
        Me.TextBox3.BackColor = vbRed
        Exit Sub
    End If
    If Len(AddEmp.TextBox4.value) = 0 Then
        MsgBox "No Empty fields are allowed for Employee Personal Details! Please enter all relevant information.", vbOKCancel + vbCritical, "Message from Add Employee"
        AddEmp.TextBox4.SetFocus
        Me.TextBox4.BackColor = vbRed
        Exit Sub
    End If
    If Len(AddEmp.TextBox5.value) = 0 Then
        MsgBox "No Empty fields are allowed for Employee Personal Details! Please enter all relevant information.", vbOKCancel + vbCritical, "Message from Add Employee"
        AddEmp.TextBox5.SetFocus
        Me.TextBox5.BackColor = vbRed
        Exit Sub
    End If
    If Len(AddEmp.TextBox6.value) = 0 Then
        MsgBox "No Empty fields are allowed for Employee Personal Details! Please enter all relevant information.", vbOKCancel + vbCritical, "Message from Add Employee"
        AddEmp.TextBox6.SetFocus
        Me.TextBox6.BackColor = vbRed
        Exit Sub
    End If
    If Len(AddEmp.TextBox7.value) = 0 Then
        MsgBox "No Empty fields are allowed for Employee Personal Details! Please enter all relevant information.", vbOKCancel + vbCritical, ""
        AddEmp.TextBox7.SetFocus
        Me.TextBox7.BackColor = vbRed
        Exit Sub
    End If
    If Len(AddEmp.TextBox8.value) = 0 Then
        MsgBox "No Empty fields are allowed for Employee Personal Details! Please enter all relevant information.", vbOKCancel + vbCritical, ""
        AddEmp.TextBox8.SetFocus
        Me.TextBox8.BackColor = vbRed
        Exit Sub
    End If
    On Error GoTo errHandler
    For j = 1 To 5 'Row
        For i = 1 To 89 'Column
            Cells(NewRow + 2, i + 1).value = Me.Controls("TextBox" & (NewRow - 1) * 12 + i).value 'This code was taken from another sheet of mine and I modified it to work on this one. All was working fine until I did changes for searching and updating.
        Next
    Next
    errHandler:
    Debug.Print i
    Debug.Print j
    Range("B3:CK152").Select
    ActiveWindow.SmallScroll Down:=-147
    ActiveWorkbook.Worksheets("Namelist").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Namelist").Sort.SortFields.Add Key:=Range("B3"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Namelist").Sort
        .SetRange Range("B3:CK152")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then ctrl.value = ""
        Next ctrl
        Application.ScreenUpdating = True
    End Sub



Which basically will pass values from textboxes into the next empty row starting in Col B seeing as Col A has predefined values. Values in textboxes are inserted manually and not found with any searching seeing as it is a new record added to database.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Your for j loop is doing nothing, other than wasting time, so get rid of it & change the for i loop to
VBA Code:
        For i = 1 To 89 'Column
            Cells(NewRow + 2, i + 1).value = Me.Controls("TextBox" &  i).value 
        Next
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123

ADVERTISEMENT

Your for j loop is doing nothing, other than wasting time, so get rid of it & change the for i loop to
VBA Code:
        For i = 1 To 89 'Column
            Cells(NewRow + 2, i + 1).value = Me.Controls("TextBox" &  i).value
        Next
Awesomeness...working now... knew it was something simple.. sometimes a fresh pair of eyes helps to correct code. Thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Jimmypop

Board Regular
Joined
Sep 12, 2013
Messages
123
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

Why would this code not pull info correct... I did for testing purposes on sheet insert values 1 to 88 in my row... but it pulls info to userform only from 2 to 88 and leaves the last textbox blank which should be 88 and it inserts 2 in textbox 1 where the value 1 should be...

VBA Code:
   Dim i As Long
   For i = 1 To 88
      Me.Controls("TextBox" & i).Value = Fnd.Offset(, i - 5).Value
   Next i
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
I've no idea :unsure:

Which code are you talking about & where did you put the values?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,573
Messages
5,637,131
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