Textbox values replace in specific cells

[Bijan2048]

Hi expets,
I can find an Id_number and get other fields via Vlookup in Textbox1,
The VBA code in Search Button is :
Me.TextBox2.Value = Application.VLookup(Val(Me.TextBox1.Value), Sheets("source1").Range("database"), 4, 0)
Me.TextBox3.Value = Application.VLookup(Val(Me.TextBox1.Value), Sheets("source1").Range("database"), 5, 0)
Me.TextBox4.Value = Application.VLookup(Val(Me.TextBox1.Value), Sheets("source1").Range("database"), 6, 0)
How can I replace whatever user interred in texbox2-4 in thier cells in Save button?
Thanks

 

[LongToast]

To do that, first you must know from what row the data came from, example from your picture, id number 10999909, it came from what row? Once you know it, you can write updated data back to that row.

 

[Bijan2048]

To do that, first you must know from what row the data came from, example from your picture, id number 10999909, it came from what row? Once you know it, you can write updated data back to that row.

Thanks for response,
It would be appropriate If you know how to do that(find row number and update)
Thanks in advance

 

[LongToast]

Actually you can get the row number when you populated search data, but since you use vlookup there is no way you can do that, i talk about "1 shot for 2 birds", when you populated search data, at the same time you can get row number too if you use looping instead, change your code in userform like this :

I assume :
1. Your data always start at A6.
2. You have 6 fields (id number, BB, CC, ref1, ref2 and title).
3. User can only update 3 fields (ref1, ref2 and title).

Option Explicit

Dim oWs As Worksheet
Dim arrData As Variant
Dim bFound As Boolean
Dim iLast, iRow As Long

Private Sub CommandButton1_Click() 'THIS IS SAVE BUTTON
    If bFound Then
        Dim arrDataUpdate As Variant
        
        arrDataUpdate = Array(Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value)
        oWs.Range("D" & iRow).Resize(1, 3).Value = arrDataUpdate
        
        MsgBox "Data updated.", vbOKOnly + vbInformation
    End If
End Sub

Private Sub ClearFields()
    Me.TextBox2.Value = vbNullString 'THIS IS TEXTBOX FOR REF1
    Me.TextBox3.Value = vbNullString 'THIS IS TEXTBOX FOR REF2
    Me.TextBox4.Value = vbNullString 'THIS IS TEXTBOX FOR TITLE
End Sub

Private Sub TextBox1_Change()
    If Application.WorksheetFunction.Trim(Me.TextBox1.Value) <> vbNullString And IsArray(arrData) Then
        Dim i As Long
        
        iRow = 5
        
        For i = LBound(arrData) To UBound(arrData)
            If CStr(arrData(i, 1)) = CStr(Me.TextBox1.Value) Then 'WE COMPARE DATA BETWEEN DATA IN ARRAY WITH ID NUMBER'S TEXTBOX
                Me.TextBox2.Value = arrData(i, 4) 'THIS IS TEXTBOX FOR REF1
                Me.TextBox3.Value = arrData(i, 5) 'THIS IS TEXTBOX FOR REF2
                Me.TextBox4.Value = arrData(i, 6) 'THIS IS TEXTBOX FOR TITLE
                
                iRow = iRow + i
                
                bFound = True
                Exit For
            End If
        Next i
    End If
    
    If Not bFound Then ClearFields
End Sub

Private Sub UserForm_Initialize()
    Set oWs = ThisWorkbook.Worksheets("Sheet1") 'CHANGE THIS TO YOUR SHEET NAME
    
    iLast = oWs.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    If iLast > 5 Then arrData = oWs.Range("A6:F" & iLast).Value
End Sub

 

[Bijan2048]

Actually you can get the row number when you populated search data, but since you use vlookup there is no way you can do that, i talk about "1 shot for 2 birds", when you populated search data, at the same time you can get row number too if you use looping instead, change your code in userform like this :

I assume :
1. Your data always start at A6.
2. You have 6 fields (id number, BB, CC, ref1, ref2 and title).
3. User can only update 3 fields (ref1, ref2 and title).

Option Explicit

Dim oWs As Worksheet
Dim arrData As Variant
Dim bFound As Boolean
Dim iLast, iRow As Long

Private Sub CommandButton1_Click() 'THIS IS SAVE BUTTON
    If bFound Then
        Dim arrDataUpdate As Variant
      
        arrDataUpdate = Array(Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value)
        oWs.Range("D" & iRow).Resize(1, 3).Value = arrDataUpdate
      
        MsgBox "Data updated.", vbOKOnly + vbInformation
    End If
End Sub

Private Sub ClearFields()
    Me.TextBox2.Value = vbNullString 'THIS IS TEXTBOX FOR REF1
    Me.TextBox3.Value = vbNullString 'THIS IS TEXTBOX FOR REF2
    Me.TextBox4.Value = vbNullString 'THIS IS TEXTBOX FOR TITLE
End Sub

Private Sub TextBox1_Change()
    If Application.WorksheetFunction.Trim(Me.TextBox1.Value) <> vbNullString And IsArray(arrData) Then
        Dim i As Long
      
        iRow = 5
      
        For i = LBound(arrData) To UBound(arrData)
            If CStr(arrData(i, 1)) = CStr(Me.TextBox1.Value) Then 'WE COMPARE DATA BETWEEN DATA IN ARRAY WITH ID NUMBER'S TEXTBOX
                Me.TextBox2.Value = arrData(i, 4) 'THIS IS TEXTBOX FOR REF1
                Me.TextBox3.Value = arrData(i, 5) 'THIS IS TEXTBOX FOR REF2
                Me.TextBox4.Value = arrData(i, 6) 'THIS IS TEXTBOX FOR TITLE
              
                iRow = iRow + i
              
                bFound = True
                Exit For
            End If
        Next i
    End If
  
    If Not bFound Then ClearFields
End Sub

Private Sub UserForm_Initialize()
    Set oWs = ThisWorkbook.Worksheets("Sheet1") 'CHANGE THIS TO YOUR SHEET NAME
  
    iLast = oWs.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
    If iLast > 5 Then arrData = oWs.Range("A6:F" & iLast).Value
End Sub

Actually you can get the row number when you populated search data, but since you use vlookup there is no way you can do that, i talk about "1 shot for 2 birds", when you populated search data, at the same time you can get row number too if you use looping instead, change your code in userform like this :

I assume :
1. Your data always start at A6.
2. You have 6 fields (id number, BB, CC, ref1, ref2 and title).
3. User can only update 3 fields (ref1, ref2 and title).

Option Explicit

Dim oWs As Worksheet
Dim arrData As Variant
Dim bFound As Boolean
Dim iLast, iRow As Long

Private Sub CommandButton1_Click() 'THIS IS SAVE BUTTON
    If bFound Then
        Dim arrDataUpdate As Variant
       
        arrDataUpdate = Array(Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value)
        oWs.Range("D" & iRow).Resize(1, 3).Value = arrDataUpdate
       
        MsgBox "Data updated.", vbOKOnly + vbInformation
    End If
End Sub

Private Sub ClearFields()
    Me.TextBox2.Value = vbNullString 'THIS IS TEXTBOX FOR REF1
    Me.TextBox3.Value = vbNullString 'THIS IS TEXTBOX FOR REF2
    Me.TextBox4.Value = vbNullString 'THIS IS TEXTBOX FOR TITLE
End Sub

Private Sub TextBox1_Change()
    If Application.WorksheetFunction.Trim(Me.TextBox1.Value) <> vbNullString And IsArray(arrData) Then
        Dim i As Long
       
        iRow = 5
       
        For i = LBound(arrData) To UBound(arrData)
            If CStr(arrData(i, 1)) = CStr(Me.TextBox1.Value) Then 'WE COMPARE DATA BETWEEN DATA IN ARRAY WITH ID NUMBER'S TEXTBOX
                Me.TextBox2.Value = arrData(i, 4) 'THIS IS TEXTBOX FOR REF1
                Me.TextBox3.Value = arrData(i, 5) 'THIS IS TEXTBOX FOR REF2
                Me.TextBox4.Value = arrData(i, 6) 'THIS IS TEXTBOX FOR TITLE
               
                iRow = iRow + i
               
                bFound = True
                Exit For
            End If
        Next i
    End If
   
    If Not bFound Then ClearFields
End Sub

Private Sub UserForm_Initialize()
    Set oWs = ThisWorkbook.Worksheets("Sheet1") 'CHANGE THIS TO YOUR SHEET NAME
   
    iLast = oWs.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    If iLast > 5 Then arrData = oWs.Range("A6:F" & iLast).Value
End Sub

Thanks a lot and sorry for late response,
I copy & paste all events but save event works without any update or even error
I guess something wrong with array because
I don't get Msgbox.
I check it several times but my knowledge is not enough to solve it.
Please if it possible for you recheck it again.
Thank you so much

 

[Bijan2048]

Thanks a lot,
VBA codes works great, I got my Problem.