I'm sooooo close... Combobox and RowSource problems in an Add-in

Dr Obnxs

New Member
Joined
Jun 8, 2012
Messages
12
Hi all, found another one that I've been banging my head on...

So, I have an Add-In (named "My Add-in.xlam") that I use for dynamic file lists and the like. The sheet name is "ValidationSheet". I have a combobox that is in my userform that is named "cbSegSelect" and I can't find the right syntax for setting the combobox list. The data is in cells E7:E22 (but the list lenght with grow).

So to start, I use the following vba code to assign RowSource
Code:
cbSegSelect.RowSource = "E7:E22"
This works, but gives me the values from the active sheet. But I'm using an add-in, so ValidationSheet isn't the active sheet.
So I try
Code:
cbSegSelect.RowSource = "[My Add-in.xlam]ValidationSheet!E7:E22"
And it faults. I get the error Run Time Error #380; Could not set the RowSource property. Invalid property value.

So I thy to create the addressing direct from code so I don't do any typos:
Code:
cbSegSelect.RowSource = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(1).Name & "!E7:E22"
Same errors.
But all these work!
Code:
cbSegSelect.RowSource = "Sheet1!E7:E22"
cbSegSelect.RowSource = "[2012.prn]Sheet1!E7:E22"
Only 2012.prn is the active workbook, not the Add-In!


In the Properties window it will take "E7:E22", but it won't take anything that points to the add-in sheet. Both of these fail:
  • [My Add-in]ValidationSheet!E7:E22
  • [My Add-in.xlam]ValidationSheet!E7:E22
But all these are accepted, just pointing to the active worksheet, not the add-in as well!

  • E7:E22
  • Sheet1!E7:E22

I Must be missing something pretty simple, but I just don't know what!

Matt
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Try this...

cbSegSelect.List = ThisWorkbook.Sheets("ValidationSheet").Range("E7:E12").Value

ThisWorkbook is the workbook with the code or in your case the add-in
 
Upvote 0
The plot thickens....

If I use your code, and Add-In is set to "TRUE" in the workbooks properties, then I get "Run Time Error: 70, Permission Denied". If I set Add-In to False, it runs fine.

Any clue where this permission is set and how I can get rid of it?

Thanks,

Matt
 
Upvote 0
I can set the IsAddin property to TRUE, but it didn't replicate the problem. My UserForm combobox.list populated with the values as expected.

Are you trying to .Select or .Activate anything within the Add-in workbook?
 
Upvote 0
Something odd is going on in the original Add-in. When A copy the whole add it to a new file name, close "My Add-in.xlam" and run the very same code in the new add-in,, it all works, even when the "IsAddin" property is set to "True"
 
Upvote 0
That was it. This is the first time I'm really digging deep in this Add-In, and it's a steep learning curve for sure. Thank you for your help.

I've finally bumped my head on the last thing that we keeping me from progressing. I've made a lot of progress in the last day or so.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top