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.
Code:
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
Bookmarks