werafa
03-27-2017, 05:18 AM
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
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