I want to add the following data validation to a cell (in this case cell N3) using VBA.

=INDIRECT("Cabletype_"&MATCH(M3;Cabletype_Names)&"_Size")

Next to the Cell "N3" in Cell "M3" is the name of the Cable. The formula searches for the position in the array "Cabletype_Names". Let's say it's in the third position, so it would construct the Name "Cabletype_3_Size". And that's the list of all sizes that specific cable can be, which should be my validation.

This works if I use the validation Pop-Up-window. However, if I try to set the validation with VBA, I just cannot get it to work.

Here is my VBA code that doesn't work.

Formula_str = "=INDIRECT(""Cabletype_""&MATCH(M3;Cabletype_Names)&""_Size"")"
   
   With Worksheets(Tablenname).Columns(13).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
         xlEqual, Formula1:=Formula_str
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .errorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
   End With

If I switch the formula to something simple like just Formula_str = "=Cabletype_3_Size" the VBA-code works as well. So it has to do with the specific formula that I want to insert.

I want to add the following data validation to a cell (in this case cell N3) using VBA.

=INDIRECT("Cabletype_"&MATCH(M3;Cabletype_Names)&"_Size")

Next to the Cell "N3" in Cell "M3" is the name of the Cable. The formula searches for the position in the array "Cabletype_Names". Let's say it's in the third position, so it would construct the Name "Cabletype_3_Size". And that's the list of all sizes that specific cable can be, which should be my validation.

This works if I use the validation Pop-Up-window. However, if I try to set the validation with VBA, I just cannot get it to work.

Here is my VBA code that doesn't work.

Formula_str = "=INDIRECT(""Cabletype_""&MATCH(M3;Cabletype_Names)&""_Size"")"
   
   With Worksheets(Tablenname).Columns(13).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= _
         xlEqual, Formula1:=Formula_str
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .errorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
   End With

If I switch the formula to something simple like just Formula_str = "=Cabletype_3_Size" the VBA-code works as well. So it has to do with the specific formula that I want to insert.

• excel
• vba

• 1 What happens if you switch the ; to a ,? – BigBen Commented 4 hours ago
• It works after that change, cheers. See my other comment in this thread. – Nanski Commented 4 hours ago

1 Answer 1

The issue is due to the use of semicolons in your formula. Even if Excel (depending on your regional settings) accepts semicolons in worksheet formulas, VBA requires the formula string to use commas (,) as argument separators. Also, it's a good idea to specify the match type for the MATCH function (commonly 0 for an exact match).

Try changing your formula string to:

Formula_str = "=INDIRECT(""Cabletype_"" & MATCH(M3, Cabletype_Names, 0) & ""_Size"")"