Auto scroll down data validation list
 

I have created a pull down menu using Data: Validation to list several
gas wells. A chart on the sheet references the list so that the well's
production shows up on the chart when I select it from the list. Is
there a way to use a button to scroll through the list automatically so
that I do not have to select the list with the mouse each time I want
to change well? Thanks.

Tex, try Alt key and up or down arrow to open the drop down and move from
top to bottom
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

wrote in message
oups.com...
I have created a pull down menu using Data: Validation to list several
gas wells. A chart on the sheet references the list so that the well's
production shows up on the chart when I select it from the list. Is
there a way to use a button to scroll through the list automatically so
that I do not have to select the list with the mouse each time I want
to change well? Thanks.

Assuming your data validation source is a named list in the workbook,
you could use code similar to the following. Use a spin button from the
Control toolbox, and assign the DVUp code to the SpinUp event, and the
DVDown code to the SpinDown event.

There's an example he
http://www.contextures.com/excelfiles.html
Under data validation, look for 'Data Validation Spinner'

'============================
Sub DVUp()
Dim intDV As Integer
Dim ws As Worksheet
Dim c As Range
Dim rngList As Range

Set ws = Sheets("DVScroll")
Set c = ws.Range("B2")
Set rngList = ws.Range("MonthList")
intDV = 0
On Error Resume Next
intDV = Application.WorksheetFunction.Match(c.Value, rngList, 0)

If intDV = 0 Or intDV = rngList.Rows.Count Then
c.Value = rngList.Cells(1, 1)
Else
c.Value = rngList.Cells(intDV + 1, 1)
End If

End Sub
'===========================================
Sub DVDown()
Dim intDV As Integer
Dim ws As Worksheet
Dim c As Range
Dim rngList As Range

Set ws = Sheets("DVScroll")
Set c = ws.Range("B2")
Set rngList = ws.Range("MonthList")
intDV = 0
On Error Resume Next
intDV = Application.WorksheetFunction.Match(c.Value, rngList, 0)

If intDV = 0 Or intDV = 1 Then
c.Value = rngList.Cells(rngList.Rows.Count, 1)
Else
c.Value = rngList.Cells(intDV - 1, 1)
End If
End Sub

'========================

wrote:
I have created a pull down menu using Data: Validation to list several
gas wells. A chart on the sheet references the list so that the well's
production shows up on the chart when I select it from the list. Is
there a way to use a button to scroll through the list automatically so
that I do not have to select the list with the mouse each time I want
to change well? Thanks.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

Thanks so much for the reply. Your code worked great. I have had the
spreadsheet up and running for a week now but for some reason I started
having trouble today. The buttons I use to run DVUp and DVDown still
work as the well name changes but now my data does not change as the
well name changes. Each wells production is listed by day using a
vlookup on the name. Now when the name changes using DVUp or DVDown the
formula does not update itself unless I either 1) hit save or 2) hit F2
on each formula (LOTS of formulae). Any ideas why my formulas stopped
auto updating? I have never seen this before.

ToolsOptionsCalculation.

Are you set to Manual or Automatic?

If the calcs are taking place when you save, I would suggest the you are in
Manual mode with "recalculate before save" checked.


Gord Dibben Excel MVP

On 28 Jan 2005 08:00:50 -0800, wrote:

Debra,

Thanks so much for the reply. Your code worked great. I have had the
spreadsheet up and running for a week now but for some reason I started
having trouble today. The buttons I use to run DVUp and DVDown still
work as the well name changes but now my data does not change as the
well name changes. Each wells production is listed by day using a
vlookup on the name. Now when the name changes using DVUp or DVDown the
formula does not update itself unless I either 1) hit save or 2) hit F2
on each formula (LOTS of formulae). Any ideas why my formulas stopped
auto updating? I have never seen this before.