Lee Rabbit
New Member
- Joined
- Apr 30, 2020
- Messages
- 43
- Office Version
- 2010
- Platform
- Windows
Good Day,
I am still learning but yet again, I have run into a brick wall.
I have created a VBA data entry form that is working perfectly to add the desired data into my table. The next step of my project is to take that data and create a copy of a template sheet, rename it based on a cell value in the table and insert the captured data into specific cells in the newly created worksheet.
Here is the VBA
I have tried many attempts but I keep running into errors.
To anyone who can help, a big thank you in advance.
Regards,
Lee
I am still learning but yet again, I have run into a brick wall.
I have created a VBA data entry form that is working perfectly to add the desired data into my table. The next step of my project is to take that data and create a copy of a template sheet, rename it based on a cell value in the table and insert the captured data into specific cells in the newly created worksheet.
Here is the VBA
VBA Code:
Private Sub CommandButton1_Click()
If Me.TextBox1.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
If Me.TextBox2.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
If Me.TextBox3.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
If Me.TextBox4.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
If Me.TextBox5.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
If Me.TextBox6.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
If Me.TextBox7.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
If Me.TextBox8.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
If Me.TextBox9.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
If Me.TextBox10.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
If Me.TextBox11.Value = "" Then
MsgBox "PLEASE COMPLETE ALL SECTIONS", vbCritical
Exit Sub
End If
'CHECK FOR DUPLICATE NAME
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DATA ENTRY")
Dim n As Long
If Application.WorksheetFunction.CountIf(sh.Range("B:B"), Me.TextBox1.Value) > 0 Then
MsgBox "THIS NAME ALREADY EXISTS IN THE DATABASE - PLEASE CHOOSE ANOTHER NAME!", vbCritical
Exit Sub
End If
n = sh.Range("B" & Application.Rows.Count).End(xlUp).Row
sh.Range("B" & n + 1).Value = Me.TextBox1.Value 'This is DRIVER NAME and will be the name of the copied TEMPLATE
sh.Range("C" & n + 1).Value = Me.TextBox2.Value
sh.Range("D" & n + 1).Value = Me.TextBox3.Value
sh.Range("E" & n + 1).Value = Me.TextBox4.Value
sh.Range("F" & n + 1).Value = Me.TextBox5.Value
sh.Range("G" & n + 1).Value = Me.TextBox6.Value
sh.Range("H" & n + 1).Value = Me.TextBox7.Value
sh.Range("I" & n + 1).Value = Me.TextBox8.Value
sh.Range("J" & n + 1).Value = Me.TextBox9.Value
sh.Range("K" & n + 1).Value = Me.TextBox10.Value
sh.Range("L" & n + 1).Value = Me.TextBox11.Value
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox4 = ""
Me.TextBox5 = ""
Me.TextBox6 = ""
Me.TextBox7 = ""
Me.TextBox8 = ""
Me.TextBox9 = ""
Me.TextBox10 = ""
Me.TextBox11 = ""
MsgBox "NEW DRIVER HAS BEEN ADDED", vbInformation
'At this point I want to take the inputted data and insert into defined cells on a "TEMPLATE" sheet
'To do this, I will need to:
'(1) Copy sheet named "TEMPLATE"
'(2) Rename the sheet with the cell value inserted as driver name (TextBox1)
'(3) Input recorded data from row to cells in new created sheet in this order:
'TextBox (1) =B4
'TextBox (2) =B5
'TextBox (3) =B6
'TextBox (4) =B7
'TextBox (5) =B8
'TextBox (6) =K4
'TextBox (7) =K5
'TextBox (8) =D26
'TextBox (9) =D27
'TextBox (10) =D28
'TextBox (11) =J2
I have tried many attempts but I keep running into errors.
To anyone who can help, a big thank you in advance.
Regards,
Lee