Userform To Worksheet Transfer

[ipbr21054]

Morning,
I have a userform that opens when i open my worksheet.
I am having difficulties with the code where i press the CommandButton2 to then transfer the data ive just entered in the TextBoxes on my form to my work sheet.

Here is some info to help you.
The information needs to be placed after my last row that has values in it.

Userfome to worksheet once we know the row to insert it into

TEXTBOX1 - COLUMN B
TEXTBOX2 - COLUMN D
TEXTBOX3 - COLUMN F
TEXTBOX4 - COLUMN H
TEXTBOX5 - COLUMN J
TEXTBOX6 - COLUMN L

Columns C,E,G,I,K are hidden

Here is what i have so far of which will need looking at.

Private Sub CommandButton2_Click()Cancel = 0
If TextBox1.Text = "" Then
    Cancel = 1
    MsgBox "Name Not Entered", vbCritical, "LOCKSMITH INFO SHEET"
    TextBox1.SetFocus
    
ElseIf TextBox2.Text = "" Then
    Cancel = 1
    MsgBox "Company Name Not Entered", vbCritical, "LOCKSMITH INFO SHEET"
    TextBox2.SetFocus
    
ElseIf TextBox3.Text = "" Then
    Cancel = 1
    MsgBox "Telephone Number Not Entered", vbCritical, "LOCKSMITH INFO SHEET"
    TextBox3.SetFocus
    
ElseIf TextBox4.Text = "" Then
    Cancel = 1
    MsgBox "Post Code Not Entered", vbCritical, "LOCKSMITH INFO SHEET"
    TextBox4.SetFocus
    
ElseIf TextBox5.Text = "" Then
    Cancel = 1
    MsgBox "Area Not Entered", vbCritical, "LOCKSMITH INFO SHEET"
    TextBox5.SetFocus
    
ElseIf TextBox6.Text = "" Then
    Cancel = 1
    MsgBox "Country Not Entered", vbCritical, "LOCKSMITH INFO SHEET"
    TextBox6.SetFocus


End If


If Cancel = 1 Then
        Exit Sub
End If


Dim i As Long
Dim x As Long
Dim ctrl As Control
Dim LastRow As Long
LastRow = ThisWorkbook.Worksheets("LOCKSMITH").Cells(Rows.Count, 1).End(xlUp).Row
    


    
 With ThisWorkbook.Worksheets("LOCKSMITH")
    .Cells(LastRow + 1, 1).Value = TextBox1.Text: TextBox1.Value = ""
    .Cells(LastRow + 1, 2).Value = TextBox2.Text: TextBox2.Value = ""
    .Cells(LastRow + 1, 3).Value = TextBox3.Text: TextBox3.Value = ""
    .Cells(LastRow + 1, 4).Value = TextBox4.Text: TextBox4.Value = ""
    .Cells(LastRow + 1, 5).Value = TextBox5.Text: TextBox5.Value = ""
    .Cells(LastRow + 1, 6).Value = TextBox6.Text: TextBox6.Value = ""
End With
End Sub

Thanks

 

[dmt32]

Hi,
untested but try following update to your code

Private Sub CommandButton2_Click()
    Dim i As Integer
    Dim LastRow As Long
    Dim wsLOCKSMITH As Worksheet
    
    Set wsLOCKSMITH = ThisWorkbook.Worksheets("LOCKSMITH")
    
    For i = 1 To 6
        With Me.Controls("TextBox" & i)
        If .Text = "" Then
        MsgBox Choose(i, "Name", "Company Name", "Telephone Number", _
                                "Post Code", "Area", "Country") & _
                                " Not Entered", vbCritical, "LOCKSMITH INFO SHEET"
            .SetFocus
            Exit Sub
        End If
        End With
    Next i


    With wsLOCKSMITH
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
    End With
    
    For i = 1 To 6
        With Me.Controls("TextBox" & i)
        wsLOCKSMITH.Cells(LastRow, i * 2).Value = .Text
            .Text = ""
        End With
    Next i
    
End Sub

Dave

 

[ipbr21054]

Thanks,
That works fine.

I would like to now add a confirmation msg after the CommandButton2 is pressed & then once i press OK on the message box the focus then is on TextBox1.
I have added the following towards the end of your code which works but doesnt mean its correct,please can you advise.
Thanks.

        wsLOCKSMITH.Cells(LastRow, i * 2).Value = .Text            .Text = ""
        End With
    Next i
    MsgBox "LOCKSMITH SHEET UPDATED", vbInformation, "LOCKSMITH INFO SHEET"
    TextBox1.SetFocus
End Sub

 

[dmt32]

what you suggest looks ok

Private Sub CommandButton2_Click()
    Dim i As Integer
    Dim LastRow As Long
    Dim wsLOCKSMITH As Worksheet


    
    Set wsLOCKSMITH = ThisWorkbook.Worksheets("LOCKSMITH")
    
    For i = 1 To 6
    
        With Me.Controls("TextBox" & i)
        If .Text = "" Then
            MsgBox Choose(i, "Name", "Company Name", "Telephone Number", _
                                    "Post Code", "Area", "Country") & _
                                    " Not Entered", vbCritical, "LOCKSMITH INFO SHEET"
            .SetFocus
            Exit Sub
        End If
        End With
        
    Next i


    With wsLOCKSMITH
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
    End With
    
    For i = 1 To 6
        With Me.Controls("TextBox" & i)
            wsLOCKSMITH.Cells(LastRow, i * 2).Value = .Text
            .Text = ""
        End With
    Next i
    
    MsgBox "LOCKSMITH SHEET UPDATED", vbInformation, "LOCKSMITH INFO SHEET"
    Me.TextBox1.SetFocus
    
End Sub

Dave

 

[ipbr21054]

Thanks