Dropdown list from all worksheets name in a workbook

[DCG-jaeson]

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

 

[Chrisso]

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

 

[DCG-jaeson]

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

 

[Gord Dibben]

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

 

[DCG-jaeson]

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

 

[rumkus]

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 here:
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

 

[rumkus]

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

 

[eliano]

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





- Mostra testo citato -

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

 

[CellShocked]

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

 

[WallyWallWhackr]

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.

 

[Gord Dibben]

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

 

[DCG-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

 

[CellShocked]

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.

 

[WallyWallWhackr]

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.