Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default 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.

  #2   Report Post  
Paul B
 
Posts: n/a
Default

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.



  #3   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #4   Report Post  
 
Posts: n/a
Default

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
data validation and autocomplete -- again... Fred Excel Discussion (Misc queries) 4 July 29th 08 01:05 PM
Data Validation List =Name Pitbull Excel Discussion (Misc queries) 3 December 30th 04 07:43 PM
Selecting data from a list based on entered values GrantM Excel Discussion (Misc queries) 1 December 20th 04 11:59 AM
Excel2000: Custom data validation and named ranges Arvi Laanemets Excel Discussion (Misc queries) 9 December 10th 04 08:05 PM
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? Arvi Laanemets Excel Discussion (Misc queries) 0 December 2nd 04 12:29 PM


All times are GMT +1. The time now is 12:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"