Legacy Text Form Field Formatting after added row

[BradBerger]

Hey everyone, I'm creating a service report document with some simple VB code using command buttons and tables. Been browsing this board and it's been very helpful thus far. However, I'm having trouble with some formatting of text form field controls and was wondering if anyone could shed some light for me. Im using Word 2016.

There is a "Parts Used" table in the document consisting of 5 columns and one row containing the titles of these columns. These titles are bold. I have set up a command button that adds a row to the table and inserts a legacy text form field in each new cell. It works exactly how I want it except for one thing. When typing in the form fields, the text is bold and not formatted how I would prefer.

Is there any way to edit the "add row" command button's code so that it can allow each form field to be formatted separately? Ideally, I'd like column 1 to be number format, column 2 and 3 to be regular, and column 4 and 5 to be number formatted $0.00.

Here is the code for the command button and table:



'Macro to add row to Parts Used Table


Sub MakeNewRow(oTable As Table)


Dim NewRow As Row
Dim oCell As Cell

ActiveDocument.Unprotect
oTable.Range.Cells(oTable.Range.Cells.Count).Select
Selection.MoveRight Unit:=wdCell
Set NewRow = oTable.Rows(oTable.Rows.Count)
For Each oCell In NewRow.Cells
oCell.Range.FormFields.Add Range:=oCell.Range, _
Type:=wdFieldFormTextInput
oCell.Range.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Name = "Col" & oCell.ColumnIndex & "Row" & oCell.RowIndex
.Execute
End With
Next
NewRow.Cells(1).Select
ActiveDocument.Protect wdAllowOnlyFormFields, NoReset:=True, _
Password:=""

End Sub


'Add Row Button - Parts Used


Sub CommandButton1_Click()
Dim oTable As Table
Set oTable = ActiveDocument.Tables(7)
Call MakeNewRow(oTable)


End Sub


If I can find a solution to this problem, there is one more thing I'd like to figure out. Column 1 is Quantity, Column 4 and 5 are Price and Total. Is there a way to code this so that the Price form is multiplied by the Quantity form and the result is entered automatically into the Total form. The Total form would be disabled from editing as well.

Thanks for reading and hopefully someone out there can help me!

-Brad

[gmayor]

The code at http://www.gmayor.com/word_vba_examples_2.htm 'Add a row to a table in a protected form' will copy the last row's fields with their formatting to a new row.
You will have to explain further about the 'Total form'.

[macropod]

For a working demo, see: http://www.msofficeforums.com/word-vba/13955-macro-add-row-table-word-form.html#post38312

[BradBerger]

Thank you for the replies guys, but right now the way I have the code setup works exactly the way I want it except for the fact that the form fields are bold and I'd like for them to be formatted a certain way instead of copying the format of the title columns of the table. Here is a screen shot of the table I am working with. This is how it would normally appear when you open the document to fill out the form.

19925

When you click the "Add Row" button, the table then looks like this...

19926

When you type in the forms, the text is bold because the previous row is bold. I want it to be regular text. The Qty column needs to be in a number format, and the "Amount" and "Total" need to be in this format: $0.00.

I hope this clears up any confusion. I'm trying to keep the code as simple as possible but I understand some things may be a bit complicated. Please let me know if there anything I need to change to simplify things.

[gmayor]

Clearly it doesn't work the way you want and the bold issue is the least of your problems. You need to set the two number field types to enable them to be calculated and include the calculation itself in the last column. Based on your example The following should be closer to what you have described. (see attached)


Option Explicit

Private Sub CommandButton1_Click()
Dim oTable As Table
Set oTable = ActiveDocument.Tables(7)
MakeNewRow oTable
lbl_Exit:
Set oTable = Nothing
Exit Sub
End Sub

Private Sub CommandButton2_Click()
Dim oTable As Table
Set oTable = ActiveDocument.Tables(7)
If Not ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Unprotect
End If
If oTable.Rows.Count > 2 Then
oTable.Rows.Last.Delete
End If
ActiveDocument.Protect wdAllowOnlyFormFields, NoReset:=True, _
Password:=""
lbl_Exit:
Set oTable = Nothing
Exit Sub
End Sub

Sub MakeNewRow(oTable As Table)
'Graham Mayor - http://www.gmayor.com - Last updated - 29 Jul 2017
Dim oNewRow As Row
Dim oRng As Range
Dim oCell As Cell
Dim iCell As Integer
If Not ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Unprotect
End If
Set oNewRow = oTable.Rows.Add
oNewRow.Range.Font.Bold = False
For iCell = 1 To 5
Set oCell = oNewRow.Cells(iCell)
Set oRng = oCell.Range
oRng.End = oRng.End - 1
Select Case iCell
Case 1
oRng.FormFields.Add _
Range:=oRng, _
Type:=wdFieldFormTextInput
With oRng.FormFields(1)
.Name = "Col" & iCell & "Row" & oCell.RowIndex
.TextInput.EditType _
Type:=wdNumberText, _
Default:="1", _
Format:="#,##0"
.CalculateOnExit = True
End With
Case 2, 3
oRng.FormFields.Add _
Range:=oRng, _
Type:=wdFieldFormTextInput
With oRng.FormFields(1)
.Name = "Col" & iCell & "Row" & oCell.RowIndex
End With
Case 4
oRng.FormFields.Add _
Range:=oRng, _
Type:=wdFieldFormTextInput
With oRng.FormFields(1)
.Name = "Col" & iCell & "Row" & oCell.RowIndex
.TextInput.EditType Type:=wdNumberText, _
Default:="$0.00", _
Format:="$#,##0.00"
.CalculateOnExit = True
End With
Case 5
oRng.FormFields.Add _
Range:=oRng, _
Type:=wdFieldFormTextInput
With oRng.FormFields(1)
.Select
.Name = "Col" & iCell & "Row" & oCell.RowIndex
.EntryMacro = ""
.ExitMacro = ""
.Enabled = False
.OwnHelp = False
.HelpText = ""
.OwnStatus = False
.StatusText = ""
With .TextInput
.EditType Type:=wdCalculationText, Default:= _
"=Col1Row" & oCell.RowIndex & " * Col4Row" & oCell.RowIndex, Format:="$#,##0.00"
.Width = 0
End With
End With

End Select
Next iCell
oNewRow.Cells(1).Select
ActiveDocument.Protect wdAllowOnlyFormFields, NoReset:=True, _
Password:=""

lbl_Exit:
Set oCell = Nothing
Set oRng = Nothing
Set oTable = Nothing
Set oNewRow = Nothing
Exit Sub
End Sub

[macropod]

Thank you for the replies guys, but right now the way I have the code setup works exactly the way I want it except for the fact that the form fields are bold and I'd like for them to be formatted a certain way instead of copying the format of the title columns of the table. Here is a screen shot of the table I am working with. This is how it would normally appear when you open the document to fill out the form.
The essential difference between your approach and the one in the links I posted is that you're starting out with a table that has only a header row (which doesn't make much sense, IMHO) and you have to click on an ActiveX control outside the table to add the row. The approach taken in the link I posted adds a new row with the same set of formfields as the row above. The code in the link I posted can easily-enough be modified to adjust the references for your calculation field.

Do note that ActiveX controls don't work on Macs so, if any of your users use Macs, that approach won't fly there.

[BradBerger]

You were right, it didnt work exactly how I wanted and now it works perfectly! Thank you so much for taking the time to write that up for me.

I have one more table after this one that needs a Date Picker and a couple Content Control Drop Down Lists. I've been experimenting with your code to add these content controls, but it seems they have to be set up differently. Here is the table:

19941

This one gets a little more complicated because I want the Time In and Time Out columns to have a drop down list so the user can select the time instead of typing it in - e.g., 12:00 AM, 12:15 AM, etc. This way, when both times have been chosen, the Total Hrs. column will display the sum. I'm guessing this is the ideal way to go about it since each item can be assigned a unique value that can be used for the calculation. Not sure though.

I was able to modify your code so when the button is clicked, a Date Picker is shown with "Select Date" as the placeholder text and my preferrred date format. Only problem is the placeholder text and date format works for the first added row, but any row after that it defaults back to the original text and date format. I also got the drop down lists in there, but couldnt figure out how to add entries.

Here's what I have so far:



Sub MakeLaborRow(oTable As Table)


Dim oNewRow As Row
Dim oRng As Range
Dim oCell As Cell
Dim iCell As Integer

If Not ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Unprotect
End If
Set oNewRow = oTable.Rows.Add
oNewRow.Range.Font.Bold = False
For iCell = 1 To 6
Set oCell = oNewRow.Cells(iCell)
Set oRng = oCell.Range
oRng.End = oRng.End - 1
Select Case iCell
Case 1
oRng.ContentControls.Add _
Range:=oRng, _
Type:=wdContentControlDate
With ContentControls(1)
.SetPlaceholderText , , "Select Date"
.DateDisplayFormat = "ddd MM/dd/yyyy"
End With
Case 2
oRng.FormFields.Add _
Range:=oRng, _
Type:=wdFieldFormTextInput
With oRng.FormFields(1)
.Name = "Col" & iCell & "Row" & oCell.RowIndex
End With
Case 3, 4
oRng.ContentControls.Add _
Range:=oRng, _
Type:=wdContentControlDropdownList
Case 5
oRng.FormFields.Add _
Range:=oRng, _
Type:=wdFieldFormTextInput
With oRng.FormFields(1)
.Select
.Name = "Col" & iCell & "Row" & oCell.RowIndex
.EntryMacro = ""
.ExitMacro = ""
.Enabled = False
.OwnHelp = False
.HelpText = ""
.OwnStatus = False
.StatusText = ""
With .TextInput
.EditType Type:=wdCalculationText, Default:= _
"=Col1Row" & oCell.RowIndex & " * Col4Row" & oCell.RowIndex, Format:="$#,##0.00"
.Width = 0
End With
End With
Case 6
oRng.FormFields.Add _
Range:=oRng, _
Type:=wdFieldFormTextInput
With oRng.FormFields(1)
.Name = "Col" & iCell & "Row" & oCell.RowIndex
.TextInput.EditType Type:=wdNumberText
End With

End Select
Next iCell
oNewRow.Cells(1).Select
ActiveDocument.Protect wdAllowOnlyFormFields, NoReset:=True, _
Password:=""

lbl_Exit:
Set oCell = Nothing
Set oRng = Nothing
Set oTable = Nothing
Set oNewRow = Nothing
Exit Sub
End Sub


Thanks again for your help. I've been working on this document for quite some time and been stuck on these two tables.

[macropod]

I have one more table after this one that needs a Date Picker and a couple Content Control Drop Down Lists. I've been experimenting with your code to add these content controls, but it seems they have to be set up differently.
The code to insert content controls is quite different from that used to insert formfields. Furthermore, you really shouldn't use content controls and formfields in the same document. They were not designed to be used that way and doing so can lead to unpredictable behaviour.

[gmayor]

Frankly you shouldn't be using content controls and legacy form fields in the same document. Personally, now that catering for Word 2003 is rarely an issue, I would use Content Controls throughout, though the only impediment to using legacy fields for what you appear to be attempting is the lack of a suitable date picker - however even that is not insurmountable using macros. In the second example, Trevor Eyre's vba date picker (http://trevoreyre.com/portfolio/excel-datepicker/) is used to prompt for the date.

Legacy form fields and content controls require different techniques and adding values to drop down content controls or form fields is fairly simple. - see attached examples.

Furthermore your code is trying to multiply a content control date in column 1 by a value from a content control drop down list value in column 4, using a calculated legacy form field, which is never going to work. With content controls you need to use the content control events, and because of that in order to determine which column you are processing, you cannot have merged cells in the table.

[BradBerger]

I took your advice and changed everything over to content controls to prevent any future problems. I spent a few hours reading through some of Greg Maxey's pages and experimenting with his content control math and I just cant figure out how to get this table to work. I tried to copy the way his multiplication function was coded because it's exactly how I want this table to behave. I've attached my document below and listed 4 problems within it. Thanks again for your help.

[gmayor]

Did you see the attachments in my last message?

[BradBerger]

Yes, I actually used your content control table and coding in the document I just attached. I changed the drop down lists to plain text because I didn't need the lists. I didn't touch the math functions because I knew they wouldn't work if I tried messing with them.

I apologize if im not making sense. I'm trying to describe things the best I can with the limited programming experience I have.

[BradBerger]

Spent some more time on this tonight and made good progress. Not perfect, but getting there.

When a new row is added, the code assigns each content control with an indexed tag (just like Graham originally had it). In this case, the tags are "Qty3" "Amount3" and "Total3". In the ContentControlOnExit sub, I have identified these content control tags so that the code recognizes them and calculates the total. Right now, the tags are set just to calculate the 3rd row of the table because I cant figure out how make the code recognize each newly added content control tag. Is there a way to change each Case to match the indexed content control tags?



Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oCC As ContentControl

Select Case CC.Tag
Case "Qty3"
If Not IsNumeric(CC.Range.Text) Then
Cancel = True
Beep
CC.Range.Select
Exit Sub
Else
CC.Range.Text = FormatValue(CC.Range.Text)
End If
Case "Amount3"
If Not IsNumeric(CC.Range.Text) Then
Cancel = True
Beep
CC.Range.Select
Exit Sub
Else
CC.Range.Text = FormatCurrency(CC.Range.Text)
End If
Set oCC = ActiveDocument.SelectContentControlsByTag("Total3").Item(1)
With oCC
.LockContents = False
.Range.Text = FormatCurrency(ProductAB(ActiveDocument.SelectContentControlsByTag("Qty3").Item(1).Range.Text, ActiveDocument.SelectContentControlsByTag("Amount3").Item(1).Range.Text))
.LockContents = True
End With
End Select
End Sub