VBA Code - Update data using VBA Form

glennjohnrobertson

New Member
Joined
Sep 10, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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!!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,374
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Sep 10, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,374
Office Version
  1. 2019
Platform
  1. Windows
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

Rich (BB code):
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,695
Members
414,088
Latest member
rodriboraun

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