2147417848 Method Default of Object Range Failed (Excel version 2016)

I have a userform to enter data (frmProducts) into the table on sheet 2. Everything was working fine and then all of a sudden, I started getting this error when clicking the cmdAdd button. Then when I close the form, MS Excel stops working and restarts.
I only made one change since frmProducts last worked properly. I added a second userform (frmRecipes) to input data into a table on Recipes (sheet3). The second form (frmRecipes) has a dropdown box (cboProduct) based on column A in sheet 2 (Products). The code for the RowSource on cboProduct is:

Please Login or Register  to view this content.

.
I don't understand why a change in coding on a different form would interfere with the function of the first form so that may be coincidental. I have searched everywhere and can't find a solution. Can anyone help?
The code I am using to save data from the frmProducts to the table "Product" is as follows: (I have named all of the form controls as reg1, reg2, reg3, etc...for a total of 24 controls. Only 6 controls are visible to the user to enter data. All of the other controls are calculated controls based on the information entered.)

Please Login or Register  to view this content.

Hi there,

It's difficult to check this properly without access to your workbook and UserForms, but the following code contains an error:

Please Login or Register  to view this content.

The highlighted line treats the variable "nextrow" as if it were a string/numeric variable, but in fact it is declared as being of Type Range.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Thank you for your response. Since my post, I decided that I needed to convert my data range to a formatted table since I will need to develop some relationships. Now I am entering a new and foreign world. 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 needs to be modified. I have been reading and trying different things but so far have been unable to get anything to work for me. Having said that, I have started a new thread with a more meaningful title, "Userform to Enter Data to Formatted Table."

Hi again,

Ok on that - thanks for your feedback.

Regards,

Greg M