Userform To Worksheet Transfer

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,636
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,083
Office Version
  1. 2019
Platform
  1. Windows
Hi,
untested but try following update to your code

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

Well-known Member
Joined
Nov 16, 2010
Messages
3,636
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
        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

Well-known Member
Joined
Jul 3, 2012
Messages
6,083
Office Version
  1. 2019
Platform
  1. Windows
what you suggest looks ok

Rich (BB 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
    
    MsgBox "LOCKSMITH SHEET UPDATED", vbInformation, "LOCKSMITH INFO SHEET"
    Me.TextBox1.SetFocus
    
End Sub

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,656
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top