Search code examples
excelvbainternationalizationuserform

Userform Error in Excel When updating record with decimals

I've written a userform that edits records from an Excel table. 

Private Sub UserForm_Initialize()
Dim ActiveRow As Long
ActiveRow = ActiveCell.Row
...
Me.Controls("exchangeRate").Value = Cells(ActiveRow, 21)

In the userform the value shows up as expected. We copy this value from our bank, and there for receive a long value with lots of decimal places.

Example:

10,8116705323301

So the userform shows this value, no problem. But when i save this value again, the comma is removed.

108116705323301

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("sheet").Unprotect Password:="xxx"
Dim ActiveRow As Long
ActiveRow = ActiveCell.Row
   Cells(ActiveRow, 21).Value = exchangeRate.Value
Application.ScreenUpdating = True
Sheets("sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, Password:="xxx"

What am I doing wrong?

Solution

  • TextBox values are strings

    There are some points causing your issue due to your local number settings (deviating from VBA's number formatting):

    1. Apparently your Application.DecimalSeparator equals ► , and not the defaulting comma . as used in VBA (in many middle European countries the default comma is represented by a column ,, whereas the local Application.ThousandsSeparator is a point .).
    2. Initially you are assigning a cell value of double type to a textbox only capable to hold text. VBA prefers to convert this number (VBA internal value: 10.8116705323301) to a locally formatted string displayed as 10,8116705323301.
    3. Writing a basically numeric string back to a cell will be interpreted as number, but following the English default settings. If there appears an English ThousandsSeparator (i.e. a colon ,) in a numeric stringthis will be neglected as unnecessary deco, thus resulting in a cell value of 108116705323301 (assuming a local numberformat of "0").

    Remedy

    As I suppose you intend to continue displaying the value in your local format within Userform, you'll have to convert the control value to a number (double type). If you are sure that your Application.DecimalSeparator equals "," and not to the English colon, you could recode your CommandButton1_Click() event as follow:

        ThisWorkbook.WorkSheets("sheet").Cells(activeRow, 21).Value = Val(Replace(Me.TextBox1, "," , "."))

    BTW Always use fully qualified range references; note that it would even preferrable to use a sheet's CodeName instead it's normal sheet name that could be overwritten too easily by other users.