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.

 

[xld]

I cannot reproduce the error, either on the same sheet or another, with Running defined as you show.

Update, I did get it if I had an empty range. If you do that manually, you get asked if you want to continue, VBA doesn't offer the option.

 

[Krishnakumar]

Hi,

May be...

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

 

[Bdra]

xld, thanks for trying. I thought this was a helpful hint that the problem might somehow be with my system setup. But then ...

Kris, your suggestion resolved it. That worked!

That is just bizarre to me. It shouldn't be necessary to activate the cell. If you don't mind my asking, how did you come up with that?

 

[Krishnakumar]

If you don't mind my asking, how did you come up with that?

Journey through this site for the last 5 years

 

[goqsane]

Hello.

I have quite a different problem. I'm manipulating an excel sheet from within VBA in Outlook.

I will give you an example of what works and what does not work:

oApp btw is:

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.UserControl = True
oApp.Workbooks.Open "C:\Reports\ECC.xlsx", False, False

    oApp.ActiveSheet.Cells(1, 14) = "IM Comments"
    
    
    oApp.ActiveSheet.Range("P1:P8").Select
    
    With oApp.Selection
        .Font.ThemeColor = 1
        .Font.TintAndShade = 0
    End With

This works well.

However, Validation is a different story. Tried almost anything possible. I keep getting 1004 on the .Add line.

oApp.ActiveSheet.Range("N2:N82").Select
          
    With oApp.Selection.Validation
    
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$P$2:$P$10"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    
    End With

Any help would be MUCH appreciated..

Thank you!

 

[Bdra]

Have you tried looping through the range and adding data validation to one cell at a time?

 

[goqsane]

Thanks Bdra, that wouldn't work though.

The problem was: MISSING LIBRARY. Outlook VBA did not have Excel 12.0 library ticked. That simple hack solved the problem.

Might be something to look at in the future.

 

[KL_10024]

Hi All,

I resolved this problem by creating a new workbook and copying same vba code into it. I can only assume original workbook had become corrupt in some way. Here is the code (Excel 2003):

'Target' passed in as range:

'*** Delete validation in cell
Target.Validation.Delete

'*** Assign range to variable
strAddressNo = ActiveCell.Offset(0, -1).Address

'*** Concatenate string to use for data validation
strString = "=INDIRECT(" & strAddressNo & ")"

'*** Add validation
Target.Validation.Add Type:=xlValidateList, _
Formula1:=strString

 

[KL_10024]

Another factor that seems to matter is the range reference style. This must be set to how the data validation was created. So in my case it was not a corrupt worksheet but reference style. So if validation is setup using R1C1 notation then this must be ticked in tools=>options general tab or unticked if using letter and number ref ("A:1").