Just an add-on here.

If all you're looking for is a method to navigate sheets, don't forget the sheet
navigation arrows at bottom left.

You can right-click on one of those to get a pop-up of 15 sheet names and "more
sheets" to scroll through.


Gord

On Sat, 10 Nov 2007 16:11:24 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Add the worksheet names to a sheet and use that list for your DV list.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "List"
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
Set sheetrng = Range("A1", Cells(Rows.Count, ActiveCell.Column).End(xlUp)
sheetrng.Name = "mydvlist"
End Sub

Ib the DV list source use =mydvlist


Gord Dibben MS Excel MVP

On Sat, 10 Nov 2007 14:18:01 -0800, Paul Moles
wrote:

How do I get the Data Validation List (presuming I need to use "List" in the
"Allow" validation criteria ) to "pick" up the sheet names?

Thanks
Paul

"Gary''s Student" wrote:

Hi Paul:

You do not have to modify the code. Just add the validation drop-down to
the cell.
--
Gary''s Student - gsnu200755


"Paul Moles" wrote:

Is it possible to adjust this code to provide a drop box of the available
worksheets? Like with a data validation drop box.

Many Thanks

Paul Moles

"Gary''s Student" wrote:

First enter this macro in the worksheet event code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1")
If Intersect(Target, r) Is Nothing Then Exit Sub
v = r.Value
For Each ws In Worksheets
If ws.Name = v Then
ws.Activate
Exit Sub
End If
Next
End Sub

The macro looks at your entries in cell A1. If you enter a valid tab name
in A1, you will jump to the corresponding worksheet. If you happen to enter
text that is not a valid tabe name, then no jump is made.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm






--
Gary''s Student - gsnu200755


"Maggie" wrote:

Is there a way to input a date or word that would cause the worksheet to open
another worksheet? I am trying to be able to plug in a date and have the
date generate which sheet I should be using. This is for the purpose of
rotating my schedules.
--
Thanks, Maggie