CODEWITHSUNDEEP
vbams-word
David Nunez
David Nunez

Reputation: 3

Word VBA Formfield number format

I am creating a form where the input of one field is used for calculations in VBA to return a value that is inserted in another text field. I am fine up to here.

Problem is, dealing with Power numbers tend to get quite high, so my code divides Watts into Kilowatts or Megawatts depending on the value.

If the number is divided by 1,000 into kW, I would like the format of the field to be "#.##0,00 kW". If it is divided by 1,000,000 into Megawatts, the format should be "#.##0,00 MW".

Any suggestions on how to do this?

My code looks something like this (previous part is irrelevant):

Dim Module As String, Power As Integer, Technology As String, TechLong As String, NumberOfCells As Integer

Module = ActiveDocument.FormFields("ModType").Result
Power = Mid(Module, 4, 3)
Technology = Mid(Module, 9, 1)
NumberOfCells = Mid(Module, 10, 2)

' Project information
Dim NumModules As Long, ProjectSize As Long, PowerField As FormField

NumModules = ActiveDocument.FormFields("ModNum").Result
ProjectSize = NumModules * Power

Set PowerField = ActiveDocument.FormFields("TotalPower")

If ProjectSize < 1000000 Then
    'Change W to kW
    PowerField.Result = ProjectSize / 1000
Else
    'Change W to MW
    PowerField.Result = ProjectSize / 1000000
End If

Thanks!

Upvotes: 0

Views: 476

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

My suggestion is to break the number and power label into two separate form fields. Deactivate the "Fillin enabled" property of the power label so that the user can't change it.

Even though the user can't change the content of the "label", your code can.

This approach allows you to leave the number formatting of the power amount field alone. Changing the number format setting using your code would mean unprotecting then re-protecting the form document. It's always better to avoid that step if at all possible.

So assuming the form field for the power label is named PowerLabel your code could look like this:

PowerLabel = ActiveDocument.Formfields("PowerLabel")
If ProjectSize < 1000000 Then
    'Change W to kW
    PowerField.Result = ProjectSize / 1000
    PowerLabel.Result = "Kw"
Else
    'Change W to MW
    PowerField.Result = ProjectSize / 1000000
    PowerLabel.Result = "Mw"
End If

Upvotes: 1

Related Questions