from User form insert to excel sheet separately ?

L

lilyn

Banned - Rules violations
Joined
Dec 29, 2020
Messages
4
I'm facing problem as I insert to my excel sheet from user form it recorded on both side of sheet.
I want it to be recorded on the sheet selected, how can I code it?

VBA Code: 
Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long

    Set sh = ThisWorkbook.Sheets("Database")

    iRow = [Counta(Database!A:A)] + 1

    With sh

        .Cells(iRow, 1) = iRow - 1
        .Cells(iRow, 2) = frmForm.txtDate.Value
        .Cells(iRow, 3) = Now()
        .Cells(iRow, 3).NumberFormat = "dd-mm-yyyy | HH:mm:ss"
        .Cells(iRow, 4) = frmForm.cmbShift.Value
        .Cells(iRow, 5) = frmForm.txtLeader.Value
        .Cells(iRow, 6) = frmForm.txtAssistance.Value
        .Cells(iRow, 7) = frmForm.txtLotNo.Value
        .Cells(iRow, 8) = frmForm.txtSoNo.Value
        .Cells(iRow, 9) = frmForm.txtBottleC.Value
        .Cells(iRow, 10) = frmForm.txtBottleN.Value
        .Cells(iRow, 11) = frmForm.txtTargetMan.Value
        .Cells(iRow, 12) = frmForm.txtManNon.Value
        .Cells(iRow, 13) = frmForm.txtManBorrow.Value
        .Cells(iRow, 14) = frmForm.txtTotalMan.Value
        .Cells(iRow, 15) = frmForm.txtPreform.Value
        .Cells(iRow, 16) = frmForm.txtColour.Value
        .Cells(iRow, 17) = frmForm.txtSetUp.Value
        .Cells(iRow, 18) = frmForm.txtTimeStart.Value
        .Cells(iRow, 19) = frmForm.txtTimeEnd.Value
        .Cells(iRow, 20) = frmForm.txtTotalHours.Value
        .Cells(iRow, 21) = frmForm.txtMaterialIn.Value
        .Cells(iRow, 22) = frmForm.txtRejection.Value
        .Cells(iRow, 23) = frmForm.txtProdOutput.Value
        .Cells(iRow, 24) = frmForm.txtRemarks.Value
        
        End With
                
        Set sh = ThisWorkbook.Sheets("Summary")
        
            iRow = [Counta('Summary'!A:A)] + 1
        
        With sh
        .Cells(iRow, 1) = iRow - 1
        .Cells(iRow, 2) = frmForm.txtDate.Value
        .Cells(iRow, 3) = Now()
        .Cells(iRow, 3).NumberFormat = "dd-mm-yyyy | HH:mm:ss"
        .Cells(iRow, 4) = frmForm.txtTotalMaterial.Value
        .Cells(iRow, 5) = frmForm.txtMachineCap.Value
        .Cells(iRow, 6) = frmForm.txtTotalOut.Value
        .Cells(iRow, 7) = frmForm.txtTotalRej.Value
        .Cells(iRow, 8) = frmForm.txtUtiliz.Value
        .Cells(iRow, 9) = frmForm.txtOut.Value
        .Cells(iRow, 10) = frmForm.txtRej.Value
        
       End With
    
End Sub

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com

1609223772350.png
1609223794160.png
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
hi lilyn,
Welcome to the board.

what do you mean by it recorded on both side of sheet???? please elaborate your query a bit more....
 
Upvote 0
Hi
as I understand your request
Try
VBA Code: 
Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long


    If InputBox("Type the sheet name yoi like") = "Database" Then
        Set sh = ThisWorkbook.Sheets("Database")

        iRow = [Counta(Database!A:A)] + 1

        With sh

            .Cells(iRow, 1) = iRow - 1
            .Cells(iRow, 2) = frmForm.txtDate.Value
            .Cells(iRow, 3) = Now()
            .Cells(iRow, 3).NumberFormat = "dd-mm-yyyy | HH:mm:ss"
            .Cells(iRow, 4) = frmForm.cmbShift.Value
            .Cells(iRow, 5) = frmForm.txtLeader.Value
            .Cells(iRow, 6) = frmForm.txtAssistance.Value
            .Cells(iRow, 7) = frmForm.txtLotNo.Value
            .Cells(iRow, 8) = frmForm.txtSoNo.Value
            .Cells(iRow, 9) = frmForm.txtBottleC.Value
            .Cells(iRow, 10) = frmForm.txtBottleN.Value
            .Cells(iRow, 11) = frmForm.txtTargetMan.Value
            .Cells(iRow, 12) = frmForm.txtManNon.Value
            .Cells(iRow, 13) = frmForm.txtManBorrow.Value
            .Cells(iRow, 14) = frmForm.txtTotalMan.Value
            .Cells(iRow, 15) = frmForm.txtPreform.Value
            .Cells(iRow, 16) = frmForm.txtColour.Value
            .Cells(iRow, 17) = frmForm.txtSetUp.Value
            .Cells(iRow, 18) = frmForm.txtTimeStart.Value
            .Cells(iRow, 19) = frmForm.txtTimeEnd.Value
            .Cells(iRow, 20) = frmForm.txtTotalHours.Value
            .Cells(iRow, 21) = frmForm.txtMaterialIn.Value
            .Cells(iRow, 22) = frmForm.txtRejection.Value
            .Cells(iRow, 23) = frmForm.txtProdOutput.Value
            .Cells(iRow, 24) = frmForm.txtRemarks.Value

        End With
    Else
        Set sh = ThisWorkbook.Sheets("Summary")

        iRow = [Counta(    'Summary'!A:A)] + 1

        With sh
            .Cells(iRow, 1) = iRow - 1
            .Cells(iRow, 2) = frmForm.txtDate.Value
            .Cells(iRow, 3) = Now()
            .Cells(iRow, 3).NumberFormat = "dd-mm-yyyy | HH:mm:ss"
            .Cells(iRow, 4) = frmForm.txtTotalMaterial.Value
            .Cells(iRow, 5) = frmForm.txtMachineCap.Value
            .Cells(iRow, 6) = frmForm.txtTotalOut.Value
            .Cells(iRow, 7) = frmForm.txtTotalRej.Value
            .Cells(iRow, 8) = frmForm.txtUtiliz.Value
            .Cells(iRow, 9) = frmForm.txtOut.Value
            .Cells(iRow, 10) = frmForm.txtRej.Value

        End With
    End If

End Sub
 
Upvote 0
fadee2 said:
hi lilyn,
Welcome to the board.

what do you mean by it recorded on both side of sheet???? please elaborate your query a bit more....
Click to expand...
I've solve this problem already. Thank you for the concern
 
Upvote 0
mohadin said:
Hi
as I understand your request
Try
VBA Code: 
Sub Submit()

    Dim sh As Worksheet
    Dim iRow As Long


    If InputBox("Type the sheet name yoi like") = "Database" Then
        Set sh = ThisWorkbook.Sheets("Database")

        iRow = [Counta(Database!A:A)] + 1

        With sh

            .Cells(iRow, 1) = iRow - 1
            .Cells(iRow, 2) = frmForm.txtDate.Value
            .Cells(iRow, 3) = Now()
            .Cells(iRow, 3).NumberFormat = "dd-mm-yyyy | HH:mm:ss"
            .Cells(iRow, 4) = frmForm.cmbShift.Value
            .Cells(iRow, 5) = frmForm.txtLeader.Value
            .Cells(iRow, 6) = frmForm.txtAssistance.Value
            .Cells(iRow, 7) = frmForm.txtLotNo.Value
            .Cells(iRow, 8) = frmForm.txtSoNo.Value
            .Cells(iRow, 9) = frmForm.txtBottleC.Value
            .Cells(iRow, 10) = frmForm.txtBottleN.Value
            .Cells(iRow, 11) = frmForm.txtTargetMan.Value
            .Cells(iRow, 12) = frmForm.txtManNon.Value
            .Cells(iRow, 13) = frmForm.txtManBorrow.Value
            .Cells(iRow, 14) = frmForm.txtTotalMan.Value
            .Cells(iRow, 15) = frmForm.txtPreform.Value
            .Cells(iRow, 16) = frmForm.txtColour.Value
            .Cells(iRow, 17) = frmForm.txtSetUp.Value
            .Cells(iRow, 18) = frmForm.txtTimeStart.Value
            .Cells(iRow, 19) = frmForm.txtTimeEnd.Value
            .Cells(iRow, 20) = frmForm.txtTotalHours.Value
            .Cells(iRow, 21) = frmForm.txtMaterialIn.Value
            .Cells(iRow, 22) = frmForm.txtRejection.Value
            .Cells(iRow, 23) = frmForm.txtProdOutput.Value
            .Cells(iRow, 24) = frmForm.txtRemarks.Value

        End With
    Else
        Set sh = ThisWorkbook.Sheets("Summary")

        iRow = [Counta(    'Summary'!A:A)] + 1

        With sh
            .Cells(iRow, 1) = iRow - 1
            .Cells(iRow, 2) = frmForm.txtDate.Value
            .Cells(iRow, 3) = Now()
            .Cells(iRow, 3).NumberFormat = "dd-mm-yyyy | HH:mm:ss"
            .Cells(iRow, 4) = frmForm.txtTotalMaterial.Value
            .Cells(iRow, 5) = frmForm.txtMachineCap.Value
            .Cells(iRow, 6) = frmForm.txtTotalOut.Value
            .Cells(iRow, 7) = frmForm.txtTotalRej.Value
            .Cells(iRow, 8) = frmForm.txtUtiliz.Value
            .Cells(iRow, 9) = frmForm.txtOut.Value
            .Cells(iRow, 10) = frmForm.txtRej.Value

        End With
    End If

End Sub
Click to expand...
Yes I already solve this problem exactly as your code given to me. Thank you
 
Upvote 0
Yes I already solve this problem exactly as your code given to me. Thank you
Click to expand...
So glad you sorted it out
And thank you for the feedback
Be happy
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,590
Messages
6,120,423
Members
448,961
Latest member
nzskater

Share this page

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