Error on number formatting on VBA - Very Strange...

[rsanabria80]

1) I have an excel sheet with values (Numbers) that represent dollar amounts.
2) I have a Userform that has labels that update the values according to the amounts on the Excel Sheet...

Everything was running smoothly until I opened the excel worksheet with a PC (I work on MAC Excel 2011) in order to be able to edit userforms.

The only way it show the number correctly if is the value on the cell is a rounded number (no decimals)

Now with the same code I get a very strange number formatting using this code:

Sub Updatelabels()

Dim CF As Worksheet
Dim DB As Worksheet
Dim Export As Worksheet
Dim CAL As Worksheet
Set CF = ThisWorkbook.Sheets("Cashflow")
Set DB = ThisWorkbook.Sheets("DB")
Set Export = ThisWorkbook.Sheets("Export")
Set CAL = ThisWorkbook.Sheets("Calculation")


'Labels for Current Situation
Me.lblActBal2.Caption = CF.Cells(3, "f")
Me.lblActBal2 = Format(Me.lblActBal2, "#,##0.00")

End Sub

And on the Images you can see the number at the excel Sheet and the outcome on the Userform...

PLEASE HELPPPPP! jajajaj

Thank you!

[/CODE]

 

[mole999]

what would happen if
"#,##0.00")
was changed to
"#,##.00")

 

[rsanabria80]

what would happen if
"#,##0.00")
was changed to
"#,##.00")

I just tried and doesn't work!!! I tried: #,###.## | #,##0.00 | 0,000.00 | #,##.00 as you suggested | I tried not formatting at all which will work except it won't give me the thousands separator.

Any other suggestion?

 

[rsanabria80]

I just tried and doesn't work!!! I tried: #,###.## | #,##0.00 | 0,000.00 | #,##.00 as you suggested | I tried not formatting at all which will work except it won't give me the thousands separator.

Any other suggestion?

Could it have anything to do with the properties? They may have changed when opened the file on the PC and I may have not realized about the change... Or properties have nothing to do with this?

 

[mole999]

is that the correct 1000 separator for the installed language, also mac and pc excels differ

 

[rsanabria80]

is that the correct 1000 separator for the installed language, also mac and pc excels differ

The problem was Excel 2011 in a MacBook Pro 2019. They are not compatible anymore as the iOS installed on the Mac only handled 64bit programs.

So finally I found out that it ws just a software compatibility problem..

Thank you for helping anyway

 

[Rick Rothstein]

Just out of curiosity, what happens if you change this line of code...

Me.lblActBal2.Caption = CF.Cells(3, "f")

to this...

Me.lblActBal2.Caption = Replace(CF.Cells(3, "f"), ",", "")