VBA - Update record in table and corresponding worksheet

Lee Rabbit

New Member
Apr 30, 2020
Office Version
  1. 2010
  1. Windows
Hello & Good Day,

OK, so here is where I am stuck again.

I have a UserForm that adds records and creates a worksheet (from template sheet) based on the input values (Huge thanks to Fluff for simplifying this task for me)

I have now created an almost identical update UserForm and used the following VBA to draw data from table into the UserForm. In the add user UserForm the name was inputted into a TextBox. That has now been replaced with a ComboBox in the update UserForm.

VBA Code:
Private Sub UserForm_Initialize()


Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DATA ENTRY")

    With ComboBox1
    Me.ComboBox1.List = sh.Range("Driver[NAME]").Value
End With

End Sub

I am having problems trying to update the data table and the worksheet at the same time. I always try to search the forum and internet for solutions but this one is giving me a headache and my wife is fed up of me shouting explicit every time I run the code and it fails.

This is the code that inputs the data into the data table and creates the worksheet. What I need to do is update the table and array fields to the corresponding worksheet when update is executed.

VBA Code:
Private Sub CommandButton1_Click()
   Dim i As Long, n As Long
   Dim sh As Worksheet, Nws As Worksheet
   Dim Ary As Variant
   Set sh = ThisWorkbook.Sheets("DATA ENTRY")
   Ary = Array("B4", "B5", "B6", "B7", "B8", "K4", "K5", "D26", "D27", "D28", "J2")  'Array B4 does not need to update as this is set in the ComboBox
   For i = 1 To 11  ' I believe that this will now become 2 To 11 as I have replaced TextBox1 with ComboBox1

      If Me.Controls("TextBox" & i).Value = "" Then
         MsgBox "PLEASE COMPLETE ALL DATA FIELDS", vbCritical
         Exit Sub
      End If
   Next i
' At this point I am aware that I am not creating a copy of a template 
' but need to locate the existing worksheet based on the value in ComboBox1
' to update the table and the corresponding worksheet   

   Sheets("Template").Copy , Sheets(Sheets.Count)
   Set Nws = ActiveSheet
   Nws.NAME = UCase(Me.TextBox1.Value)
   n = sh.Range("B" & Rows.Count).End(xlUp).Row + 1
   For i = 1 To 10 ' Similar to above this would now be 2 To 10

        sh.Range("A" & n).Offset(, i).Value = UCase(Me.Controls("Textbox" & i).Value)
        Nws.Range(Ary(i - 1)).Value = UCase(Me.Controls("Textbox" & i).Value)
        Me.Controls("Textbox" & i) = ""
   Next i
        sh.Range("A" & n).Offset(, i).Value = LCase(Me.Controls("Textbox" & i).Value)
        Nws.Range(Ary(i - 1)).Value = LCase(Me.Controls("Textbox" & i).Value)
        Me.Controls("Textbox" & i) = ""
   MsgBox "NEW DRIVER HAS BEEN ADDED", vbInformation  ' This would now display DRIVER DETAILS HAVE BEEN UPDATED
End Sub

As I am still learning, I would be very grateful for your help and many thanks in advance.


Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Latest member