help with validation

[werafa]

Hi all,

am having a problem with a routine that is supposed to set some validation.
can anyone spot my error?
thanks
Werafa

Sub RoleValidation()
'set/reset validation in Data Entry column D
Dim myRange As Range
Dim myString As String


    Set myRange = Worksheets("PM List").Range("H3:H5")
    myString = ValidationRange(myRange)
    Set myRange = Worksheets("Data Entry").Range("D:D")


    ActiveSheet.Protect UserInterfaceOnly:=True
    With myRange.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=myString 'fails here
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = False
        .ShowError = False
    End With
    
    Set myRange = Worksheets("Data Entry").Range("D1:D10")
    With myRange.Validation
        .Delete
    End With
End Sub

Function ValidationRange(myRange As Range) As String
' writes the formula1 string for validation routines
'Dim myCell As Range
Dim myString As String
Dim wsName As String
Dim rAddress As String


    wsName = myRange.Parent.Name
    rAddress = myRange.Address
    myString = "= '" & wsName & "'!" & rAddress
    
ValidationRange = myString
End Function

[mana]

＞ActiveSheet.Protect UserInterfaceOnly:=True


This line should be moved to the last

[werafa]

the worksheet is already protected, and must be unprotected for the vba to run cleanly.
this removes (or is supposed to remove) sheet protection for vba

but you are correct - it is the sheet protection that is causing the problem despite my attempt to deal with it.
thanks for the clue

[werafa]

this is getting old quickly
I had it working, and now it has stopped working again ....

Mana's comment was correct - the worksheet protection was still blocking the validation.
and it still is (as far as I can tell), despite the sheet.unprotect line.
It continues to fail on the validation.add line

can anyone save me from further hair loss?

Sub RoleValidation()
     'set/reset validation in Data Entry column D
     ' used to manage transition from early model (V3x) workbooks in which column D validation did not exist
    Dim myRange As Range
    Dim myString As String
    Dim mySheet As Worksheet
    
    ' get formula1 string
    Set mySheet = ThisWorkbook.Worksheets("PM List")
    Set myRange = mySheet.Range("H3:H5")
    myString = ValidationRange(myRange)
    
    'reset validation
    Set myRange = Worksheets("Data Entry").Range("D:D")
    mySheet.Unprotect
    
    With myRange.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=myString
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = False
        .ShowError = False
    End With
     
    Set myRange = Worksheets("Data Entry").Range("D1:D10")
    With myRange.Validation
        .Delete
    End With
    mySheet.Protect
End Sub

[werafa]

doh,

was unprotecting the wrong sheet
(sheepish grin)

must go to bed more often

[Aussiebear]

You wouldnt be the first to have done so. Glad you got it fixed