Valuta in cells

[dotchieJack]

Hoi everybody
I want to add this code under a command button in an excel userform

Range("H:H").numberformat="$ #,##0.00_-"

The code under this commandbutton is now

Private Sub CommandButton4_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet
iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row + 1
  ws.Cells(iRow, 3).Value = Me.TextBox20.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox12.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  ws.Cells(iRow, 3).Value = Me.TextBox30.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox29.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  ws.Cells(iRow, 3).Value = Me.TextBox31.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox32.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  ws.Cells(iRow, 3).Value = Me.TextBox21.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox22.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  ws.Cells(iRow, 3).Value = Me.TextBox25.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox26.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  ws.Cells(iRow, 3).Value = Me.TextBox28.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox27.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row + 1
  ws.Cells(iRow, 3).Value = Me.TextBox23.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox24.Value
  ws.Cells(iRow, 8).Font.Bold = False
  ws.Cells(iRow, 8).Font.Underline = xlUnderlineStyleSingle
End Sub

Can someone help pls
Thanks

 

[Luke M]

Not exactly sure what question/problem is...why not just do this?

Private Sub CommandButton4_Click()
Dim iRow As Long
Dim ws As Worksheet

'New line
Range("H:H").NumberFormat="$ #,##0.00_-"

Set ws = ActiveSheet
iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row + 1
  ws.Cells(iRow, 3).Value = Me.TextBox20.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox12.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  ws.Cells(iRow, 3).Value = Me.TextBox30.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox29.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  ws.Cells(iRow, 3).Value = Me.TextBox31.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox32.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  ws.Cells(iRow, 3).Value = Me.TextBox21.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox22.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  ws.Cells(iRow, 3).Value = Me.TextBox25.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox26.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row
  ws.Cells(iRow, 3).Value = Me.TextBox28.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox27.Value
  ws.Cells(iRow, 8).Font.Bold = False

iRow = ws.Cells(Rows.Count, 3) _
  .End(xlUp).Offset(1, 0).Row + 1
  ws.Cells(iRow, 3).Value = Me.TextBox23.Value
  ws.Cells(iRow, 3).Font.Bold = True
  ws.Cells(iRow, 8).Value = Me.TextBox24.Value
  ws.Cells(iRow, 8).Font.Bold = False
  ws.Cells(iRow, 8).Font.Underline = xlUnderlineStyleSingle
End Sub

 

[dotchieJack]

Thank you for the quick answer, for some reason i can not upload an example
Do you have a solution so that i can upload an example
tanks in advance
My problem is that from an exel userform i fill some cells in a worksheet but for some reason some cells are not valuta

 

[dotchieJack]

In this dutch post I have an example
http://www.helpmij.nl/forum/showthread.php/885143-Valuta-in-cellen
see post #1
Look in offerte colom H

 

[jindon]

Is this how you wanted?

Columns("h").NumberFormat = """$ ""#,##0.00_-"

 

[dotchieJack]

Hoi,
Thank you for the reply, problem is solved