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
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
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
VBA Code:
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
thanks