Data Entry Form VBA code help

    Good evening all,


    I have attached a file that I am currently working on. After 1 week of trying, the coding error has beaten me and I am really stuck - annoyingly stuck I might add. I had some help at the start of this project by a good friend of mine on this forum and I have been to a few classes to update my skills....


    Basically when the employee clicks add new record a new form appears! Great, however when they press submit the data entered does not appear on the table. I'm sure this is just a simple piece of coding I am missing or have put in wrong. The new entries need to start appearing in C12 and so forth etc.


    To access the file when opening select "user" password is "Chuckles 1"

    to unprotect VBA code "pbarratt6344"

    I think the problem is you've got all the cells on row 11 merged. The way you have the program written it wants to add the new values to this row, but there is no column "C", "D", etc. I got the password from your setup sheet. I deleted row 11 and the program seems to work fine.


    Delete row 11 entirely and you've got it, I think


    The other solution would be to manually add some "fake" data to row 12, as you have the sheet right now. Once the first set of "real" data is added, ie, real data to row 13, delete out the Range("C12:O12") (the fake data, delete using shiftup option) so that the "fake data is deleted and the data on row 13 moves to row 12. After that all should work fine.


    Hope this helps.

    hi awsmitty


    many thanks for the reply, I understand what you mean.....I think! So when you deleted row 11 then filled out the form and pressed submit it worked?


    I assume that the coding is correct, I.e no errors or omissions so to,speak.


    Thanks again.

    It's in my signature


    DataBaseForm


    You need to reformat you daat into a proper datbase format - a table with a header row(1 only) and no entirely empty columns or rows. Also, no Merged cells

    Paulbxx,


    So far as I could see after simply deleting row 11, yes, the program worked fine. Or at least the data I entered on the form was transferred to row 11. There is still work to do in my opinion. You need to unload the form and I saw the other cells were updated. You need to check and make sure those updates are what you want. But the question to your problem as asked, the data was added.

    hi awsmitty,


    you are correct, I deleted that blank line and all works, thank you. I am now looking at putting some code in so that when they press submit the data goes in to the table as now but then the form closes. Shouldn’t be too difficult I think......he says......would you put the code at the very end of the sub command?

    Yes, You definitely need to put Unlaod.Me after all the data is in the sheet. If the form is unloaded before the data has been transferred to the sheet, the data could possibly go away with the form. Basically, it is the last thing to execute once the userform has completed its job, the very last piece of code in the last control object that the user has used. As you have the code written right now that would be the Submit button. If, later on you add more buttons or other controls are added, and one of these new controls become that last control used, Unload.Me would go there as the last thing to execute.

    Hi mate,


    I put a unload button for now on each form when opened until I can learn where to put the code, like you say at the end but at least it’s working for now.


    Only 1 issue I am seeing at the moment - if you are filling in the data direct onto the table itself, when entering a time it must be in 00:00 format not any other i.e. 00.00 or it returns a run again box etc. Is it possible to put a code on the user from “text box/combo box” that they must put the entry in 00:00 specific format when entering onto the user form of add or use else it will show a vbcritical message?


    I have trawled through ozgrid forums but cant see an answer that might fit?

    You do not actually need to do delete,or alter, Row 11. You just need to account for it in the code.


    Change your code to

    Code
    Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("P BARRATT")
Dim n As Long

n = sh.Cells(.Rows.Count, 3).End(xlUp).Row + 1
If n < 12 Then n = 12
sh.Cells(n, 3).Resize(, 7) = Array(TextBox1, ComboBox1, ComboBox2, TextBox3, TextBox4, ComboBox3, ComboBox4)

End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    hi,


    that’s great, thank you very much. I will try all the above tonight at work. I’m still working on the text box issue at the mo where a user when he has opened the add toil form must enter a time as 00:00 and not 00.00 or any other time format when they open the user form to add toil. searching the forums for help...

    hi everyone that posted on here, I worked out through looking on various threads the way to write a code for the time format and it works!


    thank so much.

    You haven't said what you came up with for the time format. I would usually suggest combobxes to select hour, minute,seconds.




    I have just written this code which might be useful. Also, remember when writing the time to the sheet then it's always best to set the NumberFormat


    Code
    ActiveCell.Value = Me.TextBox1.Value
ActiveCell.NumberFormat = "hh:mm:ss"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login