NumberFormat is not formatting correctly

[ExcelEndeavor]

When clicking a command button in a userform, I am sending data to a worksheet ("PO Data"). This code uses a NumberFormat command with accounting format, but when I check the worksheet, it is still in currency format. I have column (G) in the worksheet formatted for accounting as well, so not sure why this is not formatted accurately. I use the same code on another userform that sends to a different worksheet and it works flawlessly. The accounting format line is towards the bottom. What might prevent the NumberFormat from formatting correctly in this instance?

Private Sub ButtonAddInvoice_Click()
 
    'Declare worksheet variable
    Dim rSh As Worksheet
    On Error Resume Next
    Set rSh = ThisWorkbook.Sheets("PO Data")
    On Error GoTo 0
  
        'If it is a new record
    'Get the next available row
    Dim nextRow As Long
    If Label2 = "True" Then
      nextRow = updateRow
  
    MsgBox "Successfully Added"
    Unload Me
 
    Else
      nextRow = rSh.Range("A" & Rows.Count).End(xlUp).Row + 1
    End If
  
    'Assign columns
    rSh.Range("A" & nextRow).Value = RequestForm.TxtTeamLead
    rSh.Range("B" & nextRow).Value = RequestForm.ComboBU
    rSh.Range("C" & nextRow).Value = RequestForm.TxtPayee
    rSh.Range("D" & nextRow).Value = RequestForm.TxtPONbr
    rSh.Range("E" & nextRow).Value = TxtInvoiceNumber
    rSh.Range("F" & nextRow).Value = TxtInvoiceDate
    rSh.Range("G" & nextRow).Value = TxtInvoiceAmount
    rSh.Range("H" & nextRow).Value = CheckboxPaidInvoice
      
        'Change the CheckboxPaidInvoice output to "Paid" or ""
        If Me.CheckboxPaidInvoice.Value = True Then
            rSh.Range("H" & nextRow).Value = "Paid"
        Else
            rSh.Range("H" & nextRow).Value = ""
        End If
 
    ' Activate the UpdateInvoiceList subroutine in the RequestForm userform
    Call RequestForm.UpdateInvoiceList
 
    'Format currency to accounting in worksheet
    rSh.Range("G:G").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
 
    'Unload Me
    clearForm
    Unload Me
 
End Sub

Thank you in advance.

 

[RoryA]

What format code is actually being applied to the cells?

 

[ExcelEndeavor]

formatting as accounting - the code is at the bottom of the code I posted:

    'Format currency to accounting in worksheet
    rSh.Range("G:G").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

 

[RoryA]

No, I mean what code is actually applied to the cells, given that you said it is not the one in the code. If you select one of the cells, choose Format Cells, then click the Custom category, what is the code displayed for that cell? I see this:

 

[ExcelEndeavor]

Here is a screenshot of that dialog box:

 

[RoryA]

That's exactly what your code specifies, so I'm not sure I see what the problem is?

 

[ExcelEndeavor]

The problem that it is displaying as currency:

I need it to display as accounting:

 

[MARK858]

What is the green error triangle showing when you click on it (your data looks like text to me at the moment and not a number)?

 

[ExcelEndeavor]

it says "Number Stored as Text" - that is the problem I am trying to resolve. I have formatted the column as "Accounting" and the NumberFormat in the userform is coded to send the data in accounting format. So, why is it changing the format to text?

 

[MARK858]

Try the steps below...

• Select column G
• Data tab
• Text to Columns
• Delimited
• Next
• Make sure all the checkboxes are cleared
• Next
• Make sure General is selected
• Click Finish
• Format the cells as desired

You can also try changing your code to

rSh.Range("G" & nextRow).Value = CDbl(TxtInvoiceAmount)

After making sure that your column G isn't showing as formatted as Text before the code is run