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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,883
Office Version
  1. 2016
Platform
  1. Windows
I was trying to figure out how you run your program. I'm seeing another error but not the error you mentioned.

Your PRODUCTION SHEET has merged cell and you were trying to copy Column E data in FROM FORM into the PRODUCTION SHEET Range A8 which is the merge cell. This will cause the error. I'm not sure if this caused the error you were seeing since sometimes error posted might not be the actual error.

Avoid merged cell in VBA if possible. Copy is only possible if the range is the case size. You can unmerge those cells and use Center Across Selection as Alignment.
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
I was trying to figure out how you run your program. I'm seeing another error but not the error you mentioned.

Your PRODUCTION SHEET has merged cell and you were trying to copy Column E data in FROM FORM into the PRODUCTION SHEET Range A8 which is the merge cell. This will cause the error. I'm not sure if this caused the error you were seeing since sometimes error posted might not be the actual error.

Avoid merged cell in VBA if possible. Copy is only possible if the range is the case size. You can unmerge those cells and use Center Across Selection as Alignment.
I may have overlooked unmerging that cell after I encountered the 308 error. It is not merged initially, but does get merged then reset again.
With regards to how our is run - I own the file, ufwelcome appears, I enter the data, click next and unwelcome closes, ufadd appears. I make selections from the combobox and click add button. Once all needed items are added, I click finish. Ufadd closes, then ufsave opens. From there I can export the production sheet or create another one. When I choose to create another one, the ufsave closes and ufwelcome loads. From there I start the process again. Only this time, when I attempt to make a selection from menutype combobox, I get the 380 error. It only happens after I save/continue. That's the error I cannot fix
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,298
Office Version
  1. 365
Platform
  1. Windows
Sounds like you might have more than one issue.

Mine fell over on this line:
VBA Code:
Sub COPYTOFROMFORM()
.........
Sheet3.Range(Rows("2:2"), Rows("2:2").End(xlDown)).Copy

I changed it to
VBA Code:
With Sheet3.Range("A2").CurrentRegion
    .Offset(1, 0).Resize(.Rows.Count - 1).Copy
End With

Possibly unrelated but let us know if it makes a difference.
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sounds like you might have more than one issue.

Mine fell over on this line:
VBA Code:
Sub COPYTOFROMFORM()
.........
Sheet3.Range(Rows("2:2"), Rows("2:2").End(xlDown)).Copy

I changed it to
VBA Code:
With Sheet3.Range("A2").CurrentRegion
    .Offset(1, 0).Resize(.Rows.Count - 1).Copy
End With

Possibly unrelated but let us know if it makes a difference.
I will check into that when I get back to the computer. I neglected to mention the workbook needs to be on sheet3 when ufwelcome begins or I've encountered a whole host of errors. I'll definitely look into your proposal when I get to the computer though. I appreciate your time, btw! Thank you
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Still new to vba, so forgive my ignorance. Was looking into currentregion - does this basically mean all areas sound the selected cell/ range until a blank row/column? By selecting only a2 does that avoid it treating the range as the entire table?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,298
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Still new to vba, so forgive my ignorance. Was looking into currentregion - does this basically mean all areas sound the selected cell/ range until a blank row/column? By selecting only a2 does that avoid it treating the range as the entire table?
There are going to be a number of ways of finding the range to copy. CurrentRegion happens to be one of the simplest.

The principle is select ANY cell in the data area. I have used A2 but A1 would give the same result.
To see what it does.
  • Select any cell in the data area
  • Hit Ctrl+* which is more specifically Ctrl+Shift+8
  • What you see highlighted is the Current Region
  • You are correct it stops at the first blank row and blank column
Because you don't want the heading, I shifted it 1 row down with the offset.
Since this now picks up one additional (blank) row at the end, I resized to one row less.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,883
Office Version
  1. 2016
Platform
  1. Windows
I may have overlooked unmerging that cell after I encountered the 308 error. It is not merged initially, but does get merged then reset again.
With regards to how our is run - I own the file, ufwelcome appears, I enter the data, click next and unwelcome closes, ufadd appears. I make selections from the combobox and click add button. Once all needed items are added, I click finish. Ufadd closes, then ufsave opens. From there I can export the production sheet or create another one. When I choose to create another one, the ufsave closes and ufwelcome loads. From there I start the process again. Only this time, when I attempt to make a selection from menutype combobox, I get the 380 error. It only happens after I save/continue. That's the error I cannot fix
I see.
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Thanks for that explanation!

I don't know enough to determine what the actual issue is, because if I step through it with the vba editor open, it Durant doesn't happen. Only when I actually open the workbook for use. It's so weird to me - and my only thought is that it perhaps had something to do with the new workbooks that were created but can't figure how it why. I've been trying for 2 days now and it's making me crazy. I was so excited I thought I had this project competed. I'm sure the code to you is a hot mess, but I've come a long way from where I started - with my original version using hundreds of rows of formulas as helper cells due to my lack of vba knowledge but I've read and learned so much in the past 8 months - what you see is actually a DRASTIC improvement. My original file was nearly 20Mb in size!
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,298
Office Version
  1. 365
Platform
  1. Windows
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 >
 

Forum statistics

Threads
1,148,364
Messages
5,746,275
Members
424,003
Latest member
paaskanama

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