run-time error 380 could not set the rowsource property. invalid property value

[robertmwaring2]

I am pulling my hair out over here trying to figure out what is wrong but as I am still new to this, I cannot. I am hoping someone here can assist me.

A bit about my workbook: it opens hidden using only Userforms. The user enters data into the first userform (UFWELCOME), clicks a button that stores the entered data and closes that form, opening another (UFADD). This form has 3 combo boxes, a toggle button, and 2 command buttons. The combo box lists are dependent upon each other and are populated using named ranges within the workbook. It all works perfectly without issue. However, if they click the finish button on this form, it closes the form and opens another (UFSAVE). This form is to "export" (via copying the the requested sheets to a new workbook and clearing data from the original workbook to begin again). After this step you arrive again at the first userform. All is still functioning well to this point. However, when I make it to the UFADD form (second form in the process) I encounter the error message "run-time error 380 could not set the rowsource property. invalid property value". As I am still a newbie, I do not know enough to determine what the issue is but I am thinking it somehow has to do with the newly created workbook that is not saved or named - just sitting in the background waiting for the user to finish - but again, I don't know. I am just thoroughly confused why it works until a new book is created.

I am hoping someone could take a look at what I've done and let me know where my error is or what I need to do to overcome this problem. Below is the link to my workbook itself, and as I am unsure as to what code I should post here for evaluation, I have chosen to not post any, but am willing to - feel free to ask for it if you like - but there is QUITE A BIT between the userforms (5 in total) and modules (3 in total).

Thanks in advance for any suggestions - I sincerely appreciate it. I have been working on this project for MONTHS now and was so excited I thought I had it all nailed down until I came across this issue.

My Workbook

 

[robertmwaring2]

In the middle of an event so can't get to the computer just yet, sorry. But it doesn't matter what I enter into the form the first time around - it works. The ufwelcome firms performs as expected when it loads again after I select save and continue from ufsave. Once I click continue on ufwelcome and ufadd loads again, I can choose a meal period, any one but I cannot choose any menu type. Any one I've tried in any combination gives me the error.

The event is over in an hour - I can check the current region change then

 

[Zot]

In the middle of an event so can't get to the computer just yet, sorry. But it doesn't matter what I enter into the form the first time around - it works. The ufwelcome firms performs as expected when it loads again after I select save and continue from ufsave. Once I click continue on ufwelcome and ufadd loads again, I can choose a meal period, any one but I cannot choose any menu type. Any one I've tried in any combination gives me the error.

The event is over in an hour - I can check the current region change then

What I did:
Remove merged cells in Production Sheet
Range A8:A38
Range G8:G38
Then Align across the selection to make the form looks back like original

Remove merge cells in TEMP LOG
Range A18

Then I ran Enter Breakfast blah blah blah, Save
Next enter Break ..... save
Next Lunch... save

Exit ... Generate

No error anymore

I'm not sure what else since I'm on Excel 2016 ?

 

[robertmwaring2]

What I did:
Remove merged cells in Production Sheet
Range A8:A38
Range G8:G38
Then Align across the selection to make the form looks back like original

Remove merge cells in TEMP LOG
Range A18

Then I ran Enter Breakfast blah blah blah, Save
Next enter Break ..... save
Next Lunch... save

Exit ... Generate

No error anymore

I'm not sure what else since I'm on Excel 2016 ?

Hello,

Thanks for your response, but I think there may be some confusion, some of which on my part as I neglected to notice I was responding to two different people. I realize the cells on both the temp log and production sheet are merged in the file I uploaded. That is an error on my part. I have loaded a copy without this oversight below. When I last saved the file after the 380 error, I neglected to remove the data that was saved in it. Below is how the document begins AND ends if there is no error when it is run. Keeping the code as it is right now, I too get no errors and everything works as expected if from the UFSAVE form I select create and exit. It is ONLY when I select create and continue and begin again do I get the 380 error when I attempt to choose ANY item in the combobox (menutype) - the second one on the form. The list from this box is dependent on the selection from the one above it but despite the variable in the code (NEWLIST2) reading/showing the correct information when I try to debug, it just won't set the combobox rowsource to that information. Below is the line that is highlighted when I click debug to attempt to fix it. Again, this ONLY happens when I choose create and continue to make a new production sheet.

UPDATED WORKBOOK

 

[Alex Blakenburg]

I am in Australia and have logged off, I will have another look tomorrow.

 

[robertmwaring2]

What I did:
Remove merged cells in Production Sheet
Range A8:A38
Range G8:G38
Then Align across the selection to make the form looks back like original

Remove merge cells in TEMP LOG
Range A18

Then I ran Enter Breakfast blah blah blah, Save
Next enter Break ..... save
Next Lunch... save

Exit ... Generate

No error anymore

I'm not sure what else since I'm on Excel 2016 ?

UPDATED WORKBOOK

noticed I still neglected to remove fields form the temp log

 

[robertmwaring2]

Did making the currentregion change make any difference to you original error ?
If not can you give us exactly what you entered into each form up to getting the error >

Hello!

So I made the change as you suggested (currentregion). I still encounter the same error. I took screen shots of my process to better explain and loaded them in order. I uncommented the code on the workbook sheet so it would run as expected, first hiding the workbook and start the UFWelcome form. I entered th data shown in pic1, then pic 2, then in pic 3 I selected create and continue. I got my new production sheet in a new workbook as expected - no issues and the UFWelcome form opened again. I then entered the data in pic 4, then pic 5 - here is where the problem happens. No matter what I select in the first box (mealperiod), I get the same error code. I can choose the same items I did the first time around or a different item - it seems to make no difference, I still get the error as noted in pic 5. When I click degug i see the code line in pic 6 as the error, but don't know why as the variable is clearly loaded with the correct data as noted in pic7. Why would it load the rowsource on the first time through, but not the second?

I have also updated my file link having removed the data that should not have been saved to the original file and with the currentregion change you mentioned.

UPDATED WORKBOOK

 

[Fluff]

Change that line to

    UFADD.menuname.list = ThisWorkbook.Sheets("Lists 2").Range(NEWLIST2).Value

You may need to do the same for the other combos as well.

 

[robertmwaring2]

Change that line to

    UFADD.menuname.list = ThisWorkbook.Sheets("Lists 2").Range(NEWLIST2).Value

You may need to do the same for the other combos as well.

Seems to be the same type of error - it works when I run through it the first time, but after I create and save and try to go again, I get the errors listed below this time. It doesn't appear to make it as far as it does with the code as it originally was, but the error seems to be similar (as noted in the pics below)

 

[Fluff]

Are all the named ranges for that combo on the Lists 2 sheet?

 

[robertmwaring2]

Are all the named ranges for that combo on the Lists 2 sheet?

that is correct - they are. All combobox named ranges for the three comboboxes on that form are all on the same sheet (Sheet1/"Lists 2"). And the variable itself is loaded with the correct named range - it just wont set the combo list or row source on the second time through. There could be multiple times through this process before it is exited, i just cant get passed the second - much less anything more