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

<[email protected]> wrote in message
news:[email protected]...
>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 here:
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

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

[email protected] 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.

Tools>Options>Calculation.

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, [email protected] 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.

I have Excel 2007 and don't see where to assign the assign the DVUp code to the SpinUp event to my validation list.

TC

Hello tdsmith14, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.