Text as number problems(also adding thousand separators(.))

[gulli09]

I have a userform with a lot of designed and weird code behind it so it can 'look' correctly. Therefore I can't really mess up with that code. However, when I finish the user-form and the data exports to a table I have some problems.

The error "number in the cell is formatted as text" came up. I managed to fix it for textbox which included percentages by adding .Value add the end. When I tried the same for textboxes including number it went all wrong. When I wrote 500.000 in the user-form it returned 500. I fixed that by adding * 1 at the end. Now my only remaining problem is getting "500.000" instead of "500000" eg. the thousand seperators.

.Cells(lngRow, 7) = txtNum1.Value * 1 = Format("#,##0")
.Cells(lngRow, 13) = txtNum2.Value * 1 = Format(Val(txtNum1.Text), "#,##0")

I've tried both of these, which return "FALSE", anyone know what I need to do?

 

[gulli09]

I tried simply changing format cells to accounting. It worked, but each new registry has an automated number(1,2,3,4) next to it. When I f.x. change the format of the cell for rows 1-20, with the data completely empty and then assign a new registry the data will appear in line 21, the first cell with the non-accounting format

 

[AD_Taylor]

Do you need to do something similar to this:

.Cells(lngRow, 7).Value = txtNum1.Value
.Cells(lngRow, 7).NumberFormat = "#,##0"

Or my preferred way of writing:

With .Cells(lngRow, 7)

    .Value = txtNum1.Value
    .NumberFormat = "#,##0"
    
End With

Since '.NumberFormat' is a property of the cell you have to set it with at least 2 lines.

I assume there is also a very good reason for the '.Cells' instead of referring to it as just 'Cells' so have kept this in.

 

[Norie]

Don't use Format, that returns a string.

Try Val or CDec on there own.

Cells(lngRow, 7) = CDec(txtNum1.Value )
.Cells(lngRow, 13) = CDec(txtNum2.Value)

 

[gulli09]

I really appreciate the help;

The first one(AD_Taylor) gave me the correct format(5.000.000) but then again I have the "number as string" problem.

The second one(Norie)
Cells(lngRow, 7) = CDec(txtNum1.Value )

Gave me the format as number, but the result was "500000" and not "500.000" so the thousand points are missing!

 

[Norie]

You have the right value, it's numeric so all you need to do is format the cell.

 

[gulli09]

Yes, but I would need to do it manually each time. If I change the cell format for say, A1:A30 with no input in no cells then fill out a new userform(which exports to the table) then it will start as number 31, and fill out in that column

 

[Norie]

Why not format the whole column instead of just the first 30 cells?

PS Why would you have to do it manually?

 

[gulli09]

Jesus F! I can't believe it was that simple all along! I thought it wouldn't make a difference whether I formatted the entire column or just a sample of it. When I changed the formatting on the first 30, the data would appear in row 31, so I figured If I changed the entire column, the data would appear in a new number 10300 f.x.

Works great now, thanks!

 

[AD_Taylor]

Jesus F! I can't believe it was that simple all along! I thought it wouldn't make a difference whether I formatted the entire column or just a sample of it. When I changed the formatting on the first 30, the data would appear in row 31, so I figured If I changed the entire column, the data would appear in a new number 10300 f.x.

Works great now, thanks!

Glad you got it working!

Just FYI the reason I posted my answer is because when I tested it, I didn't get the number stored as text error. I got the number 500000 stored in the formula bar, but '500,000' shown in the cell.