I have a working application I'm trying to convert to an add-in. I have a
worksheet in the add-in workbook providing 160 named ranges of values used to
populate the app's pull down menus. My problem is I can't find a way to
reference the named ranges within a new workbook using the Add-in. I make
reference to the named ranges in two ways. From the forms, I set the
Combobox2.RowSource to ='DataFields'!"$A2,$A23"; and within the code I set
object.RowSource = ThisWorkbook.Names!Priority . This works in the same
workbook, but I can't find a substitute way of setting the reference to look
in the .xla based workbook since that is where the tables are. I've tried
x=workbooks("name.xla").worksheets("DataFields").names!Projects,
x=workbooks("name.xla").worksheets("DataFields").range("A2:A23"), ...
Nothing I've tried works.

Is there a way to do this?

I think the combobox is looking for a string that has this format when the
source is in another workbook:

[book1.xls]sheet1!a1:a10

So your code should create that I think.

--
Jim
"TimK" <(E-Mail Removed)> wrote in message
news:2C233E6E-768E-460D-9925-(E-Mail Removed)...
>I have a working application I'm trying to convert to an add-in. I have a
> worksheet in the add-in workbook providing 160 named ranges of values used
> to
> populate the app's pull down menus. My problem is I can't find a way to
> reference the named ranges within a new workbook using the Add-in. I make
> reference to the named ranges in two ways. From the forms, I set the
> Combobox2.RowSource to ='DataFields'!"$A2,$A23"; and within the code I set
> object.RowSource = ThisWorkbook.Names!Priority . This works in the same
> workbook, but I can't find a substitute way of setting the reference to
> look
> in the .xla based workbook since that is where the tables are. I've tried
> x=workbooks("name.xla").worksheets("DataFields").names!Projects,
> x=workbooks("name.xla").worksheets("DataFields").range("A2:A23"), ...
> Nothing I've tried works.
>
> Is there a way to do this?

That worked! That solves my form problem. I should be able to use this to
work out coding solution. I'd like to stick to the names if I can because
that allows the code to work without change should I add to the data ranges.
Thanks very much.

"Jim Rech" wrote:

> I think the combobox is looking for a string that has this format when the
> source is in another workbook:
>
> [book1.xls]sheet1!a1:a10
>
> So your code should create that I think.
>
> --
> Jim
> "TimK" <(E-Mail Removed)> wrote in message
> news:2C233E6E-768E-460D-9925-(E-Mail Removed)...
> >I have a working application I'm trying to convert to an add-in. I have a
> > worksheet in the add-in workbook providing 160 named ranges of values used
> > to
> > populate the app's pull down menus. My problem is I can't find a way to
> > reference the named ranges within a new workbook using the Add-in. I make
> > reference to the named ranges in two ways. From the forms, I set the
> > Combobox2.RowSource to ='DataFields'!"$A2,$A23"; and within the code I set
> > object.RowSource = ThisWorkbook.Names!Priority . This works in the same
> > workbook, but I can't find a substitute way of setting the reference to
> > look
> > in the .xla based workbook since that is where the tables are. I've tried
> > x=workbooks("name.xla").worksheets("DataFields").names!Projects,
> > x=workbooks("name.xla").worksheets("DataFields").range("A2:A23"), ...
> > Nothing I've tried works.
> >
> > Is there a way to do this?
>
>
>

I've tried to develop code within my macros to access the tables by name but
haven't been successful. I need to be able to change the RowSource of my
ComboBox fields because many fields on my forms change picklist context with
the content of other fields. I've tried several variations on the solution
for the in-form reference below but keep getting either "need object" or
compile errors on what I come up with. Does anyone have a way to set
rowsource to a named range of values in a sheet of an Add-in?

"TimK" wrote:

> That worked! That solves my form problem. I should be able to use this to
> work out coding solution. I'd like to stick to the names if I can because
> that allows the code to work without change should I add to the data ranges.
> Thanks very much.
>
> "Jim Rech" wrote:
>
> > I think the combobox is looking for a string that has this format when the
> > source is in another workbook:
> >
> > [book1.xls]sheet1!a1:a10
> >
> > So your code should create that I think.
> >
> > --
> > Jim
> > "TimK" <(E-Mail Removed)> wrote in message
> > news:2C233E6E-768E-460D-9925-(E-Mail Removed)...
> > >I have a working application I'm trying to convert to an add-in. I have a
> > > worksheet in the add-in workbook providing 160 named ranges of values used
> > > to
> > > populate the app's pull down menus. My problem is I can't find a way to
> > > reference the named ranges within a new workbook using the Add-in. I make
> > > reference to the named ranges in two ways. From the forms, I set the
> > > Combobox2.RowSource to ='DataFields'!"$A2,$A23"; and within the code I set
> > > object.RowSource = ThisWorkbook.Names!Priority . This works in the same
> > > workbook, but I can't find a substitute way of setting the reference to
> > > look
> > > in the .xla based workbook since that is where the tables are. I've tried
> > > x=workbooks("name.xla").worksheets("DataFields").names!Projects,
> > > x=workbooks("name.xla").worksheets("DataFields").range("A2:A23"), ...
> > > Nothing I've tried works.
> > >
> > > Is there a way to do this?
> >
> >
> >

You've certainly already thought of this ...

You get two things going:

1. The XLA (Number crunching Macros and Worksheets you don't want to
release), AND
2. The Template (With formatting stuff, navigation stuff, and charting
stuff)

You end up adding a hidden sheet in the template for navigation and
when your macros run you store data in the navigation sheet so the
Template can be saved as a normal Workbook.

On large projects you can allow business analysts to maintain the
template.

In other words, you have to handle it with coding. No easy work
around.

I agree, but How? Everything I've tried doesn't work...

"(E-Mail Removed)" wrote:

> You've certainly already thought of this ...
>
> You get two things going:
>
> 1. The XLA (Number crunching Macros and Worksheets you don't want to
> release), AND
> 2. The Template (With formatting stuff, navigation stuff, and charting
> stuff)
>
> You end up adding a hidden sheet in the template for navigation and
> when your macros run you store data in the navigation sheet so the
> Template can be saved as a normal Workbook.
>
> On large projects you can allow business analysts to maintain the
> template.
>
> In other words, you have to handle it with coding. No easy work
> around.
>
>