Runtime Error 1004 Question

[Armored Wing]

Hi all, I am working on a project currently where I need to have 5 check boxes linked to a macro of different document type. However when i assigned each check box a macro and click each one. I receive a Runtime Error "1004" object defined or application defined error as it reference the line "ActiveSheet.Range ("B70:H70").Validation.Add xlValidateList, , , "=$N$2:$N$99" as the problem. My initial guess is that the range selected for this is too big but I am not quite sure the problem is......

Sub CheckBox_Financial_Statement_Annual_Click()
Dim PassVar As String
PassVar = "test"
ActiveSheet.Unprotect PassVar
If ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn Then
ActiveSheet.Range("A70:H74").UnMerge
ActiveSheet.Range("A70").Value = "Enter Multiple GCIF below and Select appropriate Doc Type from drop down list"
ActiveSheet.Range("A71").Value = "Financial Statement - Annual"
ActiveSheet.Range("B70:H70").Value = "Select Doc Type from drop down list"
ActiveSheet.Range("B71:H74").Validation.Add xlValidateList, , , "=$N$2:$N$99"
Else
ActiveSheet.Range("A70:H74").Value = Null
ActiveSheet.Range("A70:H74").Merge (True)
ActiveSheet.Range("A70").Value = "Comments"
End If
ActiveSheet.Range("A70:H74").Locked = False
ActiveSheet.Protect PassVar, True, True, True
End Sub

[tigeravatar]

Armored Wing,

In the future please use Code tags (and not Quote tags) to surround code (see Forum Rules link in my sig for how).
As to your question, its becuase you're trying to overwrite existing data validation without clearing the old stuff. Added a .Delete line to your code, give it a try:

Please Login or Register  to view this content.