VBA Code - Update data using VBA Form

[glennjohnrobertson]

I have a spreadhseet. On Sheet1 I have rows of Data. Column A is called Log ID. The LogID field just updates each row with a number starting from 1 then increasing numerically.
I also have a UserForm that allows the user to complete the form and the post then data into a new row. I also have a field on the form called Log ID, when a number is put in the field and search button pressed on the UserForm it displays all the fields from that row in the relevant fields on the userform.
What i can't work out is the code to do the following.
Once the row data has been brought back into the form I want the user to be able to amend that data in the form and then click a button that will amend the data in that row rather then create a new row.
Sorry im pretty new to VBA and have been trying for two days to work this out!!

 

[dmt32]

Hi welcome to forum

If you have code that finds the required record to populate the form then should be straight forward to modify it to return it back to correct row - it would be helpful if you publish the forms code - plenty here to help you modify it to meet your requirement.


Dave

 

[glennjohnrobertson]

Thanks for replying.
When my userform is loaded in user enters a number in the field called txtLogID, when they click search it finds the row with that LogID in column A and then populates the form with that data.
My code is below, sorry if its a bit dirty but im new to VBA

Private Sub CommandButton3_Click()
Dim log_id As String
log_id = Trim(txtLogID.Text)
lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To lastrow
If Worksheets("Sheet1").Cells(i, 1).Value = log_id Then
cboName.Text = Worksheets("Sheet1").Cells(i, 2).Value
cboEmployeeName.Text = Worksheets("Sheet1").Cells(i, 3).Value
txtLocation.Text = Worksheets("Sheet1").Cells(i, 6).Value
cboAlarmType.Text = Worksheets("Sheet1").Cells(i, 7).Value
txtAlarmRecieved.Text = Worksheets("Sheet1").Cells(i, 8).Value
txtTimeOrbisCalled.Text = Worksheets("Sheet1").Cells(i, 9).Value
txtComments.Text = Worksheets("Sheet1").Cells(i, 12).Value
End If

Next

End Sub

 

[dmt32]

Hi,
sorry for slow reply, I am on granddad duty today & have little spare time

Try this change to your code & see if helps you

Place ALL codes in your userforms code page

Dim ws As Worksheet
Private Sub CommandButton3_Click()
    Dim log_id      As String
    Dim FoundCell   As Range
    Dim i           As Integer
    Dim RecordRow   As Long
    Dim Ctrl        As Variant
   
    log_id = Trim(txtLogID.Text)
   
'find record
    Set FoundCell = ws.Columns(1).Find(log_id, lookat:=xlWhole, LookIn:=xlValues)
    If Not FoundCell Is Nothing Then
       
        RecordRow = FoundCell.Row
       
        For Each Ctrl In Array(cboName, cboEmployeeName, txtLocation, cboAlarmType, _
            txtAlarmRecieved, txtTimeOrbisCalled, txtComments)
            i = i + 1
            With Ctrl
                If Me.Tag = "Update" Then
'return record
                    ws.Cells(RecordRow, Choose(i, 2, 3, 6, 7, 8, 9, 12)).Value = .Text
                Else
'get record
                    .Text = ws.Cells(RecordRow, Choose(i, 2, 3, 6, 7, 8, 9, 12)).Value
                End If
            End With
        Next
       
        If Me.Tag = "Update" Then
'inform user
            MsgBox log_id & Chr(10) & "Record Updated", 48, "Record Updated"
            ChangeButton False
            Me.Tag = ""
        Else
            Me.Tag = "Update"
            ChangeButton True
        End If
       
    Else
   
        MsgBox log_id & Chr(10) & "Record Not Found", 48, "Not Found"

    End If
   
End Sub

Sub ChangeButton(ByVal State As Boolean)
    With Me.CommandButton3
        If State Then .Tag = .Caption Else RecordRow = 0
        .BackColor = IIf(State, &HFF&, &H8000000F)
        .ForeColor = IIf(State, &HFFFFFF, &H80000012)
        .Font.Bold = State
        .Font.Size = 10
        .Caption = IIf(State, "Update", .Tag)
    End With
End Sub
Private Sub UserForm_Initialize()
Set ws = ThisWorkbook.Worksheets("Sheet1")
End Sub

Note the declared variable ws shown in BOLD - this must sit at TOP of your forms code page OUTSIDE any procedure

Solution not fully tested but press commandbutton3 to search for a record - If found, the button changes to RED & Caption shows "Update"
Make your changes & Pressing button again should update the ranges & msgbox inform you. The commandbutton should re-set.

Hope Helpful

Dave