Hi Ken,
Thanks for the feedback. Using named-range reference - which I've
been fighting all day - seems to solve two problems: I was seeing an error
when defining more that 20 items on the pull-down; and it seems the list is
truely a reference! - excellent. On the other hand, Excel/VBA syntax is
definately "quirky" - fortunately there are lots of good people & examples on
the web. ;^) BTW, I vote for fixing the problems.
Hey, just noticed your "signature" - dBaseIII was my second language! Man,
after doing file handling under IBM Basic, I thought Ashton-Tate rulled!
Those were the days...
Cheers!
"K_Macd" wrote:
> tbd
>
> The way to go is to set up your validation list in another sheet then give
> it a range name (anchor it beyond the last row so you can add new items
> without having to redefine the range name) then refer to the sheet and range
> name from the validation dialog. By the way I only found this the other day
> after looking for about 5 years.
>
> Joel
>
> You were a little bit unkind to the fine folks at MS but what an interesting
> problem to have - fix the faults and alienate those who have developed
> work-arounds or continue to alienate the new-be's who rely on the
> documentation or good programming practice to get things done.
>
> --
> Ken
> "Using Dbase dialects since 82"
> "Started with Visicalc in the same year"
>
>
> "Joel" wrote:
>
> > I don't know how smart macrosoft programmers are. I know there are a lot of
> > poor descions where made by programmers at miicrosoft in developing different
> > products incluing windows and office. there are lots of problems that
> > microsoft never fies becauwe it would affect million of users if bugs were
> > corrected because customers softwae would also have to be fixed. sio I don't
> > know if names cells in excel uses links when using named ranges which will
> > use less memory and would be fster to execute; then performing a lookup of
> > the named ranges. I would like to think a link was used.
> >
> > "tbd" wrote:
> >
> > > Hi Joel,
> > > Sorry if I didn't explain well. I'm using VBA to control a worksheet
> > > where a set of cells are configured for Validation with a listbox (AKA
> > > "drop-down".) [Using VBA] it IS possible to use any sheet when 'Add'ing the
> > > listbox - this works for me:
> > >
> > > [during Workbook open-event handling]
> > > ActiveWorkbook.Names.Add Name:="myrange", _
> > > RefersToR1C1:="='Settings'!R10C3:R57C3"
> > > 'NOTE: The listbox choices come from the sheet named "Settings"
> > > [/]
> > > [executed once during Worksheet init...]
> > > Public Sub Range_Validation(rRange As Range)
> > > 'NOTE: rRange is NOT on the "Settings" sheet
> > > With rRange.Validation
> > > .Delete
> > > .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
> > > Operator:=xlBetween, Formula1:="=myrange"
> > > [/]
> > >
> > > My original question related to how the listbox choices are stored AFTER the
> > > .Add method. If a "reference" can be used, then specifying Validation for a
> > > single cell might cost as much memory as the reference - perhaps four or
> > > eight bytes. If the choices are always stored as actual data, there are
> > > memory-use consequences. Imagine one string is 10 characters, there are 100
> > > strings (100 choices) and there are 1000 cells where each cell needs a copy
> > > of the list. That's a meg of data Excel would have to manage. My experience
> > > with Excel 2002 and VBA leaves me skeptical that this will work well.
> > >
> > > I'm definately an Excel/VBA nube, but have used the Macro-recorder. With
> > > respect to Validation source, I never figured-out how to select the listbox
> > > choices from a different sheet (as required and shown in the working code
> > > above. )
> > >
> > > Thanks/Cheers!
> > > "Joel" wrote:
> > >
> > > > The source box in a validation list can either contain an array of values or
> > > > use a range of cells in the worksheet when you have the option set to "LIST".
> > > > If you set up a validation list manually on the worksheet you can select the
> > > > source to be a range of cells on the same worksheet as the validation list.
> > > > Excel doesn't allow you to use a different worksheet.
> > > >
> > > > If you need the VBA code for settting up the range just record a macro while
> > > > you perform the operation manually.
> > > >
> > > > "tbd" wrote:
> > > >
> > > > > Greetings,
> > > > > With VBA I'm currently specifying a long list of xlValidateList items
> > > > > (formula1:= CSVlist) for each of many cells. There is only one list
> > > > > (constructed at run-time), but there could be hundreds of values, and there
> > > > > may be hundreds of cells for which the user will select a unique value. I'm
> > > > > not sure, but I think Excel typically keeps a seperate xlValidateList for
> > > > > each cell - if so, this will be very inefficient.
> > > > >
> > > > > 1) Is it possible for Excel 2002 to implement the Validation list by
> > > > > reference to a data-structure or range - so that the "one list" only ever
> > > > > exists in one place, but many cells refer to it?
> > > > >
> > > > > 2) Please confirm: when VBA executes the following assignment:
> > > > >
> > > > > formula1:="=$n$1:$n$18"
> > > > >
> > > > > , the expression "=$n$1:$n$18" is evaluated immediately(?)
> > > > >
> > > > > Any help is appreciated,
> > > > > Cheersr!
|
Similar Threads
