VBA automated replacing of "." to ","-CodePudding

I have a userform that insterts the value of TextBoxes into Cells of an Excel sheet. In case someone is typing e.g. "4.4" instead of "4,4", I want to automatically replace the dot with a comma. I am using the following code, but somehow nothing happens and the cell value still contains dots instead of commas.

Private Sub CmdFinish1_Click()
Worksheets("1").Range("C10").Value = UserForm.TextBox1.Value & "mm"

Range("C10").Select
Range("C10").Activate
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

UserForm.Hide
End Sub

CodePudding user response：

I'm not exactly sure why your code doesn't work but let's use pure VBA to make the replacement:

Private Sub CmdFinish1_Click()
    Worksheets("1").Range("C10").Value = UserForm.TextBox1.Value & "mm"
    Range("C10").Value = Replace(Range("C10").Value, ".", ",")
    UserForm.Hide
End Sub

CodePudding user response：

If this it is a locale thing as @VincentG suggested.

You could give the cell a custom number format of #,##0.00 "mm" - I'm not sure the correct format for your locality, but I chose one from those available and added "mm" on the end.

Your code can then be changed to:

Private Sub CommandButton1_Click()
    Worksheets("1").Range("C10").Value = Val(UserForm.TextBox1.Value)
End Sub

The VAL turns the text input into a numeric value. Will throw an error if you enter actual text though - so won't accept A for example.