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

 

[rlv01]

One way.

Sub n()
    Dim WS As Worksheet
    Dim rng As Range
    Dim wsValidationListSheet As Worksheet
    Dim ValidationListRange As Range
    Dim I As Long
    
    Set wsValidationListSheet = Worksheets("Validation List") 'you must create a worksheet to hold the list of worksheets. This sheet can be hidden
    
    'Create the list of worksheets for the validation menu
    With wsValidationListSheet
        Application.Intersect(.UsedRange, .Columns("A:A")).ClearContents
        I = 1
        For Each WS In ActiveWorkbook.Worksheets
            .Cells(I, 1).Value = WS.Name
            I = I + 1
        Next WS
        Set ValidationListRange = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))             'alternate
    End With
    
    '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:="=" & ValidationListRange.Address(, , , True)
    End With
End Sub

 

[Ali M]

thanks for this solution , but how do that without using button . if I add new sheet it will add in datavalidation automatically without using button every time . last thing can do it without depends on list in column A ? just directly as in combobox without using helper column .

 

[rlv01]

One way would be to call it from the worksheet activate event.

Private Sub Worksheet_Activate()
   Call n
End Sub

 

[Ali M]

you don't answer me with this part

last thing can do it without depends on list in column A ? just directly as in combobox without using helper column .

is not it clear for you ?

 

[rlv01]

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")
    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]

fantastic ! may you exclude sheet1 from the dropdown , I try adding this condition

If WS.Name <> "sheet1" Then

after

    Set WS = ThisWorkbook.Worksheets("Sheet1")

but doesn't work , any idea?

 

[rlv01]

any idea?

If WS.Name <> "sheet1" Then

This comparison is case sensitive. Likely the actual sheet name is "Sheet1".

 

[Ali M]

actually I use copy the sheet name from tab and paste into the code but doesn't work .

 

[rlv01]

actually I use copy the sheet name from tab and paste into the code but doesn't work .

I don't automatically know what "doesn't work" means. Unless you explain and/or post the complete subroutine with your modifications, I cannot advise you.