Data Validation With Dynamic List Of Sheet Names

[TomyLee]

Hi,

Intro cell with data validation I want to have all the sheet names (except the first sheet), but dynamic. I mean if you add or delete a worksheet, I want to see this in cell with data validation.

Example:
I have 10 sheets in my file (Sheet1 to sheet 10) in cell M3 is data validation and there we can choose any piece between 2 and 10. If you add 2 sheets in M3 can choose between sheet 2 and 12.
Conversely if I delete 5 sheets in M3 I wish I could choose between sheet 2 to 5.

How can I do this? Thanks.

[Mike H]

Hi,

Right click Sheet 1 tab, view code and paste this code in on the right. I have used Z2:Zn to build the dynamic list of sheet names and this list is re-created every time you click in M3 of sheet 1. If you want to use a different range then ensure you start in Row 2 of which ever column you choose.



Private Sub Worksheet_Activate()
If ActiveCell.Address = "$M$3" Then
ActiveCell.Offset(-1).Select
End If
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Long
If Target.Address <> "$M$3" Or Target.Cells.Count > 1 Then Exit Sub
For x = 2 To Worksheets.Count
Cells(x, "Z") = Sheets(x).Name
Next
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Z$2:$Z$" & x - 1
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

[TomyLee]

Mike, thank you very much.
Have a good day.