Adding Data Validation Via VBA Returns 1004 Error

[Bdra]

I thought this would be easy. I have set up 3 radio buttons on a worksheet. Corresponding to these are 3 named ranges on a separate sheet. As each radio button is selected, I am trying to make it change the data validation list in Range("D12") to the corresponding named range.

The range names are "Running" (for running expenses), "Bills", and "Debts".

Here is the code for the first radio button. The other two are identical, except for the range name:

Private Sub OptionButton1_Click()

    With Range("D12").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Running"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub

For some reason this breaks on the .Add line, with a run-time error 1004, "Application-defined or object-defined error."

The only suggestion I have been able to find, searching several forums, is that 1004 occurs when the name doesn't translate into a valid range. The ranges are defined dynamically, using the following formula (only column names differ between the three):

=OFFSET(Categories!$A$2,0,0,COUNTA(Categories!$A:$A)-1,1)

But I can add the validation lists manually without problems. I also tried substituting static named ranges and running the code, and it ran into the same error. So I am at a loss.

Any suggestions will be much appreciated.

 

[happyie]

Hi,

I am writing a function in excel vba to enforce users to input their gmail address. The following is the code. I always get a "run time error 1004' error message saying that "Application-defined or object-defined error". Please advise.


Sub CheckEmailAddress()
Range("I12").Activate
With ActiveCell.Validation
.Delete
.Add Type:=xlValidateCustom, Formula1:="=RIGHT(I12,9)="gmail.com"
'AlertStyle:=xlValidAlertStop, _
'.InputTitle = "Email Address"
'.ErrorTitle = "Error"
'.InputMessage = "Enter your email address (#####@gmail.com) to receive response."
'.ErrorMessage = "Please enter your gmail (e.g. #####@gmail.com)."
End With
End Sub

 

[Achintya Mittal]

Hi,

May be...

Range("d12").Activate
With ActiveCell.Validation
    .Delete
    .Add xlValidateList, Formula1:="=Running"
End With

Hi KrishnaKumar... I know this is a decade old post. But if you get a chance to see this... I just want to thank you. I was searching so long for a solution to this query and your solution... just worked..!! Cheers.

 

[GilouLeBoss]

Hi,

May be...

Range("d12").Activate
With ActiveCell.Validation
    .Delete
    .Add xlValidateList, Formula1:="=Running"
End With

Thank you so much, I've been trying for at leats 8 hours to solve this probleme. I create an account specificly to thank you 16 years after you posted the solution. Hope you see this and that your life as been gradualy better since 2008