Excel Data Validation .Add 1004 Error

Question

Hi!

I have been experiencing 1004 Errors when adding a validation list to a cell. I have tried the following codes:

Public masterstepsrows As Integer

private sub ABCFunction()

With Worksheets("Min Cost").Range("B10").Validation

    .Delete

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

    xlBetween, Formula1:="='Step Master'!R2C2:R" & masterstepsrows & "C2"   

    .IgnoreBlank = True

    .InCellDropdown = True

    .InputTitle = ""

    .ErrorTitle = ""

    .InputMessage = ""

    .ErrorMessage = ""

    .ShowInput = True

    .ShowError = True

End With

With Worksheets("Min Cost").Range("B10").Validation

    .Delete

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

    xlBetween, Formula1:="='Step Master'!C2:C" & masterstepsrows  

    .IgnoreBlank = True

    .InCellDropdown = True

    .InputTitle = ""

    .ErrorTitle = ""

    .InputMessage = ""

    .ErrorMessage = ""

    .ShowInput = True

    .ShowError = True

End With

end sub

The second version sometimes works but it doesn't after I have reopened the Excel file.

Can someone please help me?

Answer 1

Interesting one.

As you may know, Excel has two notations for addresses: A1 and R1C1.

Cell B3 would be R3C2 in R1C1 notation.

You can switch Excel between those two notations on File > Options > Formulas > R1C1 reference style

Your first version uses R1C1 notation for the formula and only works when Excel is in R1C1 reference style; the second uses A1 notation and only works when Excel is in A1 reference style.

This looks like bad design to me, and I will report it to the Excel product team.

To have the macro work in whichever reference style the user has chosen (assuming the list is in column C):

With Worksheets("Min Cost").Range("B10").Validation

  .Delete

  If Application.ReferenceStyle = xlR1C1 Then

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

       xlBetween, Formula1:="='Step Master'!R2C3:R" & masterstepsrows & "C3"

  Else

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

       xlBetween, Formula1:="='Step Master'!C2:C" & masterstepsrows

  End If

  .IgnoreBlank = True

  .InCellDropdown = True

  .InputTitle = ""

  .ErrorTitle = ""

  .InputMessage = ""

  .ErrorMessage = ""

  .ShowInput = True

  .ShowError = True

End With

Hi Bill!

Thanks for the help! it turns out that I forgot to unprotect the worksheet in the end.

:S

Answer 2

Interesting one.

As you may know, Excel has two notations for addresses: A1 and R1C1.

Cell B3 would be R3C2 in R1C1 notation.

You can switch Excel between those two notations on File > Options > Formulas > R1C1 reference style

Your first version uses R1C1 notation for the formula and only works when Excel is in R1C1 reference style; the second uses A1 notation and only works when Excel is in A1 reference style.

This looks like bad design to me, and I will report it to the Excel product team.

To have the macro work in whichever reference style the user has chosen (assuming the list is in column C):

With Worksheets("Min Cost").Range("B10").Validation

  .Delete

  If Application.ReferenceStyle = xlR1C1 Then

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

       xlBetween, Formula1:="='Step Master'!R2C3:R" & masterstepsrows & "C3"

  Else

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

       xlBetween, Formula1:="='Step Master'!C2:C" & masterstepsrows

  End If

  .IgnoreBlank = True

  .InCellDropdown = True

  .InputTitle = ""

  .ErrorTitle = ""

  .InputMessage = ""

  .ErrorMessage = ""

  .ShowInput = True

  .ShowError = True

End With