From what you describe it seems to be working as I intended and what I
thought you wanted -

a) Define a named range "myList" A1:A22 for the 'entire' DV list.
b) Define a 'dynamic' named range "myList8" to return cells from the value
of the spinner (linked to E1) to 8 rows below.
c) Set the 'Max' value of the spinner to 8 less than the rows-count of
"myList"

So when the spinner changes the drop down list should show the dynamic range
myList8, namely a segment of the full list (myList) between the value of the
spinner and 8 rows down. Indeed the spinner should stop at 8 less than the
count of the full list (without re-creating I would have thought the spinner
stops at 15 rather than 14, ie 22-8 in the example).

It's not designed to change the value of the DV cell, do that by selecting
from the 'reduced' dropdown having set it approximately in the right region
with the spinner.

Do I follow correctly you want the spinner to change the DV cell. Wouldn't
that mean changing the spinner between 1 & 10's of k's, a lot of clicks!
surely not.

Try re-defining the name 'myList' to the range of your actual list (a single
column).
As you've got such a large list increase each instance of 8 in the example
to say 25.

I also need to preserve the ability to access the entire range
of values in the DV drop down at all times for the long scrolls to older
records (there is a list of 10s of thousands of rows). Do you have a way
to
do this?

Sub ToggleDVlist()

Dim dvCell As Range
Dim sFmla As String
Static bFlag As Boolean

sFmla = IIf(bFlag, "=myList", "=myList8")
bFlag = Not bFlag

Set dvCell = Range("E5") ' the DV cell on activesheet

With dvCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=sFmla
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
MsgBox "DV list in " & dvCell.Address(0, 0) & " " & sFmla

End Sub

This is only slightly adapted from a recorded macro. Record your own to get
any other DV settings and adapt as above. Run the macro from a button on the
sheet.

Regards,
Peter T

PS don't forget you can change the value in the spinner's linked cell (E1 in
the example) instead of using the spinner, and avoid many spinner clicks.




"RD Wirr" wrote in message
...
Hi Peter,

Thanks for the help. I really appreciate it. I hope you can bear with me a
bit longer. I have followed your instructions and admittedly I don't
follow
exactly the VBA portion of it, but it works, although for some reason E1
always stops at a maximum value of 14. What I get is a DV drop down where
the
validation list is limited to 8 cells of data and upon incrementing the
spinner, the 8 lines of dropdown data is being shifted rather than
changing
the current value in the validation cell. Is this what you intended? If I
got
something wrong here, please let me know.

For my purposes I need to actually change the current value in the DV
cell.
I am using that DV cell as the reference for a Vlookup to find a
particular
row of data. I also need to preserve the ability to access the entire
range
of of values in the DV drop down at all times for the long scrolls to
older
records (there is a list of 10s of thousands of rows). Do you have a way
to
do this?

Thanks n Regards,
RDW


"Peter T" wrote:

A couple of refinements -

SpinButton1, SmallChange: 8

for info -
=INDEX(myList,E1) &"-to-" &INDEX(myList,E1+7)

I put a small 'horizontal' the spinner in the cell above the DV
dropdown,
all seems to work quite well.

Peter T


"Peter T" <peter_t@discussions wrote in message
...
Perhaps something like this (only lightly tested) -

Named ranges
myList: the full DV list in a column, eg =$A$1:$A$22
myList8: =OFFSET(myList,$E$1-1,0,8,1)

ActiveX Spinner from the Controlbox menu, properties
Min: 1
Max: start with (list-count +1 -8)
LinkedCell: E1 (used in the offset formula above)

Private Sub SpinButton1_GotFocus()
Dim nMax As Long
nMax = Range("myList").Rows.Count - 8
If nMax < 1 Then nMax = 1
SpinButton1.Max = nMax
End Sub

Use 'myList8' for the custom DV list

The 8 could be a cell ref

Could also use a Forms spinner, though more work to update its max
value
if
myList-rows count changes.

Regards,
Peter T



"RD Wirr" wrote in message
...
I have an application that has a validation drop down list that
references
a
long list of dates to lookup data associated with the dates. This
works
fine
but since the list is long, it is cumbersome to advance
incrementally
with
the dropdown list so I would like to have a spinner that can advance
the
value in my validation cell incrementally. I still want to keep the
drop
down
list for deep lookups but then be able to increment the value in
that
same
cell with the spinner. Anyone have a solution for this?
Thanks,
RDW