Solved

VBA - Formatting number textbox

Posted on 2015-01-27
8
208 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
Industry Leaders: 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!

 
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 500 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

696 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