?
Solved

VBA - Formatting number textbox

Posted on 2015-01-27
8
Medium Priority
?
215 Views
Last Modified: 2015-01-27
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
0
Comment
Question by:Hakum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40572347
I would use:

TextBox132.Text = Format(TextBox132.Value, "0,00")
0
 
LVL 1

Author Comment

by:Hakum
ID: 40572352
its not working :S

Capture.PNG
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40572394
How about this:

TextBox132.Text = Round(TextBox132.Value, 2)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:Hakum
ID: 40572400
Getting closer but stille not there.. this is what i get:

4641177750506,65

and i would like something like:

4,65
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40572407
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
 
LVL 1

Author Comment

by:Hakum
ID: 40572427
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40572429
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
 
LVL 1

Author Comment

by:Hakum
ID: 40572606
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question