Team,

How can I automatically use the Worksheets name in to a Dropdown list
(Validation). Can I just make it with a excel formula or need a Macro?

I were using Excel 2000.

Thanks in advance,
~jaeson

On 25 Aug, 17:25, DCG-jaeson wrote:
Team,

How can I automatically use the Worksheets name in to a Dropdown list
(Validation). Can I just make it with a excel formula or need a Macro?

I were using Excel 2000.

Thanks in advance,
~jaeson

A macro would be required - here is a stub:

Sub List_Worksheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Debug.Print ws.Name
' add to your data validation list here
Next ws
End Sub

Hi Crisso,

Thanks for the concern, this look great...But the line " ' add to
your data validation list here " confuse me what to do. Maybe this
will clear my problem, I need to put a list in Cell A1 Dropdown all
the WorkSheets name.

Thanks again,
~jaeson

Run this macro which adds a new sheet named "List".

All existing sheetnames will be listed in column A in this new sheet.

Use this list as the source for your DV dropdown.

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
If Sheet.Name < "List" Then
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub


Gord Dibben MS Excel MVP


On Wed, 25 Aug 2010 09:47:03 -0700 (PDT), DCG-jaeson
wrote:

Hi Crisso,

Thanks for the concern, this look great...But the line " ' add to
your data validation list here " confuse me what to do. Maybe this
will clear my problem, I need to put a list in Cell A1 Dropdown all
the WorkSheets name.

Thanks again,
~jaeson

There's no Macro to run in your Codes Sir Gord, but you give me an
idea. Well thanks then for your help.

There's no Macro to run in your Codes Sir Gord
-----------------------------------------------------------------------------
Please place Gord's below macro in a module and run.
How to place codes to a workbook is explained in detail he
http://www.contextures.com/xlvba01.html
Rgds
-------------------------------------------------------------------------------

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
If Sheet.Name < "List" Then
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub

Change
Private Sub ListSheets()
To
Public Sub ListSheets()

In order to see it in macro list if you don't know how to run it
within code window.
Rgds

On 26 Ago, 05:01, Gord Dibben wrote:
Run this macro which adds a new sheet named "List".

All existing sheetnames will be listed in column A in this new sheet.

Use this list as the source for your DV dropdown.

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
* * * * *If Sheet.Name < "List" Then
* * * rng.Offset(i, 0).Value = Sheet.Name
* * * i = i + 1
* * * End If
* Next Sheet
End Sub

Gord Dibben * * MS Excel MVP

On Wed, 25 Aug 2010 09:47:03 -0700 (PDT), DCG-jaeson
wrote:



Hi Crisso,

Thanks for the concern, this look great...But the line " * ' add to
your data validation list here *" confuse me what to do. Maybe this
will clear my problem, I need to put a list in Cell A1 Dropdown all
the WorkSheets name.

Thanks again,
~jaeson- Nascondi testo citato

- Mostra testo citato -

Hi Sir Gord.
When a man with a toothpick meets a man with a gun......:-))
Eliano

Is there not a way to simply apply the sheet names to a columnar cell
array, name it, and make a validation criteria call to the named range?

Then, all one would have to do is maintain the sheet name list.

No VB required


On Wed, 25 Aug 2010 20:01:00 -0700, Gord Dibben wrote:

Run this macro which adds a new sheet named "List".

All existing sheetnames will be listed in column A in this new sheet.

Use this list as the source for your DV dropdown.

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
If Sheet.Name < "List" Then
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub


Gord Dibben MS Excel MVP


On Wed, 25 Aug 2010 09:47:03 -0700 (PDT), DCG-jaeson
wrote:

Hi Crisso,

Thanks for the concern, this look great...But the line " ' add to
your data validation list here " confuse me what to do. Maybe this
will clear my problem, I need to put a list in Cell A1 Dropdown all
the WorkSheets name.

Thanks again,
~jaeson

On Aug 25, 9:25*am, DCG-jaeson wrote:
Team,

How can I automatically use the Worksheets name in to a Dropdown list
(Validation). Can I just make it with a excel formula or need a Macro?

I were using Excel 2000.

Thanks in advance,
~jaeson

I have a dynamic drop down in my time sheet workbook. No macros.

It is on the MS user submitted templates site.

http://tiny.cc/haj2z

Note how the time selection drop downs in the time sheet pages rely
on the listings on the info data sheet, and the selections made there.

I made it so a person could select whichever time increment he or
she wants to track his or her time in.

It is not exactly what you are after, but if you make a list of your
sheet names yourself, you can name that range and us it in a drop down
list.

Note also that I opted for a hyperlink list for my sheet tabs, which
allows one to simply pick a sheet tab name in the list and jump to it
from the hyperlink click.

Most assuredly one could manually type the sheetnames and name that range.

If I had a multitude of sheetnames I would prefer an automatic solution.


Gord


On Fri, 27 Aug 2010 08:41:02 -0700, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:

Is there not a way to simply apply the sheet names to a columnar cell
array, name it, and make a validation criteria call to the named range?

Then, all one would have to do is maintain the sheet name list.

No VB required


On Wed, 25 Aug 2010 20:01:00 -0700, Gord Dibben wrote:

Run this macro which adds a new sheet named "List".

All existing sheetnames will be listed in column A in this new sheet.

Use this list as the source for your DV dropdown.

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
If Sheet.Name < "List" Then
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub


Gord Dibben MS Excel MVP


On Wed, 25 Aug 2010 09:47:03 -0700 (PDT), DCG-jaeson
wrote:

Hi Crisso,

Thanks for the concern, this look great...But the line " ' add to
your data validation list here " confuse me what to do. Maybe this
will clear my problem, I need to put a list in Cell A1 Dropdown all
the WorkSheets name.

Thanks again,
~jaeson

Thanks to all of you guys especially sir Gord and Rumkus,

The Code below is well working, i Just remove the Line that adding
Sheet named "List", due once if you run it for the 2nd Time it will
error due it has already a "list" sheet in the workbook.

Public Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name < "List" Then
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub


This code you provided was a huge help for me.

Thanks Thanks,
~jaeson

Then all one needs is the script to create the list and name the range.

No script required to actually use a named range as a validation
element.



On Fri, 27 Aug 2010 09:11:48 -0700, Gord Dibben wrote:

Most assuredly one could manually type the sheetnames and name that range.

If I had a multitude of sheetnames I would prefer an automatic solution.


Gord


On Fri, 27 Aug 2010 08:41:02 -0700, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet .org wrote:

Is there not a way to simply apply the sheet names to a columnar cell
array, name it, and make a validation criteria call to the named range?

Then, all one would have to do is maintain the sheet name list.

No VB required


On Wed, 25 Aug 2010 20:01:00 -0700, Gord Dibben wrote:

Run this macro which adds a new sheet named "List".

All existing sheetnames will be listed in column A in this new sheet.

Use this list as the source for your DV dropdown.

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
If Sheet.Name < "List" Then
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub


Gord Dibben MS Excel MVP


On Wed, 25 Aug 2010 09:47:03 -0700 (PDT), DCG-jaeson
wrote:

Hi Crisso,

Thanks for the concern, this look great...But the line " ' add to
your data validation list here " confuse me what to do. Maybe this
will clear my problem, I need to put a list in Cell A1 Dropdown all
the WorkSheets name.

Thanks again,
~jaeson

Right result. Not nec right approach.

The sheet (or list location on an existing sheet) should/can be already
created. No need to automate that (or error code for it). The script
would then only need to maintain the sheet list compilation code and
named range that refers to it.

After that, your standard validation list selection dialog works by
entering =RangeName where "RangeName" is your name for the
range.("list") into the list dialog box.

This makes any assigned validation list able to be dynamic, because it
will always ONLY refer to whatever is contained in the named range as
long as that range is only a single column reference.

On Fri, 27 Aug 2010 11:23:33 -0700 (PDT), DCG-jaeson
wrote:

Thanks to all of you guys especially sir Gord and Rumkus,

The Code below is well working, i Just remove the Line that adding
Sheet named "List", due once if you run it for the 2nd Time it will
error due it has already a "list" sheet in the workbook.

Public Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name < "List" Then
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub


This code you provided was a huge help for me.

Thanks Thanks,
~jaeson