vba not currency but accounting

DB73

DB73

Board Regular
Joined
Jun 7, 2022
Messages
102
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2010
  6. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
hi peeps,

question....
with the following line i put my combobox value to my my sheet;
VBA Code: 
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
VBA Code: 
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;
VBA Code: 
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Try this:

VBA Code: 
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
 
Upvote 0
DanteAmor said:
Try this:

VBA Code: 
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
Click to expand...
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;
VBA Code: 
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
 
Upvote 0
Change this line:

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

For this:

VBA Code: 
  With Sheets("dump stats").Range("AR" & Rows.Count).End(xlUp).Offset(0, 0)
    .Value = ComboBox18.Value
    .NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* "" - ""??_-;_-@_-"
  End With
 
Upvote 0
DanteAmor said:
Change this line:

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

For this:

VBA Code: 
  With Sheets("dump stats").Range("AR" & Rows.Count).End(xlUp).Offset(0, 0)
    .Value = ComboBox18.Value
    .NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* "" - ""??_-;_-@_-"
  End With
Click to expand...
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.
 
Upvote 0
DB73 said:
i already did that....thats doesnt seems to work
it only formats it in Currency.
Click to expand...
Works for me.


Change the format of the all AR column to accounting format.
Try this alternative:
VBA Code: 
  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
 
Upvote 0
Solution
DanteAmor said:
Works for me.


Change the format of the all AR column to accounting format.
Try this alternative:
VBA Code: 
  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
Click to expand...
this one is working for me.
can u explain why this one work and the other one doesn't
 
Upvote 0
You must log in or register to reply here.

Similar threads

DB73
  • Question
mouse click instead of cmdbtn click
Replies
3
Views
300
Herakles
H
DB73
  • Question
Fault 1004 range of object aplication failed on userform.show
2
Replies
12
Views
534
Fluff
Fluff
D
  • Solved
Userform formatted as Currency
Replies
3
Views
467
Dan Swartz
D
DB73
  • Solved
from combobox to cell formating in currency
Replies
4
Views
637
DB73
DB73
DB73
  • Solved
userform combobox not showing percentage in pulldown
Replies
3
Views
293
dmt32
D

Forum statistics

Threads
1,214,979
Messages
6,122,552
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top