Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: VBA code for Submit/Clear Button?

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code for Submit/Clear Button?

    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=1ob...Dn2OsSqXbO_nJb. Hope someone can point me to the right direction.


    Cy
    Thanks

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA code for Submit/Clear Button?

    Try this:

    Code:
    Private Sub CommandButton2_Click()
    Dim i As Long
        For i = 1 To 4
           Me.Controls("Textbox" & i).Value = ""
        Next
    
    End Sub
    
    Private Sub Submit_Btm_Click()
    Dim n As Long
    With Sheets("Overtime Record")
        n = .Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    
        .Cells(n, "C") = TextBox1.Value
        .Cells(n, "I") = CDate(TextBox2.Value)
        .Cells(n, "J") = TextBox3.Value
        .Cells(n, "K") = TextBox4.Value
    End With
    End Sub

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code for Submit/Clear Button?

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

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

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA code for Submit/Clear Button?

    Quote Originally Posted by cyrous0425 View Post
    Thanks for the reply. I got an error "Object variable or with block variable not set" for this line:

    Code:
     n = .Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Ah, sorry, my mistake.
    It should be:

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

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code for Submit/Clear Button?

    Error: Type mismatch

    Code:
    .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?
    Last edited by cyrous0425; Feb 6th, 2019 at 02:24 AM.

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA code for Submit/Clear Button?

    Quote Originally Posted by cyrous0425 View Post
    Error: Type mismatch

    Code:
    .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/meztxo3461...%201.xlsm?dl=0

    BCDEFGHIJKLMN
    9NO.PRNNAMEPOSNT/GRADEBASICDAYSOT/DATETIME INTIME OUTTOTAL OT HRSRATE/HROVERTIME TOTAL AMOUNT
    101rrJum04/05/201812:0012:221SAR 1,02

    Overtime Record



    Last edited by Akuini; Feb 6th, 2019 at 02:39 AM.

  7. #7
    New Member
    Join Date
    May 2018
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code for Submit/Clear Button?

    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/th4gf4l3pz...time.xlsm?dl=0
    Last edited by cyrous0425; Feb 6th, 2019 at 03:06 AM.

  8. #8
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA code for Submit/Clear Button?

    Quote Originally Posted by cyrous0425 View Post
    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/th4gf4l3pz...time.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.

  9. #9
    New Member
    Join Date
    May 2018
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code for Submit/Clear Button?

    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.
    Last edited by cyrous0425; Feb 6th, 2019 at 03:21 AM.

  10. #10
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA code for Submit/Clear Button?

    Quote Originally Posted by cyrous0425 View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •