Solved: Calculation in a TextBox

[ctengelen]

: pray2: Hi all,

I am having one more problem with my form that I am working on:

I need to add a TextBox that caluclates Feet/Inches to centimeters upon "Exit".

example: the user enters 5'3" and tabs out of the box and 160 cm appears in that box.

I do know that the user will have to enter the 5'3" value in a different way" 503 for this to work. Right?? And that I need to run a macro to get this done.

Please:help me - as this is a bit too much for this VBA Newbie.

Thanks in advance for all your help

Trudy

[Tommy]

Hi ctengelen,

The below code will take the text from text1 (in the format 12'-4 3/16 or 12'4 3/16) in the active document and return the metric conversion.


Function GenDec(FtInch As String) As Double
Dim mFeet As Integer
Dim mInch As Integer
Dim mFract As Double
Dim mI As Integer
Dim mHldStr As String
Dim mIncSix As Variant
mHldStr = FtInch
mFeet = Val(mHldStr)
mI = InStr(1, mHldStr, "'-")
If mI = 0 Then mI = InStr(1, mHldStr, "'")
If mI > 0 Then
mHldStr = Replace(Mid(mHldStr, mI + 1), "-", vbNullString)
Else 'means no feet
mFeet = 0
End If
mIncSix = Split(mHldStr, " ")
mInch = Val(mIncSix(0))
If UBound(mIncSix) > 0 Then
mI = InStr(1, mIncSix(1), "/")
mFract = Val(mIncSix(1)) / Val(Mid(mIncSix(1), mI + 1))
ElseIf InStr(1, mIncSix(0), "/") > 0 Then
mInch = 0
mI = InStr(1, mIncSix(0), "/")
mFract = Val(mIncSix(0)) / Val(Mid(mIncSix(0), mI + 1))
End If
GenDec = mFeet * 12 + mInch + mFract
End Function
Function MakeMetric(FeetText As String) As Double
MakeMetric = GenDec(FeetText) * 2.54
End Function
Public Sub GetInfo()
Dim Info As FormField
Set Info = ActiveDocument.FormFields("Text1")
Info.Result = MakeMetric(Info.Result)
End Sub


HTH

[ctengelen]

Hi Tommy,

This is GREAT!!!! Just one more small thing - I do not want the fraction for the conversion - only up to the whole centimeter. What do I change???

Trudy
Forever grateful.

[ctengelen]

Hello again Tommy,

Now my boss - seing that it is possible to do the height conversion looks for a weight conversion also. Can't satisfy them bosses no how!

Pretty Please!!!!

[Tommy]

Public Sub GetInfo()
Dim Info As FormField
Set Info = ActiveDocument.FormFields("Text1")
Info.Result = Round(MakeMetric(Info.Result) ,0) '<-- this line add round to round to the first decimal
End Sub

[ctengelen]

:bow: Tommy this works GREAT!!!!!

Thanks you so much.

Trudy

[Tommy]

:)
Weights - are they entered? or do they need to be calculated from lengths?
what is the unit of measuresment? Lbs to ? LOL I've got to the point of just saying ok to them bosses :)

[ctengelen]

Hi Tommy, :bow:

Yep, it is the best to just smile and say sure to them darn bosses. But to say the least You have totally impressed him.

The weight measurements are from lbs. to kilogram and again without the fractions.

The part of the form I am working on at this moment is for description of people. Once I have completed this part (I hope) this form should be in bed, so to speak. Then I will be able to get my brain wrapped around this conversion macro to try to learn from it.

Thanks one more time.

Trudy

[Tommy]

This should make it happen :)

Function ConvertLbsToKG(iLbs As String) As Double
ConvertLbsToKG = Round((Val(iLbs) * 0.45359237), 0)
End Function
Public Sub GetLbs()
Dim Info As FormField
Set Info = ActiveDocument.FormFields("Text2")
Info.Result = CStr(ConvertLbsToKG(Info.Result))
End Sub

[ctengelen]

:cloud9:

Tommy - you are GREAT! Thank you so so much for your help!

:friends: From my boss a handshake for thank you. (I just hope he doesn't come up with more and 'better' ideas on making this form work.)

Forever grateful.

Trudy:doh:

[ctengelen]

:banghead:

I knew it!!!!

Now he wants to have the original feet/inches measurement shown in one TextBox (TextBox1) and the conversion than going into the next TextBox (TextBox2). Same for the weight.

So it looks like: 5'3" ft/in 160 cm

At least this is happening to me on a Friday and not on a Monday!!!!

Give a Boss an INCH and he wants it also in cm.:bug:

Trudy

[Tommy]

Is this textbox on a form or in a doc? If it is in a doc I can't seem to get a hold of it. in a form no problem.

[ctengelen]

The textbox is in a form.

[Tommy]

I am using the same functons I am just passing the text from the textboxes.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.Text = MakeMetric(TextBox1.Text)
End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox4.Text = ConvertLbsToKG(TextBox3.Text)
End Sub

[ctengelen]

Hello again Tommy... so far I could follow and implement the two prior macros and they work just great...where do I now place the last one - at the end of the MakeMetric code or in a seperate module??

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.Text = MakeMetric(TextBox1.Text)
End Sub

This is not healthy for my brain to work that hard so late in the week.

Trudy

[Tommy]

LOL put the macros in the form. In this form there should be 4 textboxes, named TextBox1,TextBox2, TextBox3, and TextBox4.
TextBox1 is the input for feet. TextBox2 is output for CM.
TextBox3 is input for lbs. TextBox4 is output for kg.

I used the default naming conventions for examples only, I would normally name the TextBox1-> "InputFeet", TextBox2 ->"OutCm", TextBox3 ->"InputLbs", TextBox4 ->"Outkg", this way I can look at the names and tell immediately what is going on. I would suggest that you name the textboxes in a manner that makes sense to you. I have included a sample file for you, this is what I tested with.:yes

[ctengelen]

:hairpull:

Hi again Tommy.

This is really to have kittens on a Friday afternoon.

The UserForm works wonderful = IF= the form is unprotected, but as soon as I protect the from again it stops and laughs at me (or so it seems).

Can I Unprotect the form just BEFORE I activate the UserForm and protect it again immediatly after???

:dunno Trudy

I attached a little word document for your Friday joy.

[ctengelen]

See how bad it is getting - I even forgot the attachment.

I need a drink!!!!!!!!!!!!!!!

[ctengelen]

Tommy - Tommy - Tommy!

It is me again.....I found a way to Unprotect my document.

I wrote another little macro that runs when the user enters a textbox. This macro will unprotect the form and runs the Userform. But no matter what I try, I can't protect the form after again.

With this - I will leave to go home. I hope you will have a great weekend and thanks for all your help.

Trudy

[MOS MASTER]

Nice job Tomy! :yes

[Tommy]

Hi Trudy :)

I really like the poem :cloud9: :yes

Have you tried

ActiveDocument.Protect wdAllowOnlyFormFields, True


Thanks Joost :hi: I actually have GenDec in 4 different versions. It depends on how the input is arriving :yes . LOL half my code will be history when the US goes to metric.

[ctengelen]

:thumb

Hello Tommy!

What a weekend off from work does for a very confused mind!

This morning I figured out that I do not need to unprotect my document in order for the conversion in both ft/in lbs/kg to work. I just placed the items in an unprotected part of my form and yippee it worked.

Tommy, if you were here in our city - by gosh and by golly - I would give you a bear hug like you never got one before!

Or better yet, I would take you to our local Oktoberfest. :beerchug:


I am so very grateful for all your help. I sure hope that in future I can assist folks the same way.

To all of you VBA gurus - hats off for a wonderful job you are doing.:bow:

Trudy
from the chillie North (Edmonton, Alberta where we had frost this morning)

[ctengelen]

Hi Trudy :)

I really like the poem :cloud9: :yes

Have you tried

ActiveDocument.Protect wdAllowOnlyFormFields, True


Thanks Joost :hi: I actually have GenDec in 4 different versions. It depends on how the input is arriving :yes . LOL half my code will be history when the US goes to metric.

Hi Tommy and Joost,

Canada has been metric for a long time now - but as you guessed it we are still doing stuff in 'Imperial' The stores show prices in pounds and in small for kg and so on. Only the gas stations show prices in litres. So, don't archive that code yet for a long, long time.

Trudy

[Tommy]

FROST!! It is 90 degrees F here 32 degrees C. LOL No cool weather for about a month.:yes No frost for 2.

I won't be archiving anytime soon. A lot of the stuff I do is used in China so I have to convert back an forth a lot, as long as there is no $ in front of the number I do ok but add the $ and I lose it. LOL 1/16", 1.2 mm I know, $0.02 everyone has something to say.:rofl: