Link to home
Start Free TrialLog in
Avatar of Harsh Kumar
Harsh KumarFlag for Denmark

asked on

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
Avatar of Phillip Burton
Phillip Burton

I would use:

TextBox132.Text = Format(TextBox132.Value, "0,00")
Avatar of Harsh Kumar

ASKER

its not working :S

User generated image
How about this:

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

4641177750506,65

and i would like something like:

4,65
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!!!
My guess - you have imported information from an American-type number source.

Therefore, I would use:

TextBox132.Text = Format(replace(TextBox132.Value,".",","), "#,##0.00")
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