+ Reply to Thread
Results 1 to 12 of 12

Userform to enter data into formatted table

  1. #1
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Userform to enter data into formatted table

    I am trying to build an Excel database for my son's restaurant to manage recipes and foods costs. I am very familiar with building database applications in MS Access but I am very new to doing the same thing using excel. Formulas, etc. on a spreadsheet are easy but the idea of formatted tabled and relationships is very foreign to me. I have my table(s) created with several calculated columns that are all functioning well. I want to use a userform to enter data into the table and I know that the code is different than entering into an unformatted range on a sheet.. I have been reading and trying different things but so far have been unable to get anything to work for me.

    My table (tlProducts) has 25 columns but only 7 of them are on the userform. The remaining columns are calculated based on data entered. The form controls include, txtProductID, txtProductName, txtDate, txtSize, cboUnit, txtPrice, txtYield. I need to find the end of the table, create a new row and enter the data in that row. The code that has been suggested to me is below, however it produces the error, 9 - Sub script out of range. The range of my table (tblProducts) is C7:Y12 (header row included). I also want to lookup the max ID number and increase by one for the new record. I have been getting so frustrated. I think things make sense but then it fails. Perhaps I have bitten off more than I can chew but I really want to learn and get a functioning application for my son's restaurant. Any help you can give me would be greatly appreciated.

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    01-08-2016
    Location
    Cagayan De Oro City, Philippines
    MS-Off Ver
    2013
    Posts
    152

    Re: Userform to enter data into formatted table

    Hi

    i just quickly put something together here which is super simple

    Please Login or Register  to view this content.
    If you need some things adding for relationships etc on the Primary Key you could just store a value on a hidden sheet that tracks this number and increments everytime 'tbl.Listrows.add' is used

    Thanks


    PS - make sure you change the names to the correct ones on the code sheets("") and ListObjects("")

  3. #3
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Userform to enter data into formatted table

    Much easier to provide accurat answer if you post example file wit Table, Userform and code.

  4. #4
    Forum Contributor
    Join Date
    01-08-2016
    Location
    Cagayan De Oro City, Philippines
    MS-Off Ver
    2013
    Posts
    152

    Re: Userform to enter data into formatted table

    I think the OP is very clear on what he needs doing......

    Oh @omagoodness......

    Change this line
    Please Login or Register  to view this content.

    To
    Please Login or Register  to view this content.

    The reason being is your error maybe caused by the table name being wrong, this will tell you immediately if the table is missing or miss-named

    if this isnt the reason for your error then the code i gave you earlier will fix your problem

    just add the parts linking your form input to the 'TableNewRow.Cells(#).Value'

  5. #5
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Re: Userform to enter data into formatted table

    OMG! I'm about ready to tear my hair out. I tested the code you provided in a macro and it worked perfectly! So, I copied the code to the cmdAdd button on my form and added the lines to add data to the remainder of the table columns. Guess what? I got the error "Method 'Add' of Object 'ListRows' Failed". Then when I click the debug option, Excel completely stops working and restarts. So, I went back to the beginning and did the test macro again and got the same error. I even tried copying your code again in case I inadvertently changed something and couldn't see it.

    Upon reviewing your code, I think I understand most of it except, when I did the first 'successful' test, the next id number was automatically incremented and I don't see how that happens in your code. What am I missing? I know this is an ambitious project for a 'newbie' but I am determined to do it so you will be hearing from me a lot. Thanks in advance for your help.
    Here is a link to a sample of my project. https://drive.google.com/file/d/0B-P...ew?usp=sharing

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Userform to enter data into formatted table

    This works for me.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Re: Userform to enter data into formatted table

    Thank you so much ... but ... when I ran the code using your file, I got the same error (4 times). However this time Excel didn't shut down on me. I am running the 2016 version of Excel. Could there be a glitch or something or perhaps something in my settings that may be causing this?

    Please Login or Register  to view this content.

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Userform to enter data into formatted table

    Try it this way.
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    01-08-2016
    Location
    Cagayan De Oro City, Philippines
    MS-Off Ver
    2013
    Posts
    152

    Re: Userform to enter data into formatted table

    if bakerman2's last post doesnt work you can try the following.

    you can actually get away without adding a row to the table, all you do is offset the last row of the table by 1 (so you get the row under the table) and add something there, the table will automatically increase its size by 1 row to encompass the new data.

    Please Login or Register  to view this content.
    however if you try and use the 'tbl.DataBodyRange' code when the table has no data you will get an error because 'DataBodyRange' doesn't exists until you use the 'AddRow' part......

    as for your error, excel is a pain in the backside sometimes. how long have you been working on this project? sometimes adding and removing things constantly can cause your excel file to bloat with unused junk that has not been cleaned properly and causes problems, however this may not be your problem. Google 'VBA Decompiler' (website orlando.mvps.org) and try this tool out you never know, i swear by this tool when building production systems on excel.

  10. #10
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Re: Userform to enter data into formatted table

    Thank you all so much for your suggestions. I will try everything and see which one works for me. BTW - I have not been working on this project very long and the few rows of data that are there are the ones I entered manually to be able to create the table so it is still very clean that way.

  11. #11
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Re: Userform to enter data into formatted table

    I downloaded and ran the decompiler and now bakerman2's suggested code works perfectly. Thanks for the tip!!!!!

  12. #12
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Userform to enter data into formatted table

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] trouble sorting data formatted as table
    By Palooka_ in forum Excel General
    Replies: 3
    Last Post: 07-09-2015, 03:52 PM
  2. Replies: 15
    Last Post: 02-12-2015, 03:19 AM
  3. Replies: 0
    Last Post: 02-27-2014, 04:43 PM
  4. Linking formatted chart to pivot table data
    By vlag in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-23-2013, 10:29 AM
  5. Userform to enter data
    By phalcon45 in forum Excel General
    Replies: 3
    Last Post: 11-17-2012, 08:19 AM
  6. Replies: 7
    Last Post: 11-08-2012, 03:06 PM
  7. Userform to Enter Data
    By stanysurfer in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-03-2010, 08:45 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1