VBA - Formatting number textbox

Hi guys,

I need some help formatting the output which is in TextBox132 in word userform to be in rounded up with only 2 digits

this is the entire code:

Private Sub CommandButton4_Click() ' Beregn Aaop

Dim Belob As Double
Dim Omkostninger As Double
Dim Rente As Double
Dim Ydelse As Double
Dim Lobetid As Double
Dim Belob2 As Double
Dim Frekvens As Integer

Belob = TextBox14
Omkostninger = InputBox("Omkostninger")
Rente = TextBox15
Lobetid = TextBox18
Belob3 = Belob + Omkostninger
Frekvens1 = ComboBox1

' Beregner løbetid i antal terminer

If Frekvens1 = "Kvartalsvis" Then
Lobetid = Lobetid * 4
Frekvens = 4
Else
Lobetid = Lobetid * 12
Frekvens = 12

End If

Ydelse = WorksheetFunction.Pmt(Rente * 365 / 360 / 400, Lobetid, Belob3)

Belob2 = ((1 + WorksheetFunction.Rate(Lobetid, Ydelse, Belob)) ^ Frekvens) - 1
'MsgBox (Belob2) ' Check
TextBox132 = Belob2

Open in new window


I tried formatting it like this:

Private Sub TextBox132_AfterUpdate()
    
    TextBox132.Text = Format(TextBox132.Value, "#,##")
End Sub

Open in new window


it just dont work.

please advise
LVL 1
HakumAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
I don't get it.

You are clearly in a comma country, i.e. 1.000,56 means one thousand and 56/100s.

However, AOP in your screenshot above says "4.641177" - i.e. the full stop (period) is a thousand separator.

So presumably your value is 4.641.177.750.506,65 - so it works.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I would use:

TextBox132.Text = Format(TextBox132.Value, "0,00")
0
 
HakumAuthor Commented:
its not working :S

Capture.PNG
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
How about this:

TextBox132.Text = Round(TextBox132.Value, 2)
0
 
HakumAuthor Commented:
Getting closer but stille not there.. this is what i get:

4641177750506,65

and i would like something like:

4,65
0
 
HakumAuthor Commented:
sorry you are right, i got this:

4.641.177.750.506,65

with this:

TextBox132.Text = Format(TextBox132.Value, "#,##0.00")

sorry my math might not be in the right place.

But something is not working correctly I think i'll go back to the drawing table and see how to do it diffrently.

Thanks alot!!!
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
My guess - you have imported information from an American-type number source.

Therefore, I would use:

TextBox132.Text = Format(replace(TextBox132.Value,".",","), "#,##0.00")
0
 
HakumAuthor Commented:
this look sooo much better, but for some reason its not giving the correct output... i did a check on it, but after the formating its not calculating it correct.

Private Sub CommandButton4_Click() ' Beregn Aaop - Billån

Dim Belob As Double
Dim Omkostninger As Double
Dim Rente As Double
Dim Ydelse As Double
Dim Lobetid As Double
Dim Belob2 As Double
Dim Frekvens As Integer

Belob = 250000
Omkostninger = 0
Rente = 4.5
Lobetid = 80
Belob3 = Belob + Omkostninger
Frekvens1 = "Kvartalsvis"

' Beregner løbetid i antal terminer

If Frekvens1 = "Kvartalsvis" Then
Lobetid = Lobetid * 4
Frekvens = 4
Else
Lobetid = Lobetid * 12
Frekvens = 12

End If

Ydelse = WorksheetFunction.Pmt(Rente * 365 / 360 / 400, Lobetid, Belob3)

Belob2 = ((1 + WorksheetFunction.Rate(Lobetid, Ydelse, Belob)) ^ Frekvens) - 1
MsgBox (Belob2) ' Check
TextBox132 = Belob2
'TextBox132.Text = Format(TextBox132.Value, "#,##0.00")
TextBox132.Text = Format(Replace(TextBox132.Value, ".", ","), "#,##0.00")

End Sub

Open in new window


You could try to copy it into word and see how it reacts
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.