using excel userform to populate table1

  • Hello everyone,


    I have been researching and researching with building userforms and their reactions with the spreadsheets. I came across so many different ways to accomplish the same thing. It's crazy how many people do this differently. So I came out with this fully functioning coding that does the trick.... except when I converted my spreadsheet list to a table (Table1), all hell broke loose.. haha. nothing works. Can anyone help me with altering the code WITHOUT completely rewriting the entire script? And for those who are seeking the same assistance, I will share my current coding and if I can get help with this, I will share the end result as well.


    Admin Edit: Also posted on Mr. Excel: https://www.mrexcel.com/board/…1-on-spreadsheet.1121121/


    USERFORM:

    MODULE:

    Files

    • NewFormat.xlsm

      (39.49 kB, downloaded 33 times, last: )

    Edited once, last by S O: added cross post link ().

  • Hello and Welcome to the Forum :)


    First of all ... Congratulations on your very nice UserForm


    When it comes to Tables ... in fact a Table is a ListObject !!!


    Below is a good explanation :


    https://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Jim, I'll try to play with this later today ... I guess you are getting the same as me, which is: change the lists to tables, and then the macro kills Excel completely (crash, attempting to recover etc).

  • Jim, can I ask ... why do you want to convert the list to tables anyway?

    Glenn, There will be more sheets and tables that will be created that will auto-populate from this table using all sorts of formulas. I'm using the userform for my data entry... Master Items (raw materials), vendors, etc. I'm tinkering around with an inventory tracking idea.

  • Glenn, There will be more sheets and tables that will be created that will auto-populate from this table using all sorts of formulas. I'm using the userform for my data entry... Master Items (raw materials), vendors, etc. I'm tinkering around with an inventory tracking idea.

    I've created such things in the past, and have always used native Excel lists, not Tables. I wondered why you were choosing Tables.

  • Can I ask why you don't have all the code for the userform in the userform?


    Your table is not a Table or Listobject.


    I've had a look at your code and started amending it.


    1. I have converted the data to a Table.

    2. I have moved the the set up of the userform to the Initialize event of the userform. I see no reason why you have it in a module.

    2. I have put a Procedure in the UserForm to load the data using the Table to the ListForm

    4. I have re-written your save code to write to a new row in the Table and clear the form.

    5. The Function ClearAll will clear the data before loading the amended data. It is a Function so that it can be applied to any UserForm.


    I'll have a look at the rest of the code later.

  • Can I ask why you don't have all the code for the userform in the userform?


    Your table is not a Table or Listobject.

    I've had a look at your code and started amending it

    2. I have put a Procedure in the UserForm to load the data using the Table to the ListForm

    Roy, thank you so much. that looks incredible.

    "Can I ask why you don't have all the code for the userform in the userform?"....

    ---- I was trying to have less coding on one page. minimize the mess by using the "Call" function. tinkering with ideas I guess.


    2. I have put a Procedure in the UserForm to load the data using the Table to the ListForm

    ---- Nothing appears in the list box when I open the form via the button on the first page.

  • Tables replaced Lists in Excel with version 2007

    Yes, they did. I know they did. I still don't like Tables, and they annoy me every time I encounter them in my work. I'm going to continue to avoid them, best I can, until I actually retire. Stubborn, aren't I????? :)

  • Thanks Carim.

    My workarounds/reasons ...


    1) Drop-down lists ... I hit Ctrl+Shift+L and they are there in my range


    2) Column Headings are always visible ... I freeze the top row of my sheets


    3) Automatic Totals - even when you filter. OK, that is a good one, but I use PivotTables on most lists, and can see that instantly. If I want it at the end of the list, I leave a blank row, then have my totals (SUBTOTAL function, obviously), so that when I insert more rows at the bottom, the ranges adjust automatically.


    4) Banded rows - if I want that I use Conditional Formatting, formula, with ISEVEN(ROW())


    5) Dynamic Named Range Created Whenever you Create a Table - I create one myself ... using the OFFSET function in a name


    6) Magic Formulas - this is the reason I DON'T use tables, as I absolutely hate the referencing being this way. The Magic Formulas outside of a Table are useful, but not worth having to put up with having to use the horrible referencing method inside the table.


    7) Create a Dynamic Range for Charts & PivotTables - yeah, useful, but I generate my own dynamic ranges all the time anyway.

  • Hi Jim, welcome to OzGrid.


    Please note that one of the rules of this forum (and quite commonly others too) is that you declare when you have asked a question on another forum. This is commonly referred to as cross-posting and when you declare this it means that we can check the other sites too and make sure that we aren't just duplicating effort.


    I've updated your post on this occasion, but please do keep this in mind for the future.


    All the best,


    Sam