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:

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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
 

Dr Obnxs

New Member
Joined
Jun 8, 2012
Messages
12
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
 

Dr Obnxs

New Member
Joined
Jun 8, 2012
Messages
12

ADVERTISEMENT

It's the "IsAddin" property for the workbook in the VBA editor's Properties Window

Matt
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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?
 

Dr Obnxs

New Member
Joined
Jun 8, 2012
Messages
12
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"
 

Dr Obnxs

New Member
Joined
Jun 8, 2012
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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