Excel vba commands for multi language of excel

[bosstec]

I'm developing excel in english language and writing commands in english, which is not my main language. I'm located in Denmark.

My problem:
All my formulas in the excel sheet is originally written with english commands, like SUM, IF, OR ect.
When i open the workbook in another language og Excel, all the commands will change to this language and excel understand the formulas. So far so good.

But when i write formulas (commands) to cells from vba, they are in english, because i wrote them like that in vba, they are not working anymore - excel don't recognice the commands...!

How can i work around this problem?

reg Frank

 

[shg]

Maybe use the FormulaLocal property?

 

[bosstec]

I already have the FormulaLocal in front of the string i write to the cell (red marked). But it still write the english commands "IF", "OR" ect
Is there some problem with my code?


Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
ActiveSheet.Unprotect Password:="password"
Application.EnableEvents = False
Dim formula As String
If Not Application.Intersect(Target, Range("P8:P16,P19:P27,P30:P38,P41:P49,P52:P60,P63:P71,P74:P82")) Is Nothing Then
With Target.Offset(, 1)
If Target.Value = "Vagtudkald" Then
.Value = ""
.Interior.Color = RGB(255, 255, 0)
.Locked = False
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Time24"
Else
.FormulaLocal = "=IF(OR(" & Target.Address(0, 0) & "="""";" & Target.Offset(-1, 2).Address(0, 0) & "="""");"""";" & Target.Offset(-1, 2).Address(0, 0) & ")"
.Interior.Color = RGB(217, 217, 217)
.Locked = True
.Validation.Delete
End If
End With
End If
Application.EnableEvents = True
ActiveSheet.Protect Password:="password"
End Sub