VBA code for Submit/Clear Button?

[cyrous0425]

Good day guys. I'm trying to create a Data Entry using VBA code. I successfully created the User Form but I am having problem about the code in Submit / Clear button. So, basically I just want to fill up my excel sheet what ever I enter in my user form specifically in COLUMN C, I, J, and K. I know that I can do it by just simply using index/match formula,however, there will be thousand's of data to be entered and scrolling through out the data then entering the new data will be time consuming so I tried to create a macro for it. Sorry for my bad English. Here's my sheet by the way https://drive.google.com/open?id=1obslo7TWRXNkoDmkOFDn2OsSqXbO_nJb. Hope someone can point me to the right direction.


Cy
Thanks

 

[Akuini]

Try this:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] CommandButton2_Click()
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]4[/COLOR]
       Me.Controls([COLOR=brown]"Textbox"[/COLOR] & i).Value = [COLOR=brown]""[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Submit_Btm_Click()
[COLOR=Royalblue]Dim[/COLOR] n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Overtime Record"[/COLOR])
    n = .Range([COLOR=brown]"A:A"[/COLOR]).Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + [COLOR=crimson]1[/COLOR]

    .Cells(n, [COLOR=brown]"C"[/COLOR]) = TextBox1.Value
    .Cells(n, [COLOR=brown]"I"[/COLOR]) = [COLOR=Royalblue]CDate[/COLOR](TextBox2.Value)
    .Cells(n, [COLOR=brown]"J"[/COLOR]) = TextBox3.Value
    .Cells(n, [COLOR=brown]"K"[/COLOR]) = TextBox4.Value
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[cyrous0425]

Thanks for the reply. I got an error "Object variable or with block variable not set" for this line:

 n = .Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

 

[Akuini]

Thanks for the reply. I got an error "Object variable or with block variable not set" for this line:

 n = .Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

Ah, sorry, my mistake.
It should be:

    n = .Range("B:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

 

[cyrous0425]

Error: Type mismatch

.Cells(n, "I") = CDate(TextBox2.Value

)

Edit: I'm sorry my bad I will only get the error if I entered not a date in column I. Now I understand.
However, when I click submit it doesn't fill the data into my table?

 

[Akuini]

Error: Type mismatch

.Cells(n, "I") = CDate(TextBox2.Value

)

Edit: I'm sorry my bad I will only get the error if I entered not a date in column I. Now I understand.
However, when I click submit it doesn't fill the data into my table?

You put the code the form Add_New_Data, didn't you?
Here's the workbook:
I inserted one row of data in sheet Overtime Record via the userform:

https://www.dropbox.com/s/meztxo3461cuhdz/NB OT - Copy 1.xlsm?dl=0

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">NO.</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">PRN</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">NAME</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">POSN</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">T/GRADE</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">BASIC</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">DAYS</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">OT/DATE</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">TIME IN</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">TIME OUT</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">TOTAL OT HRS</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">RATE/HR</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #375623;;">OVERTIME TOTAL AMOUNT</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #A6A6A6;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">rr</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #A6A6A6;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #A6A6A6;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #A6A6A6;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #A6A6A6;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #A6A6A6;;">Jum</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">04/05/2018</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12:22</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #A6A6A6;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #A6A6A6;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #A6A6A6;;">SAR 1,02</td></tr></tbody></table><p style="width:12em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Overtime Record</p><br /><br />

 

[cyrous0425]

Yes I put the code in Add_New_Data
I downloaded the file in your drop box but when I click Add New Data button>Enter PRN, DATE, IN & OUT then hit submit the following information wont insert on my table? Any idea?

https://www.dropbox.com/s/th4gf4l3pzgyngf/Overtime.xlsm?dl=0

 

[Akuini]

Yes I put the code in Add_New_Data
I downloaded the file in your drop box but when I click Add New Data button>Enter PRN, DATE, IN & OUT then hit submit the following information wont insert on my table? Any idea?

https://www.dropbox.com/s/th4gf4l3pzgyngf/Overtime.xlsm?dl=0

By table you mean the table in sheet Overtime Record, right?
I have no problem doing that, so I don't know why that happened.

 

[cyrous0425]

My bad. I didn't noticed the record is inserted from the bottom of the table but I make it worked.

Thank you so much.

 

[Akuini]

My bad. I didn't noticed the record is inserted from the bottom of the table but I make it worked.

Thank you so much.

You're welcome, glad to help, & thanks for the feedback.