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

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,074
Office Version
  1. 365
Platform
  1. Windows
Ok some of the ranges are only single cells, so easiest option is to do it like
VBA Code:
    UFADD.menutype.RowSource = ThisWorkbook.Sheets("Lists 2").Range(NEWLIST1).address(, , , 1)
 
Solution

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
UPDATE
I'm not sure why, but as I mentioned in the beginning, I felt like the issue has something to do with the new workbooks that are created once you click create and continue. So to test this, I added a line of code that closes the new workbook without saving it just to see if my hunch was correct. As it turns out, it was. Not sure what this means in terms of finding a work around or if this is helpful to any of you, but I would prefer to NOT have to save the new files in order to close them so the code works. Most of the newly created files are simply printed, some may need to be edited and only a few are saved if there is a chance it will need to be added to.
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Ok some of the ranges are only single cells, so easiest option is to do it like
VBA Code:
    UFADD.menutype.RowSource = ThisWorkbook.Sheets("Lists 2").Range(NEWLIST1).address(, , , 1)
I will test this solution, but my question still is, why does it work initially? I can open the workbook provide the event details on the UFWELCOME form, click next and add items all day long on the UFADD form - once I click finish on the UFADD form, and then create and continue on the UFSAVE form to begin again, I start the process over with the UFWELCOME form, click continue, but then the combo boxes on the UFADD form are now a problem.
I am going to test your proposal now, but its confusing me . . .
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Ok some of the ranges are only single cells, so easiest option is to do it like
VBA Code:
    UFADD.menutype.RowSource = ThisWorkbook.Sheets("Lists 2").Range(NEWLIST1).address(, , , 1)
That actually appeared to solve the problem!! May I ask how or why this worked? what does ".address(,,,1) do exactly?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,074
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The problem is that once you open a new workbook, the code is looking for the named ranges in the new book.
The address(,,,1) return the full address of the range including the workbook
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
The problem is that once you open a new workbook, the code is looking for the named ranges in the new book.
The address(,,,1) return the full address of the range including the workbook
How does replacing the original code with your new code in only ONE of the boxes make the others fall in line?

And if I neglected to mention it, Thank you SOOOOO much for this! Not sure if you read this whole post but I am still learning and have been working on this project for MONTHS. I was so glad to have what I thought was a finished product until this issue. I was ready to just quit because I couldn't figure out what I was doing wrong. Thank you so much for your time! I sincerely appreciate the info/effort!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,074
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How does replacing the original code with your new code in only ONE of the boxes make the others fall in line?
I wouldn't expect it to.
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
I wouldn't expect it to.
that doesn't sound promising. (lol) I will keep playing with it to see if I can get any other errors, but thus far, It seems to be working as expected. Thanks again!
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
I wouldn't expect it to.
I do have one final question, if I may. . . .

How would you write your proposed code without referencing the Sheet Name but instead use the Sheets code name (Sheet1 vs "Lists 2")? I want to avoid an issue if someone changes the sheet names.
VBA Code:
UFADD.menutype.RowSource = ThisWorkbook.Sheets("Lists 2").Range(NEWLIST1).address(, , , 1)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,046
Messages
5,767,822
Members
425,437
Latest member
blaix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top