PDA

View Full Version : [SOLVED] help with validation



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

mana
03-27-2017, 06:14 AM
>ActiveSheet.Protect UserInterfaceOnly:=True


This line should be moved to the last

werafa
03-27-2017, 01:30 PM
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
03-29-2017, 03:53 PM
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
03-29-2017, 04:01 PM
doh,

was unprotecting the wrong sheet :banghead:
(sheepish grin)

must go to bed more often

Aussiebear
03-29-2017, 07:48 PM
You wouldnt be the first to have done so. Glad you got it fixed