Solved: Run Time Error 13 & Format Func in userform

[slamet Harto]

Hi there,

I've an userform with the following code, however, If we chose "CboBps" for the first time userform show than it will reflect debug with message "Run time error 13" Type mismacth

Q2: What is format function that I can use with this userform if I want to change the format data in entire column E, for example, the data as format like this:

Column E >>> It should be
12.000 >>> 12000
20.000 >>> 20000


Could you help me to fix the error, please?
Any reply or suggestion, would be highly appreciate it.
Many thanks in advance,
Rgds,Harto


Private Sub TxtCurrPts_Change()
If Not TxtCurrPts = "" Then
TxtCurrPts = FormatNumber(TxtCurrPts, 0)
TxtTotpts = FormatNumber(CSng(TxtCurrPts), 0) ' + TxtCurrPts, 0)
TxtTotpts.ForeColor = IIf(TxtTotpts < 0, vbRed, vbBlue)
TxtCurrPts = FormatNumber(TxtCurrPts, 0)
End If
End Sub

Private Sub Cbobps_Change()
If Not Cbobps = "" Then
Cbobps = FormatNumber(Cbobps, 0)
TxtTotpts = FormatNumber(CSng(Cbobps) + TxtCurrPts, 0)
TxtTotpts.ForeColor = IIf(TxtTotpts < 0, vbRed, vbBlue)
End If
End Sub

Private Sub userform_initialize()
With Cbobps
.AddItem 10000
.AddItem 20000
.AddItem 30000
.AddItem 40000
.AddItem 50000
.AddItem 60000
.AddItem 70000
.AddItem 80000
.AddItem 90000
End With
End Sub

[Jan Karel Pieterse]

The error is caused by an empty textbox and can be resolved by changing this line:

TxtTotpts = FormatNumber(CSng(Cbobps) + TxtCurrPts, 0)

To:

TxtTotpts = FormatNumber(CSng(Cbobps + TxtCurrPts), 0)

(notice the placement of the brackets)

[slamet Harto]

Hi Jan Karel

Thansk a lot for your quick reply, but how about Question#2
Means, if the user click "entry"/Command button1 in userform, then
those cell in column "E" will be format as text .(see file attached)
(I'm sorry if the question is not clear enough)



Once again, thanks for your help
Best, harto

[Jan Karel Pieterse]

Change this line:

Cells(NewRow, 5) = Cbobps

To:

Cells(NewRow, 5) = CSng(Cbobps)

[slamet Harto]

Hi JKP

Sorry to bothering you, I just realized that sum function (TxtTotpts) were not calculating properly.
If I send 1000 to TxtCurrpts and 20000 to CboBps
then txttotpts will be 200001000

Can you help me again.
Thanks & Rgds, Harto

[Jan Karel Pieterse]

Duh, of course.

Change:

TxtTotpts = FormatNumber(CSng(Cbobps + TxtCurrPts), 0)

To:

If TxtCurrPts="" Then
TxtTotpts = FormatNumber(CSng(Cbobps), 0)
Else
TxtTotpts = FormatNumber(CSng(Cbobps) + CSng(TxtCurrPts)), 0)
End If

[slamet Harto]

Dear JkP

Brilliant, Thank you so much for your help and asistance.

Kind rgds,
harto