I used this macro:
Sub test3()
Dim x As String
On Error Resume Next
x = Range("$A$1:$A$10").Validation.Formula1
If Err.Number = 0 Then
'validation exists
Range("$a$1:$a$10").Validation.Delete
End If
On Error GoTo 0
Range("$a$1:$a$10").Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "=$c$1:$c$5"
End Sub
It works fine until a user moves one of the cells between A1 and A10 to somewhere outside the A1:A10 range; leaving the cell without a drop down list. I would run the above macro again; hoping to generate a drop down list to replace the moved cell, and that's when the runtime 1004 error appears.
In short, I am trying to make A1 to A10 maintain dropdown lists even if one of the cells gets moved. Maybe by automatically replacing the empty cell with a new dropdown list. Any ideas? Thanks in advance.
Sub test3()
Dim x As String
On Error Resume Next
x = Range("$A$1:$A$10").Validation.Formula1
If Err.Number = 0 Then
'validation exists
Range("$a$1:$a$10").Validation.Delete
End If
On Error GoTo 0
Range("$a$1:$a$10").Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "=$c$1:$c$5"
End Sub
It works fine until a user moves one of the cells between A1 and A10 to somewhere outside the A1:A10 range; leaving the cell without a drop down list. I would run the above macro again; hoping to generate a drop down list to replace the moved cell, and that's when the runtime 1004 error appears.
In short, I am trying to make A1 to A10 maintain dropdown lists even if one of the cells gets moved. Maybe by automatically replacing the empty cell with a new dropdown list. Any ideas? Thanks in advance.