Userform To Worksheet Transfer

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top