Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special spinner
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special spinner
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special spinner
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special spinner
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special spinner
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special spinner
Hi Peter,
As you describe, I have set it up and it does indeed work as you intended. Sorry I wasn't more clear at first. For my application I need to be able to manipulate the data in the DV cell with both the dropdown and the spinner, calling up values from the same list. This is so that I can search through the whole long column for a particular date value at anytime but also be able to increment the value in that same cell with the spinner. I need to have coarse control with the dropdown and fine adjustments in the same cell with the spinner. The problem is; the DV dropdown list supplies only the dates of which there are usually many of the same but which are unique with the included time stamp. But this isn't enough information for the user to identify a specific record. So we still must scroll through these row entries, while reading the data (v)looked up from the associated row records. A drop down list is not as easy to use as a spinner for these smaller shifts and also, a DV dropdown always opens with the first row in the list so scrolling through the whole long list each time just to shift down a few rows is really cumbersome. For sure this is an application better suited to Access or some proper DB but as usual systems tend to be outgrown over time and we have to deal with them until we find or build a replacement. Anyway, I appreciate your help on this issue. If you know a way to load this DV cell from both the drop down list and the spinner, I will be very grateful. Thanks, RDW "RD Wirr" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Special spinner
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 "RD Wirr" wrote in message ... Hi Peter, As you describe, I have set it up and it does indeed work as you intended. Sorry I wasn't more clear at first. For my application I need to be able to manipulate the data in the DV cell with both the dropdown and the spinner, calling up values from the same list. This is so that I can search through the whole long column for a particular date value at anytime but also be able to increment the value in that same cell with the spinner. I need to have coarse control with the dropdown and fine adjustments in the same cell with the spinner. The problem is; the DV dropdown list supplies only the dates of which there are usually many of the same but which are unique with the included time stamp. But this isn't enough information for the user to identify a specific record. So we still must scroll through these row entries, while reading the data (v)looked up from the associated row records. A drop down list is not as easy to use as a spinner for these smaller shifts and also, a DV dropdown always opens with the first row in the list so scrolling through the whole long list each time just to shift down a few rows is really cumbersome. For sure this is an application better suited to Access or some proper DB but as usual systems tend to be outgrown over time and we have to deal with them until we find or build a replacement. Anyway, I appreciate your help on this issue. If you know a way to load this DV cell from both the drop down list and the spinner, I will be very grateful. Thanks, RDW "RD Wirr" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximum Value for Spinner | Excel Discussion (Misc queries) | |||
Can you hide a spinner??? | Excel Discussion (Misc queries) | |||
Spinner | Excel Discussion (Misc queries) | |||
Spinner | Excel Programming | |||
Using Protection on a Spinner | Excel Programming |