Excel VBA: Add Row to end of Table error

Hi Guys,
I've been programming for what seems ages and never ran into a similar issue.
I haven't prepared the file to attach but I just want to ask if somebody ever ran into similar issues.
I have one worksheet named Database an the variable in VBA is DBSheet
The Table (Listobject) is named tbl_Data and the variable is DBTable


The code below

Please Login or Register  to view this content.

Works perfectly and adds a row to the end of the Listobject and it returns the correct row number in the table, this table holds 33 rows and after adding 34, so thta's correct

I also have a Userform and a button to add data

Please Login or Register  to view this content.

The moment I confiem and the code 'add_Row_2_Table' is run it thows an error unable to address the ListObjec bla bla and Excel just closes, no chance to press debug or anything, it triggers the error immediately

Any ideas?

I've rebuilt the table, it has 7 columns and the first column a formula to place the row number in the table
the formula:

Please Login or Register  to view this content.

If I remove the formula the same happens so that's not it
Worksheet is unprotected and unlocked.

Any ideas would be welcome

Hi,

difficult with a partial code, I can just see how to optimize your code just removing the useless but no relation with your issue …
How many tables in worksheet DBSheet ?

attached demo form is the format that I have adopted for virtually all my 'run of the mill' userforms.
in the sub 'SAVEDATA' if adding to the next bottom row of a table
first use the 'CLEAR' button if adding data - then press 'ADD' after filling in textboxes.

Please Login or Register  to view this content.

Do away with the Function...This has never failed me...Populates Col B to whatever
No file to test...keep formula in Col 1

Please Login or Register  to view this content.

Hi Greg,
Thanks for looking, the thing is the code that adds the row works perfectly when invoked outside the user form.
The whole thing goes haywire when the Userform is used.
The file belongs to somewone I'm trying to help out too but this baffles me.
I'll attach the file in a moment.
Cheers,
Hans

The ADD button remains disabled but that I figured out
I'm attaching the file I'm working on in a moment, it's from someone I'm trying to help
Thanks for looking an yout effort
Cheers,
Hans

Hi Guys,
Here's the culprit and I cannot figure out why.

Just open the userform add data and press ADD

Hi Keebellah, Initialization of the form failed for me the setting of the worksheet and table should be - this is also wrong in the submit routine

Please Login or Register  to view this content.

following on from that the combobox loads failed - at this stage it is far past my bedtime so will look again tomorrow.
Also had a 'deja vu' moment when I saw the database - so I have attached a solution I gave four weeks ago - maybe the listview did not work on the O.P. computer although I had no feedback.

Hi, this OP asked another question in the My Online Training Hub forum about the display size and this database, that I fixed but then ran into the ADD error.
Your file throws an error with the view that I'm misssing project or library
I'll try your macro on the version I attached earlier and see what that does.
Thanks

Ignoring the obvious combobox population errors, Something tells me that book has become corrupt...My supplied code above which has never ever failed also errors as per your findings...

@sintek: just tested your macro in the file but the moment a listrow is added I get the same error and it it's gone.

Please Login or Register  to view this content.

Yes, that is the case...I copied your Worms controls to a new userform and all worked as it should...

Please Login or Register  to view this content.

Great. Thanks , but...
I still cannot understand why the code I have to add a row when run outside the form adds the row without errors and the moment it's trigerred by the userform command it hangs

I told you...Your workbook and Form had somehow become corrupt...Your code works as it should with my new workbook attached...
But why would you want to revert to the old style when a more stable simplistic method has been offered...

@Sintek: I agree for 200% with you, but was and am still curious what caused the corruption and why it works outise the form and not inside the ofrm.
Thanks for you sound advice and help, as always

One of Excels many unsolved mysteries...I had a very similar issue a couple of years ago with listrows.add method on 2010 & 2013 versions

@sintek: just tested your macro in the file but the moment a listrow is added I get the same error and it it's gone.

PS...Was not my code...Perhaps someone elses...

Here's the file with your userform
That's the only thing different. I removed the old worms for and inserted yours, renamend it and added some modifications. It works!!!
The secrets of Excel and its ways of workings will remain a mystery

Thanks again for looking at it

Hi again Hans,

Well, it took a bit of head-scratching, but it seems that the problem is caused by the following line in the "Reset" routine:

Please Login or Register  to view this content.

. . . commenting-out this line seems to avoid the problem of Excel crashing.

I've taken the liberty of rewriting your code to decouple the UserForm from the Workbook as much as possible - UserForms should not "know" anything about worksheet layouts etc., and similarly, workbooks should not "know" anything about UserForm controls etc.

The attached version of your workbook uses the following code in a standard VBA CodeModule:

Please Login or Register  to view this content.

and the following code in the VBA CodeModule of the UserForm:

Please Login or Register  to view this content.

Hope this helps - I'd be interested to hear any comments you might have.

Best regards,

Greg M

Hi again,

Just a (very!) slightly better version of the "Show_Form" routine:

Please Login or Register  to view this content.

Regards,

Greg M

Hi Greg,
No the error occurs when the routine is called from the command click to save the record.
The add_Row_2_Table routine works in any other place but not whne the command button is clicked.
I used Sintek's code and just replaced the userfrom and thta works but it still bugs me not to know the reason why Excel hangs and restarts

Just looked at your file and it works .
Works great and thanks for that.
Will take it with me and see what actually causes the problem, Reset is not called until after the attempt is made to save the new record so am still not sure where the actual corruption lies.
Thanks to you too for the tip and patience helping out.

Hi Greg,
The reason you got an error running Reset is probably because the code uses the UNIQUE() function which works only with 365 or version 2021 and newer, I forgot to mention that when posting the file.
Thanks again

Hi Sintek and Greg,
Even though the post is solved I wanted to share the last update where I included the Application Version Number check.
Greg reported het got an error when running Reset and the reason in his case is he is runnineg version 2016 and I used the UNIQUE() function which only works with 365 or 2021 and up.
I had run into this when helping a friend and found a code that detects the 'real' version numer using the registry entry.
In this attached sample I include that check in the USerform to check if the application version is greater than or equal 2021 or 365 and the form is then populated with either the UNIQUE() function or the Dictionary as Sintek did.

Maybe you can put it to use in other projects.

Thanks again for all the help suggestions and tips, never too ol to learn