VBA - Update record in table and corresponding worksheet

[Lee Rabbit]

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.

Private Sub UserForm_Initialize()

ComboBox1.SetFocus

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

    With ComboBox1
        .Clear
        .AddItem
                
    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.

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.

Regards,
Lee

 

[Lee Rabbit]

I have resolved this issue now