Transfer data from userform to spread sheet in excel

[mmix803]

I am new to VBA and userforms. I cannot get my userform to transfer to my worksheet. This is what I have. I did little steps to see if it would go. It did not. Please help.

Private Sub submitcmd_Click()

Dim invoicedate As Date
Dim invoicenumber As Long

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Invoice")

Dim nextRow As String
nextRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

ws.Range("A" & nextRow) = invoicedatetxt.Value
ws.Range("B" & nextRow) = invoicenumbertxt.Value


End Sub

 

[Logit]

Paste the following INSIDE your userform :

Option Explicit

Sub submitcmd_Click()

Dim invoicedate As Date
Dim invoicenumber As Long

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Invoice")

Dim nextRow As String
nextRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

ws.Range("A" & nextRow) = invoicedatetxt.Value
ws.Range("B" & nextRow) = invoicenumbertxt.Value


End Sub

Private Sub CommandButton1_Click()
    submitcmd_Click
End Sub

In this version I inserted a CommandButton on the userform (UserForm1) and the button's name is CommandButton1.

In a regular module paste the following :

Option Explicit

Sub shwFrm()
    UserForm1.Show
End Sub

On the worksheet "Invoice" paste a CommandButton and attach it to this macro (shwFrm).

 

[mmix803]

I have a submit button on my form already. When I hit the submit it does nothing. My form go automatically to my sheet .I even put option explicit on top and it still does not work. Thank you for your suggestion.

 

[mmix803]

I did put that command button on and it worked, but I do not want a command button on my spreadsheet. This is a simple form and I want to add to it. I just want to be able to use my submit button on my userform. Sorry if I am being a pain.

 

[abdelfattah]

but I do not want a command button on my spreadsheet.

so how open the form ?!!

I just want to be able to use my submit button on my userform

what's the difference command button and submit button on the form?
do you mean command button name is submit or what?

 

[mmix803]

I have a command button on my main form to open the form and it opens on my spread sheet so I may see both. I want to transfer my data from my userform to my worksheet. It will not do anything when I hit my submit button on my userform to transfer the data to my worksheet.

 

[abdelfattah]

see if that work for you , don't forget change date formatting if it doesn't show based on your setting in PC

Private Sub submitcmd_Click()
Dim ws As Worksheet
Set ws = Sheets("Invoice")
Dim nextRow As Long
nextRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
ws.Range("A" & nextRow) = Format(Me.invoicedatetxt.Text, "mm/dd/yyyy")' Change date formatting as you need it.
ws.Range("B" & nextRow) = Me.invoicenumbertxt.Text
MsgBox "Done"
Me.invoicedatetxt.Text = ""
Me.invoicenumbertxt.Text = ""
End Sub

 

[mmix803]

Sorry it did not work. I too have tried everything.

 

[abdelfattah]

no way !!
it works for me !!
make sure from names :
sheet name= invoice
commandbutton1=submitcmd
textbox1=invoicedatetxt
textbox2=invoicenumbertxt
make sure for all of names should match with inside the code .

 

[mmix803]

i will check it again. Thank you. I am using the submit button on my form.