VBA - Update record in table and corresponding worksheet

Lee Rabbit

New Member
Joined
Apr 30, 2020
Messages
41
Office Version
  1. 2010
Platform
  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()

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.


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.

Regards,
Lee
 

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

Threads
1,118,121
Messages
5,570,309
Members
412,318
Latest member
angoeyuan
Top