Value from UserForm to Worksheet - Accounting format - (How to?)

[melpa]

I am entering data from a UserForm to a Worksheet.

When entering the value from the "Cost" TextBox , I want it to be in Accounting format on the Worksheet.

I have tried:

Sheet1.Range("A1") = cCur(Cost.Value)

Sheet1.Range("A1") = Format(Cost.Value,"$#,##0.00")

Sheet1.Range("A1") = Format(Cost.Value,"$*  #,##0.00")

They all end up in Currency format

(I also tried

Sheet1.Range("A1") = Format(Cost.Value,"_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)")

but that caused an error)

The value in the Cost TextBox has been formatted itself:

UserForm.Cost = Format(ListBox1.Column(1), "$#,###.00")

What can I do so that the value is in Accounting format when it is entered on the Worksheet?

[Logit]

.
Not certain if this is an answer but .... have you formatted the Column where the entries are ending up ?

[Paul_Hossler]

Format() returns a string, and when the WS gets it it tries to help by making it into number using whatever the NumberFormat is


Try something like this and see if it helps

Sheet1.Range("A1").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)"

Sheet1.Range("A1").Value = Cost.Value

[melpa]

Thank you both for your suggestions.

@Logit: I am adding the value to a table - and the relevant column is formatted Accounting. However since I am adding it to the first row below the table your suggestion made me think to add the formatting to the whole worksheet column - not just the table. Unfortunately that didn't help.

@Paul_Hossler
Unfortunately I couldn't get your suggestion to work for me either. Possibly because in trying to simplify my query, I didn't provide enough detail.
I am (trying) to populate a table with the values entered on a UserForm using a loop.
I tried inserting the NumberFormat you suggested as follows, but it creates an error.

 With Sheet1.Range("B9999").End(xlUp).Offset(1, 0)
                  .Offset(0, 1).Value = CDate(frmInventory.Date)
                  .Offset(0, 2).Value = frmInventory.Supplier
                  .Offset(0, 3).Value = frmInventory.Item
                  .Offset(0, 4).NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* " - "??_-;_-@_-"
                  .Offset(0, 4).Value = frmInventory.Cost
          End With

(I tried the above Accounting Format as "_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)" didn't work)

[Paul_Hossler]

This seems to work -- look at the attachment

Capture.JPG

Private Sub CommandButton1_Click()

    ActiveSheet.Range("A1").Value = Me.TextBox1.Value
    ActiveSheet.Range("A1").NumberFormat = "_($* #,##0.00_);($* #,##0.00);_($* 0.00_);_(@_)"


    ActiveSheet.Range("A2").Value = -1 * Me.TextBox1.Value
    ActiveSheet.Range("A2").NumberFormat = "_($* #,##0.00_);($* #,##0.00);_($* 0.00_);_(@_)"


    ActiveSheet.Range("A3").Value = 0
    ActiveSheet.Range("A3").NumberFormat = "_($* #,##0.00_);($* #,##0.00);_($* 0.00_);_(@_)"

    Me.Hide
    Unload Me
End Sub

[melpa]

Thank you again, Paul_Hossler. That works.

It wasn't only the reversed order (add value then format) that was necessary, I also had to specify the TextBox1.Value (Cost.Value).

I read somewhere that .Value is the default for TextBoxes and it isn't necessary to specify.
This has been working for me up until this instance.
Your solution wouldn't work for me until I added the ".Value".

[melpa]

P.S. Could you answer another question for me, please?

I noticed in your attachment you write:

  Me.Hide
  Unload Me

Why is the Me.Hide necessary? Isn't Unload.Me sufficient?

[Paul_Hossler]

P.S. Could you answer another question for me, please?

I noticed in your attachment you write:

  Me.Hide
  Unload Me

Why is the Me.Hide necessary? Isn't Unload.Me sufficient?

Probably sufficient, but I'm in the habit of 'bracketing' commands even if not strictly necessary (might be bad habit, but 'old dog and new tricks .... ')

So

Load UserForm1
UserForm1.Show

....

UserForm1.Hide     ' bracketing the .Show
Unload UserForm1  ' bracketing the Load

Just personal style

[SamT]

add the formatting to the whole worksheet column - not just the table. Unfortunately that didn't help.

Format the cells in the Table as Accounting, Then format the cells below the table as Accounting.