Forum Discussion
Range.Validation.Add problem with VBA
Hello ,
using the excel user interface, the following formula can be applied to a column.
- Userdefined
- formula: =IstZahl(A10)
This works properly.
I try to apply the same with VBA.
Dim formel as string
formel = "=IstZahl(A10)"
With column.Validation
.Delete
.Add Type:=xlValidateCustom, Operator:=xlEqual, Formula1:=formel, AlertStyle:=xlValidAlertStop
.IgnoreBlank = True
.InCellDropdown = False
.ErrorTitle = "Wert eingeben"
.InputMessage = ""
.ErrorMessage = "Der Wert darf keine Duplikate haben und muss eine Zahl sein."
.ShowInput = False
.ShowError = True
End With
I get allways an error when the red line is executed.
I guess that the syntax is not correct but I find no error.
Manfred
The issue with using Range.Validation.Add in VBA often comes down to the way Excel interprets formulas when they are used in VBA. Specifically, formulas in VBA should always be written using English function names and with absolute references when necessary. Additionally, using the right syntax for specifying the range for validation is crucial.
Here's an attempted version of your VBA code with a fix for adding a custom validation rule using ISNUMBER, the English equivalent of ActualNumber.
Dim formel As String formel = "=ISNUMBER(A10)" With Columns("A:A").Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:=formel .IgnoreBlank = True .InCellDropdown = False .ErrorTitle = "Wert eingeben" .InputMessage = "" .ErrorMessage = "Der Wert muss eine Zahl sein." .ShowInput = False .ShowError = True End WithThe VBA code is untested and serves as an example only, please backup your file in advance as a precaution.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
2 Replies
The issue with using Range.Validation.Add in VBA often comes down to the way Excel interprets formulas when they are used in VBA. Specifically, formulas in VBA should always be written using English function names and with absolute references when necessary. Additionally, using the right syntax for specifying the range for validation is crucial.
Here's an attempted version of your VBA code with a fix for adding a custom validation rule using ISNUMBER, the English equivalent of ActualNumber.
Dim formel As String formel = "=ISNUMBER(A10)" With Columns("A:A").Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:=formel .IgnoreBlank = True .InCellDropdown = False .ErrorTitle = "Wert eingeben" .InputMessage = "" .ErrorMessage = "Der Wert muss eine Zahl sein." .ShowInput = False .ShowError = True End WithThe VBA code is untested and serves as an example only, please backup your file in advance as a precaution.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- You are right,
using the englisch names is the solution.
Thank you very much for your help.
Manfred
