get list of sheet in dropdown(datavalidation) not combobox "run time error438

[Ali M]

hello
I try getting list of sheets into dropdown (datavaldation ) I don't want using combobox, is it possible?
this is what I got but gives error object doesn't support this property

Sub n()
Dim ws As Worksheet
Dim  rng As Range
 Dim oSheet As Excel.Worksheet
'change Sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.Range("B1")
For Each oSheet In ActiveWorkbook.Sheets
With rng.Validation
    .Delete 'delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="='" & ws.Name & "'!" & oSheet
End With
Next
End Sub

so I want doing that by worksheet change event and make it dynamically when add new sheet or delete should update in datavalidation automatically
thanks

 

[Ali M]

this is the whole code

Sub n()
    Dim WS As Worksheet
    Dim rng As Range
    Dim ValidationList As String

    
    'Create the list for the validation menu
    For Each WS In ActiveWorkbook.Worksheets
        ValidationList = ValidationList & WS.Name & ","
    Next WS
    ValidationList = Left(ValidationList, Len(ValidationList) - 1)
    
    'define the validation cell range
    Set WS = ThisWorkbook.Worksheets("Sheet1")
    If WS.Name <> "Sheet1" Then

    Set rng = WS.Range("B1")
    
    'Update the validation
    With rng.Validation
        .Delete 'delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=ValidationList
    End With
    End If
End Sub

I don't automatically know what "doesn't work" means

should not include Sheet1 into dropdown

 

[rlv01]

You have placed If WS.Name <> "Sheet1" Then in the wrong location. Do this instead:

Sub n()
    Dim WS As Worksheet
    Dim rng As Range
    Dim ValidationList As String
    
    
    'Create the list for the validation menu
    For Each WS In ActiveWorkbook.Worksheets
        If WS.Name <> "Sheet1" Then
            ValidationList = ValidationList & WS.Name & ","
        End If
    Next WS
    ValidationList = Left(ValidationList, Len(ValidationList) - 1)
    
    'define the validation cell range
    Set WS = ThisWorkbook.Worksheets("Sheet1")
    Set rng = WS.Range("B1")
    
    'Update the validation
    With rng.Validation
        .Delete 'delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=ValidationList
    End With
End Sub

 

[Ali M]

new version is excellent !
many thanks for every thing