Special spinner

[RD Wirr]

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

 

[Peter T]

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

 

[Peter T]

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

 

[RD Wirr]

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]

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.

 

[Peter T]

Why not use a ScrollBar.
As with the Spinner set LinkedCell to (say) E1, Min Value to 1, SmallChange
to (say) 8 but this time set the Max value to total number of records in
your list.

Private Sub ScrollBar1_Change()
Range("E4") = Range("myList")(ScrollBar1.Value, 1)
' or name the DV cell
' Range("myDVcell") = Range("myList")(ScrollBar1.Value, 1)
End Sub

Private Sub ScrollBar1_GotFocus()
' this just to ensure Max is always correct
ScrollBar1.Max = Range("myList").Rows.Count
End Sub

You can have your cake and eat it if you define the dynamic range, myList8
and use as in the previous example for your custom DV list.

You could use a Forms Scrollbar with similar properties but assign a macro
to make the change, and possibly update the Max value of the Scrollbar.

FWIW, though not useful for DV -
=INDEX(myList, theLinkedCell)

Regards,
Peter T