Excel - VBA decimal seperator. Comma or Point

[peioz]

I cannot get my hands on it.

In Excel the situation is as follows :
the comma is my decimal seperator (ie : 0,5)
On my numerical pad, the point-key is interpreted as a comma as well.

In VBA the situation is as follows :
On my numerical pad, the point-key is interpreted as a point.
In a form I have a textbox where worked hours have to be put in.
These can be 1 hour , 1,25 hours etc.
When I put the textbox-value of let's say 1,5 hours in a cell (By clicking the OKAY button on my user form)
Excel does not recognize 1,5 as a number. I have to manually go to the cell and give it an "edit and return" without changing the value. Only then excel sees it as 1,5 hours.

When I put 1.25 hours in my textbox excel recognizes it as 1,25 hours.

I have also a spinner box that adds every time 0,25 hours to the textbox. (I divide the spinner-box value by 4).
But the spinner box returns the value as 0,25.

So my spinner box uses the comma notation as decimal seperator.
the rest of vba uses a point......
and excel uses a comma.

I am completely lost.

 

[Andrew Poulsom]

See if something like this works for you:

Private Sub CommandButton1_Click()
    Dim Temp As String
    Temp = WorksheetFunction.Substitute(TextBox1.Text, ",", ".")
    Range("A1").Value = Val(Temp)
End Sub

 

[fairwinds]

Hi,

Personally I have found I get as little problem as possible by:

(Control panel - regional options)

Keeping my regional settings as Swedish in the general tab. in order to have time and date etc as I'm used to (as it agffects all windows programs) but:

In Numbers tab, change decimal from , to . and list separator from ; to ,

(When using the numerical pad, it "understands" this change and gives . insted of ,)

 

[peioz]

thanks for replying.

Changing my windows settings concerning comma's and points will be difficult. Because then I should do that company-wide.

I am now using office 2000. Is this an issue that is solved with the office XP / 2003 version ?

 

[capmar]

See if something like this works for you:

Private Sub CommandButton1_Click()
    Dim Temp As String
    Temp = WorksheetFunction.Substitute(TextBox1.Text, ",", ".")
    Range("A1").Value = Val(Temp)
End Sub

Thank you!
This code helped me a lot!

 

[dmlv_]

The region in my case was the problem.