the formatnumber() function does not display decimal numbers that have more than 3 decimal places

[gogi100]

i have next code

Dim z As Range
Dim inputstring As String
Dim brojdecimalnihmesta As Integer
Dim DecSep As String
Dim ThousendSep As String
Dim CurrentSelectionRng As String
Dim eIndex As Long
Dim eRowIndex As Long


CurrentSelectionRng = Selection.Address
On Error Resume Next

MsgBox "Pre nego sto unesete format kolone: broj, tekst, datum, procenat; izaberite jednu celiju u koloni, koja ce biti selektovana", vbOKOnly
inputstring = InputBox(Prompt:="Unesi format kolone('b' - broj, 't' - tekst, 'd' - datum, 'p' - procenat", Title:="Format kolone")


 If inputstring = "b" Then
     MsgBox "Format celije(a) ce biti broj", vbOKOnly
    brojdecimalnihmesta = InputBox(Prompt:="Unesi broj decimalnih mesta", Title:="Decimalna mesta")
    eIndex = Application.ActiveCell.Column
    eRowIndex = Application.ActiveSheet.Cells(Rows.count, eIndex).End(xlUp).Row
    Range(Cells(2, eIndex), Cells(eRowIndex, eIndex)).Select
    System = Application.UseSystemSeparators
     With Application
        .DecimalSeparator = ","
        .ThousandsSeparator = "."
        .UseSystemSeparators = False
    End With
      If System = True Then
            DecSep = Application.International(xlDecimalSeparator)
            ThousendSep = Application.International(xlThousandsSeparator) 'not used;for information only
      Else
            DecSep = Application.DecimalSeparator
            ThousendSep = Application.ThousandsSeparator 'not used;for information only
      End If
 
  
   For Each z In Selection
  
  If brojdecimalnihmesta = 0 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    ElseIf brojdecimalnihmesta = 1 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    ElseIf brojdecimalnihmesta = 2 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    ElseIf brojdecimalnihmesta = 3 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    ElseIf brojdecimalnihmesta = 4 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    ElseIf brojdecimalnihmesta = 5 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    End If
  
   Next
    With Selection
  
    .HorizontalAlignment = xlRight
   ' .NumberFormat = "###,###,###,###0.00"
    
   End With
  
   Exit Sub
 End If

when I apply my macro each number for example 34563,9843 is displayed in the format 34,563.98 when I enter the number of decimal places 3 or 4 or 5. when I select the number of decimal places 1 the display in excel is 34,563.90. the display after the separator and decimals is fine with the menu, but why doesn't it display decimal places larger than 2.
I have the same problem with the percentage format.
I noticed when I entered the number of decimal places. This number actually rounds to that number of decimal places, and a always shows 2 decimal places

 

[Alex Blakenburg]

I need a screenshot of what it looks like on the other machine and also what you want it to look like.

By the way the code you have below, I would consider a very nasty piece of code.
You are changing the settings on someone's machine without telling them and withou resetting it when you finish.

      If System = True Then
       With Application
        .DecimalSeparator = ","
        .ThousandsSeparator = "."
        .UseSystemSeparators = False
       End With
      End If

 

[gogi100]

On my computer

this is what i want. the format is number in column, but on another computer i receive the same display, but format is problem i receive in corner error message 'this cell formated as text...'

 

[gogi100]

one more question, why on my computer this error does not displays but another computer this error is displays?

 

[gogi100]

in my code i added on computer, where my macro does not works, next code
z = z*1
z = z.Value

 

[Alex Blakenburg]

I am unable to replicate the issue you have on the other machine.
Is the other machine using the same spreadsheet ?
If its a different spreadsheet perhaps share the spreadsheet with us using one of the sharing platforms eg dropbox, google drive etc

If it is the same spreadsheet, I don't know what it could be. You can try to send me a picture of what it looks like on the other machine to see if that helps me at all.
Also the exact data and input box responses you are using.

 

[gogi100]

I am unable to replicate the issue you have on the other machine.
Is the other machine using the same spreadsheet ?
If its a different spreadsheet perhaps share the spreadsheet with us using one of the sharing platforms eg dropbox, google drive etc

If it is the same spreadsheet, I don't know what it could be. You can try to send me a picture of what it looks like on the other machine to see if that helps me at all.
Also the exact data and input box responses you are using.

thanks for help, i resolved my problem with code above

 

[Alex Blakenburg]

OK sounds good.
PS: Please get rid of those lines that deactivate the "Use systems separators", I believe you don't need it for your code to work and will cause anyone who runs it a great deal of frustration when it mucks up their Excel.