Solved

VBA - Formatting number textbox

Posted on 2015-01-27
8
201 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now