#1
|
|||
|
|||
Calculation to Pre poutlate Text
Hi
I am trying to find a way that I am enter a value and then a calculation would fill out the rest of text. Below is an example I am trying to enter the sq ft value and then the sqm will auto populate. I can not use a form as this will have other text that will need edited. taken (____) sq ft ((____) sq m) All Advise welcome. |
#2
|
||||
|
||||
With protected forms ruled out, you cannot do it automatically without macros, so you would need to save the document as either a macro enabled document or template and the users would have to allow the macro to run.
For Word 2013 or 2010 (Word 2007 will require additional code, and it will not work in earlier versions which do not support content controls) Insert a plain text content control at each location, name the first SQFT and the second SQM. Set the default texts of each to 0 and check the property to disable deletion. In the second field only check the 'Contents cannot be edited check box' property In the ThisDocument module of the document/template add the following code which will calculate to two decimal places the value of sq ft in sq m. Code:
Option Explicit Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean) Dim oCC As ContentControl Select Case CC.Title Case "SQFT" 'Validate/format data entered in variable content control. If Not IsNumeric(CC.Range.Text) Then Cancel = True Beep CC.Range.Text = "0" With ActiveDocument.SelectContentControlsByTitle("SQM").Item(1) .LockContents = False .Range.Text = "0.00" .LockContents = True End With CC.Range.Select Exit Sub Else CC.Range.Text = Format(Val(CC.Range.Text)) End If 'Update content control contents. Set oCC = ActiveDocument.SelectContentControlsByTitle("SQM").Item(1) With oCC .LockContents = False .Range.Text = Format(Val(ActiveDocument.SelectContentControlsByTitle("SQFT").Item(1).Range.Text * 0.09290304), "0.00") .LockContents = True End With End Select Set oCC = Nothing lbl_Exit: Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Thanks Gmajor this has worked perfectly.
One last question for larger values is to possible to get a , between the zeros ie 25,000 Also I would then be looking to use the the SQFT to work out the rent i.e £ (____) pa, equating to £ (____) psf (£ (____) psm). So if I enter the PA value it will out out the PSF and PSM. Is this possible? I will then look to run similar Macros but in different parts of the doc, do I just re name the plain text content in the different area say SQFT1 and add a new module as rename the code from 'SQFT' to 'SQFT1'. Alot of question but much appreciated Thanks |
#4
|
||||
|
||||
Do note that having other text that needs to be edited does not of itself rule out the use of formfields and forms protection. Depending on your requirements, you may be able to use Section breaks to delineate the editable Sections, in which case the calculations can be done without macros.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
You don't need more macros, you just need more case statements or more calculations e.g. as follows, which will also format with comma thousands separators. Here the field names are PA, PSF and PSM. You will have to provide your own calculations, those included are just examples.
Code:
Option Explicit Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean) Dim oCC As ContentControl Select Case CC.Title Case "SQFT" 'Validate/format data entered in variable content control. If Not IsNumeric(CC.Range.Text) Then Cancel = True Beep CC.Range.Text = "0.00" With ActiveDocument.SelectContentControlsByTitle("SQM").Item(1) .LockContents = False .Range.Text = "0.00" .LockContents = True End With CC.Range.Select Exit Sub Else CC.Range.Text = Format(Val(CC.Range.Text), "#,###,##0.00") End If 'Update content control contents. Set oCC = ActiveDocument.SelectContentControlsByTitle("SQM").Item(1) With oCC .LockContents = False .Range.Text = Format(Val(ActiveDocument.SelectContentControlsByTitle("SQFT").Item(1).Range.Text * 0.09290304), "#,###,##0.00") .LockContents = True End With Case "PA" If Not IsNumeric(CC.Range.Text) Then Cancel = True Beep CC.Range.Text = "0.00" With ActiveDocument.SelectContentControlsByTitle("PSF").Item(1) .LockContents = False .Range.Text = "0.00" .LockContents = True End With With ActiveDocument.SelectContentControlsByTitle("PSM").Item(1) .LockContents = False .Range.Text = "0.00" .LockContents = True End With CC.Range.Select Exit Sub Else CC.Range.Text = Format(Val(CC.Range.Text), "#,###,##0.00") End If Set oCC = ActiveDocument.SelectContentControlsByTitle("PSF").Item(1) With oCC .LockContents = False 'The calculation for PSF - here 4 payments .Range.Text = Format(Val(ActiveDocument.SelectContentControlsByTitle("PA").Item(1).Range.Text / 4), "#,###,##0.00") .LockContents = True End With 'Update content control contents. Set oCC = ActiveDocument.SelectContentControlsByTitle("PSM").Item(1) With oCC .LockContents = False 'The calculation for PSM - here 12 payments .Range.Text = Format(Val(ActiveDocument.SelectContentControlsByTitle("PA").Item(1).Range.Text / 12), "#,###,##0.00") .LockContents = True End With 'More Case statements as required End Select Set oCC = Nothing lbl_Exit: Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help with a sum calculation please. | ballst | Excel | 4 | 06-04-2015 11:40 AM |
Please help with age calculation!!! | Microsoft Idiot | Word | 4 | 10-29-2012 05:54 AM |
Date Calculation | Lights | Excel | 5 | 04-18-2012 04:31 AM |
Calculation within Cells | manich1 | Excel | 2 | 12-07-2011 02:59 PM |