How to add data validation from range with numeric columns VBA

Question

I want to add "data validation" including a drop down menu to some cells in a Workbook that uses numeric columns. I only managed to do it with alphabetical columns.

When I execute the following code I get the error message "1004" and the macro stops at the line with the Formula1 (excel settings to numerical columns):

Sub foo_numeric()
  Dim rngTest As Range 'Range where the Data validation should be applied
  Dim wsTest As Worksheet

  Set wsTest = Worksheets("WorkSheetName")
  Set rngTest = wsTest.Range(wsTest.Cells(1, 2), wsTest.Cells(5, 2))

  With rngTest.Validation
    .Delete
    .Add Type:=xlValidateList, _
       Formula1:="=WorkSheetName!Z1S1:Z22S1"
  End With
End Sub

When I switch the settings to alphabetical columns and execute the following code, I get no error message and the macro works smoothly:

Sub foo_alphabetical()
    Dim rngTest As Range 'Range where the Data validation should be applied
    Dim wsTest As Worksheet

    Set wsTest = Worksheets("WorkSheetName")
    Set rngTest = wsTest.Range("B1:B5")

    With rngTest.Validation
     .Delete
     .Add Type:=xlValidateList, _
         Formula1:="=WorkSheetName!$A$1:$A$22"
    End With    
End Sub

Is there some error in my code that I missed? How can I use data validation with numerical columns?

Remark: To put a String with the possible options into Formula1 is not an option as the string would be too big.

wsTest.Range(wsTest.Cells(1, 2), wsTest.Cells(5, 2)) - you need to add the worksheet to both uses of Cells(). Also, did you change it for ease of reading here on SO, or is the sheet literally called WorkSheetName? — BruceWayne
– BruceWayne, Commented Mar 13, 2019 at 15:22
Thanks, I forgot to add the worksheet to both uses of Cells(). I edited it, but it doesn't change anything about my problem. In my minimal example (that I also tested), the worksheet is literally called "WorkSheetName", but I get the same error with any other name. — julian
– julian, Commented Mar 13, 2019 at 16:22
First, what do you mean by a range of Z1S1:Z2S51?? Also, for a quick test, can you Dim rngFormulaTest as Range // Set rngFormulaTest = Worksheets("WorkSheetName").Range("A1:A22") // ... // Formula1:= rngFormulaTest. Does that throw an error? — BruceWayne
– BruceWayne, Commented Mar 13, 2019 at 16:28
Thanks to your questions I found the source of the problem. Thanks! The Format of Formula1 needs to be a string and can't be just the Range object. (stackoverflow.com/questions/22956604/…) — julian
– julian, Commented Mar 13, 2019 at 17:24
I believe Formula1 can be a range object. However, glad to see it was solved. You needed it in R1C1 format, which is what you noticed when "translating" it from German. — BruceWayne
– BruceWayne, Commented Mar 13, 2019 at 17:30

julian · Accepted Answer · 2019-03-13 17:20:44Z

0

Thanks to the questions of @BruceWayne I was able to solve my issue.

Solution:

Replace

Formula1:="=WorkSheetName!Z1S1:Z22S1"

with

Formula1:="=WorkSheetName!R1C1:R22C1"

Source of my Problem:

My office package is in German, so when I reference to Rows and Columns in excel, I reference to Zeilen and Spalten. That's what Z1S1:Z2S51 means. By changing my office package to English I figured out how to reference properly to numerical Columns.

Remark: Range.Adress always gives me alphabetical columns back.

answered Mar 13, 2019 at 17:20

julian

31 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add data validation from range with numeric columns VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related