PDA

View Full Version : Solved: Another format numeber issue



ioncila
12-29-2009, 06:41 PM
Hi
Sorry for the insistence, but can't solve another issue.

I multiply the values of 2 textboxes and the result would be paste in a sheet cell. This is done well.

...
Sub Totalizar()
txtNEWEXIST.Text = Val(txtEXIST) + Val(txtENTR) - Val(txtSAID)
txtVALTOTAL.Text = txtNEWEXIST.Value * txtPRUNIT.Value
'Format
txtPRUNIT.Value = Format(txtPRUNIT.Value, "#,###.00 €")
txtVALTOTAL.Value = Format(txtVALTOTAL.Value, "#,###.00 €")
End Sub

Private Sub txtENTR_AfterUpdate()
If txtENTR.Value = "" Then
txtENTR.Value = "0"
End If
Totalizar
End Sub
Private Sub txtPRUNIT_AfterUpdate()
If txtPRUNIT.Value = "" Then
txtPRECOUNIT.Value = "0"
End If
Totalizar
End Sub
Private Sub txtSAID_AfterUpdate()
If txtSAID.Value = "" Then
txtSAID.Value = "0"
End If
Totalizar
End Sub
Private Sub txtEXIST_AfterUpdate()
If txtEXIST.Value = "" Then
txtEXIST.Value = "0"
End If
Totalizar
End Sub
Private Sub txtNEWEXIST_AfterUpdate()
If txtNEWEXIST.Value = "" Then
txtNEWEXIST.Value = "0"
End If
Totalizar
End Sub
...

My problem is that the pasted value has a text format and it would be a number format. The sheet column is setup for number format but the values always are shown in text format.

How do I fix that?

Thanks

mikerickson
12-29-2009, 07:32 PM
I don't see where you are putting the values in the cells, but textboxes contain text values that (when put in a cell) are still text values.

Something like this in the "put in cell" routine should work.
Range("A1").Value = Val(txtVALTOTAL)
Range("A1").NumberFormat = "#,###.00 €"

The second line might be optional depending on if A1 always has the proper formatting.

ioncila
12-30-2009, 04:08 AM
Thank you very much for the help.
It almost fixed that issue. Vaues are numbers in cells now.
But it created another problem that I cant solve:

Example:

If TextBox1 = 6 and TextBox2 = 40,66 € result should be

6 * 40,66 € = 243,96 €. But It shows 243,00 €

Which part in "#,###.00 €" is wrong?

Bob Phillips
12-30-2009, 05:03 AM
For you, maybe



With Range("A1")

.Value = Val(txtVALTOTAL.Text)
.NumberFormat = "#.###,00 €"
End With

ioncila
12-30-2009, 05:25 AM
Sorry cant see the difference

Sorry again. Its ".Text".

Thanks

Bob Phillips
12-30-2009, 05:35 AM
No, it is the format that is the relevant change.

mikerickson
12-30-2009, 06:45 AM
The currency symbol makes the textboxs' contents string that can't be multiplited, using Val will turn the textbox strings into numbers
txtVALTOTAL.Text = Val(txtNEWEXIST.Value) * Val(txtPRUNIT.Value)

ioncila
12-31-2009, 08:57 AM
Thank you all. Its solved.

Happy New Year.
Ioncila