Microsoft Excel 4.0 Macro -> VBA

[YvorL]

Hello!

I've a few named ranges, most of it in one sheet. This sheet updates itself (when the workbook opened), from a central worksheet. In the other sheets are the drop-down lists. The first problem I had was that some lists are dependent on the value of an other cell. So I named the ranges after these values and gave the list as:

=EVALUATE(INDIRECT(ADDRESS(ROW();5)))

The problem is that every time I open this workbook I got a noticing message that this is a Microsoft Excel 4.0 Macro.
Can I get rid of that somehow?
Like:
- there is an option to turn this message (and only this) of in this (and only this) workbook
or
- there is a macro doing the equivalent stuff

Thank You!

 

[RoryA]

Do you actually need the EVALUATE function? (that is what is causing the prompt)
If you use code you will simply get a different macro prompt so I'm not sure what benefit that would be to you.

 

[YvorL]

Yes I'm aware that this is the cause, and no I don't need this (exact) function. But I need the dependent list.
But I don't have any good workaround. Macros (trusted) are enabled, so it isn't a problem, but I don't know how to translate that formula to a macro. Or if there is an another formula that I can use. Unfortunately I have to work with MS office 2003. So my options are limited in a way.

 

[RoryA]

Why do you use EVALUATE? Is the list dynamic? If not, you can just use INDIRECT; if so, you can use Evaluate in code too.

 

[YvorL]

Yes it's dynamic.
It looks like this:
Sheet(SUM):
A1-K1: Name1;Name2;Name3;Name4...
Names:=OFFSET(Sheet!$A$1;0;0;1;COUNTA(Sheet!$A$1:$K$1))
A2-A10: Value1,Value2,Value3...
B2-B10: Value2,Value6,Value8...
...
Name1: =OFFSET(Sheet!$A$2;0;0;COUNTA(Sheet!$A$2:$A$10);1)
Name2: =OFFSET(Sheet!$B$2;0;0;COUNTA(Sheet!$B$2:$B$10);1)
...
(OFFSET+COUNTA: because these are changing and don't wan't to see blank options in the list)
Sheet(Work1),Sheet(Work2),Sheet(Work3)...:
A1 (list): =Names
A2 (list): =Names
A3 (list): =Names
...
B1 (list): =EVALUATE(INDIRECT(ADDRESS(ROW();1))

 

[RoryA]

You can use a function like

Function GetList(sName As String) As Range
    Set GetList = ThisWorkbook.Names(sName).RefersToRange
End Function

and then create a defined name, called say MyList, with a refersto:
=GetList(ADDRESS(ROW();1))
then just use =MyList in the DV source

 

[YvorL]

Somehow it doesn't work. Have you tried it?
I opened a new workbook to test it, did exacty (to make it easy) as you wrote and when I choose =MyList in DV source, a message pops up, that "The source currently evaluates to an error...".
I tried in a cell (d4), just plainly:
=GetList($A$3)
and the result was the same value as in the same column D, row 25, where the list is.
If I try =GetList(ADDRESS(3;1)) or =GetList(listname) I get a simple #VALUE error.
Am I doing something wrong?

 

[RoryA]

Sorry-when adjusting for your situation I forgot the INDIRECT part
=GetList(INDIRECT(ADDRESS(3;1)))
the referenced cell (A3) needs to be populated with a range name when you apply the dv list or you will naturally get an error

 

[YvorL]

Works like a charm!
Thank you!

 

[YvorL]

Looks like I was a bit too optimistic. As soon as I changed the named ranges to dynamic it stopped working
Any suggestion?