# vba not currency but accounting



## DB73 (Jan 3, 2023)

hi peeps,

question....
 with the following line i put my combobox value to my my sheet;

```
Sheets("dump stats").Range("AR" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox18.value
```

with the following i make it in in the cb look like currency

```
Private Sub ComboBox18_Change()
ComboBox18.value = Format(ComboBox18.value, "currency")
End Sub
```

my problem is that everything on that sheet is in "accounting" format

is it possible to , make it as accounting format too?

the combobox is populated like this;

```
ComboBox18.List = Application.Range("te_declareren_per_km").value
```
comes from a table with conditional format as currency
do i have to remove the formating of this table ?

thanks for the help


----------



## DanteAmor (Jan 3, 2023)

Try this:


```
Private Sub CommandButton1_Click()
  With Sheets("dump stats").Range("AR" & Rows.Count).End(xlUp).Offset(0, 0)
    .Value = ComboBox18.Value
    .NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* "" - ""??_-;_-@_-"
  End With
End Sub
```


----------



## DB73 (Jan 4, 2023)

DanteAmor said:


> Try this:
> 
> 
> ```
> ...


thanks DanteAmor, but where or how do i put the code in here ??
there are actuly 3 lines where i want to use this (accounting)
but i tought, when i got the answer, i can use it also for the rest.
as u can see its for cb15,16 and 18
but i want them seperatly bcause im still bzzy withe taht userform, so , for me, i can easely changse the cbxes

this is the code i use for my cmdbttn;

```
Private Sub CommandButton5_Click()

If (ComboBox1.value = "") Then
    MsgBox "werkdag/ziek/verlof is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox2.value = "") Then
    MsgBox "Project is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox3.value = "") Then
    MsgBox "Werklocatie is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox4.value = "") Then
    MsgBox "Normale of overuren is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox5.value = "") Then
    MsgBox "tarief percentage is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox6.value = "") Then
    MsgBox "Aanvang werktijd is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox7.value = "") Then
    MsgBox "Einde werktijd is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox8.value = "") Then
    MsgBox "Pauze is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox9.value = "") Then
    MsgBox "BTW ja/nee is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox11.value = "") Then
    MsgBox "Rit van is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
 If (ComboBox12.value = "") Then
    MsgBox "Rit naar is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox13.value = "") Then
    MsgBox "Enkele reis of retour rit is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox14.value = "") Then
    MsgBox "Reden rit is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox15.value = "") Then
    MsgBox "Te declareren per Km is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox16.value = "") Then
    MsgBox "Declaratie bedrag (anders) is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If
If (ComboBox17.value = "") Then
    MsgBox "Reden van declaratie is niet ingevuld"
    'ComboBox1.BackColor = RGB(255, 0, 0)
      'ComboBox1.BackColor = RGB(230, 230, 47)
    Exit Sub
    End If

'Datum
    Sheets("dump stats").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).value = CDate(TheDate.value)
'ziek/werkdag/feestdag
    Sheets("dump stats").Range("M" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox1.Text
'project
    Sheets("dump stats").Range("N" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox2.Text
'werk locatie
    Sheets("dump stats").Range("Q" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox3.Text
'normale of over uren
    Sheets("dump stats").Range("R" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox4.Text
'uur tarief percentage
    Sheets("dump stats").Range("S" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox5.Text
'starttijd
    Sheets("dump stats").Range("T" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox6.Text
'eindtijd
    Sheets("dump stats").Range("U" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox7.Text
'pauze
    Sheets("dump stats").Range("V" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox8.Text
'BTW verlegd ja/nee
    Sheets("dump stats").Range("Y" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox9.Text
'BTW heffing
    Sheets("dump stats").Range("Z" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox10.Text
'Rit van
    Sheets("dump stats").Range("AF" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox11.Text
'Rit naar
    Sheets("dump stats").Range("AJ" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox12.Text
'Enkele reis of retour rit
    Sheets("dump stats").Range("AN" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox13.Text
'reden rit
    Sheets("dump stats").Range("AP" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox14.Text
'declaratie bedrag pkm zakelijk
    Sheets("dump stats").Range("AQ" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox15.value 'CCur(ComboBox15.value)
'overige declaratie
    Sheets("dump stats").Range("BA" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox16.value 'CCur(ComboBox16.value)
'reden overige declaratie
    Sheets("dump stats").Range("AZ" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox17.Text
'Km bedrag declaratie prive
    Sheets("dump stats").Range("AR" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox18.value 'CCur(ComboBox18.value)
   
Unload Me

End Sub
```


----------



## DanteAmor (Jan 4, 2023)

Change this line:


```
Sheets("dump stats").Range("AR" & Rows.Count).End(xlUp).Offset(0, 0).value = ComboBox18.value
```

For this:


```
With Sheets("dump stats").Range("AR" & Rows.Count).End(xlUp).Offset(0, 0)
    .Value = ComboBox18.Value
    .NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* "" - ""??_-;_-@_-"
  End With
```


----------



## DB73 (Jan 5, 2023)

DanteAmor said:


> Change this line:
> 
> 
> ```
> ...


i already did that....thats doesnt seems to work
it only formats it in Currency.

has it something to do with conditional formating from where i populate cmbx18 from.
i cant find any line in my file/vba that also wants to change or affect that output.


----------



## DanteAmor (Jan 5, 2023)

DB73 said:


> i already did that....thats doesnt seems to work
> it only formats it in Currency.


Works for me.


Change the format of the all AR column to accounting format.
Try this alternative:

```
Dim n As Double
  With Sheets("dump stats").Range("AR" & Rows.Count).End(xlUp).Offset(0, 0)
    If ComboBox18.Value <> "" Then
      n = CDbl(ComboBox18.Value)
      .Value = n
      .NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* "" - ""??_-;_-@_-"
    End If
  End With
```


----------



## DB73 (Monday at 5:26 AM)

DanteAmor said:


> Works for me.
> 
> 
> Change the format of the all AR column to accounting format.
> ...


this one is working for me.
can u explain why this one work and the other one doesn't


----------

