How to write textbox entries (values with more than two decimals) to Excel cells? 
Author Message
 How to write textbox entries (values with more than two decimals) to Excel cells?

While writing data from a userform (VBA) to an excel worksheet I
receive confusing results depending on the formats and conversions
used.

Example 1 (using "," as separator for decimals):
I enter 12,34567890 to a textbox and use the VBA-line
<worksheet>.Cells(x, y).Value = Format(CSng(TextBox1.Value), "0.00")
to write the value to the desired cell.
The result is 12,35 (which is what i expected)

Example 2:
I enter 12,34567890 to a textbox and:
<worksheet>.Cells(x, y).Value = Format(CSng(TextBox1.Value), "0.0000")
The result is 123457,0000 (I expected 12,3457 instead)

Example 3 (without format()):
I enter 12,34567890 to a textbox and:
<worksheet>.Cells(x, y).Value = CSng(TextBox1.Value)
The result is 12,34567928 (I expected 12,3456789 instead)

I would be very glad if somebody could explain these effects and give
solutions.



Thu, 08 Jul 2004 04:36:52 GMT  
 How to write textbox entries (values with more than two decimals) to Excel cells?
Hi HJRigel,

You have to format cell. Do this:

Example 2:
I enter 12,34567890 to a textbox and:
<worksheet>.Cells(x, y).NumberFormat = "0.0000"
<worksheet>.Cells(x, y).Value = CSng(TextBox1.Value)

HTH



Quote:
> While writing data from a userform (VBA) to an excel worksheet I
> receive confusing results depending on the formats and conversions
> used.

> Example 1 (using "," as separator for decimals):
> I enter 12,34567890 to a textbox and use the VBA-line
> <worksheet>.Cells(x, y).Value = Format(CSng(TextBox1.Value), "0.00")
> to write the value to the desired cell.
> The result is 12,35 (which is what i expected)

> Example 2:
> I enter 12,34567890 to a textbox and:
> <worksheet>.Cells(x, y).Value = Format(CSng(TextBox1.Value), "0.0000")
> The result is 123457,0000 (I expected 12,3457 instead)

> Example 3 (without format()):
> I enter 12,34567890 to a textbox and:
> <worksheet>.Cells(x, y).Value = CSng(TextBox1.Value)
> The result is 12,34567928 (I expected 12,3456789 instead)

> I would be very glad if somebody could explain these effects and give
> solutions.



Thu, 08 Jul 2004 05:56:45 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. write cell value to textbox

2. write cell value to textbox

3. Combining Two Textbox Values into a Third Textbox

4. getting values form two cells so both values show in one cell

5. Return currency value from textbox to an excel cell

6. How To put The textbox value in the cell of Excel

7. Combine two Form textbox fields prior to writing the record

8. decimal value in the textbox.

9. Print PDF in Excel X, Search a specific value in cells/Replace value in other cells

10. values I input into Excel automatically go to a decimal value


 
Powered by phpBB® Forum Software © phpBB Group