#1
|
|||
|
|||
Trying to add rows to a protected document with form fields
I noticed that this question has been asked several times already. I know macropod is probably tired of answering the question. Unfortunately I wasn't able to figure out how to do this by looking at past posts. I just can't interpret the code when I read it, and I haven't found a code that works. I want to add a row to the bottom table on this page whenever the user exits the last cell, and it should repeat as the user exits the last cell again on the new row. It should occur as many times as is needed. It is the third (last) table in the file. It is a protected document with form fields and it needs to be re-protected at the end of the macro. Please any help would be appreciated I have attached an anonymous version of the file to this post here. EC..doc Thank you |
#2
|
||||
|
||||
See 'Add a row to a table in a protected form' at http://www.gmayor.com/word_vba_examples_2.htm The following code from that page should work with your table when added to the last field in the last row of the table in your document and protection applied. See the web page for versions for text and checkbox form fields.
Code:
Sub AddRow() 'Run on exit from the last form field in 'the last row of the table Dim oTable As Table Dim oRng As Range Dim oNewRow As Range Dim oCell As Range Dim oLastCell As Range Dim sResult As String Dim iRow As Long Dim iCol As Long Dim CurRow As Long Dim i As Long, j As Long Dim sPassword As String sPassword = "" 'password to protect/unprotect form With ActiveDocument .Unprotect Password:=sPassword 'Unprotect document Set oTable = Selection.Tables(1) 'Establish which table the cursor is in For j = 1 To .Tables.Count If oTable.Range.Start = .Tables(j).Range.Start Then 'Table is j Exit For 'Stop checking End If Next j 'Select the appropriate table iCol = oTable.Columns.Count 'Record the last column number Set oLastCell = oTable.Cell(iRow, iCol).Range 'Record the last cell sResult = oLastCell.FormFields(1).DropDown.Value 'Get the value in the last cell Set oRng = oTable.Rows.Last.Range 'Add the last row to a range Set oNewRow = oTable.Rows.Last.Range 'Add the last row to another range oNewRow.Collapse wdCollapseEnd 'Collapse the second range to the end of the table oNewRow.FormattedText = oRng 'insert the content of the last row into the new range 'thereby adding a new row with the same content as the last row CurRow = oTable.Rows.Count 'Determine the new last row of the table For i = 1 To iCol 'Repeat for each column Set oCell = oTable.Cell(CurRow, i).Range 'process each cell in the row oCell.FormFields(1).Select 'Select the first field in the cell With Dialogs(wdDialogFormFieldOptions) 'and name it .Name = "Tab" & j & "Col" & i & "Row" & CurRow 'eg Tab1Col1Row2 .Execute 'apply the changes End With Next i 'Select the formfield in the last cell of the previous row oLastCell.FormFields(1).ExitMacro = "" oLastCell.FormFields(1).DropDown.Value = sResult 'so restore the result of the cell .Protect NoReset:=True, _ Password:=sPassword, _ Type:=wdAllowOnlyFormFields 'Reprotect the form .FormFields("Tab" & j & "Col1Row" _ & CurRow).Select 'and select the next field to be completed End With lbl_Exit: Set oTable = Nothing Set oRng = Nothing Set oNewRow = Nothing Set oCell = Nothing Set oLastCell = Nothing 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
|
||||
|
||||
You should be able to use the macro in the document attached to https://www.msofficeforums.com/word-...html#post38312 as is. All you need do is add an 'on exit' reference to the AddRow macro to the final formfield in your table.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Thank you macropod. I had seen that post but I'm new to VB and didn't know how to apply it. I believe I have it working now but it runs very slowly. It takes 2-3 seconds to add a row. Is there a way to fix that?
|
#5
|
||||
|
||||
The speed will depend on your system and the formatting of the table concerned. On mine, it takes less than a second for the demo file - even when I've added enough new rows to spill over onto a second mage. If your table is formatted to allow auto-fitting, turning that off (via Table Tools>Layout>Properties>Table>Options) can improve performance.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
The table is set to have fixed width, and when following your string of clicks, "automatically resize to fit contents" is not checked. Your sample table also works instantly for me, but my form does not. If there is no fix, that is fine. It's already much faster than adding a row manually.
I have one more issue though. Running the macro causes the first box at the top of the document to be selected. I want the first box in the new row to be selected instead. I tried and failed to fix this by editing the very end of your code here: Code:
Option Explicit Sub AddRow() Application.ScreenUpdating = False 'This macro adds another row to the table. ' If you need the formfields named, activate the commented-out code. ' As coded, the formfield names assume each column uses a different formfield ' name ending in a two-digit number corresponding to the formfield row. For ' example, formfield names on tabe row 2, which is formfield row 1, end with 01. Dim i As Long, j As Long, FmFld As FormField, Prot As Variant 'Dim StrNms As String Const Pwd As String = "pswrd" 'Insert password here With Selection i = .Tables(1).Range.FormFields.Count j = ActiveDocument.Range(.Tables(1).Range.Start, .Range.End).FormFields.Count End With If i = j Then If MsgBox("Add new row?", vbQuestion + vbYesNo) = vbYes Then With ActiveDocument Prot = .ProtectionType If .ProtectionType <> wdNoProtection Then Prot = .ProtectionType .Unprotect Password:=Pwd End If With Selection.Tables(1).Rows i = .Count With .Last.Range 'For Each FmFld In .FormFields 'StrNms = StrNms & "|" & Left(FmFld.Name, Len(FmFld.Name) - 2) & Format(i, "00") 'Next .Next.InsertBefore vbCr .Next.FormattedText = .FormattedText End With With .Last.Range.FormFields For i = 1 To .Count With .Item(i) If .Type = wdFieldFormCheckBox Then .CheckBox.Value = False If .Type = wdFieldFormTextInput Then .TextInput.Clear If .Type = wdFieldFormDropDown Then .DropDown.Value = .DropDown.Default '.Select 'With Dialogs(wdDialogFormFieldOptions) '.Name = Split(StrNms, "|")(i) '.Execute 'End With End With Next .Item(1).Select End With End With .Protect Type:=Prot, Password:=Pwd, Noreset:=True End With End If End If Application.ScreenUpdating = True Call Macro1 End Sub Code:
Sub Macro1() ' ' Macro1 Macro ' test, go to paart number after add line ' Selection.EndKey Unit:=wdStory Selection.MoveLeft Unit:=wdCharacter, Count:=45 End Sub Last edited by Pindar; 01-11-2017 at 10:35 PM. |
#7
|
||||
|
||||
When I use a form with the AddRow macro (e.g. the attached one in the link), the first formfield on the new row is selected - which is how it was coded - not the first formfield in the document.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
I'm using your code on my document in Word 2016 and it selects the first drop-down menu in the document upon finishing the macro. I have not changed any part of the code except adding "pswrd" to the password section. Here is the exact file I'm using after applying your code. EC..doc
|
#9
|
||||
|
||||
As I said earlier the macro I posted works with your document (as long as the document is protected for forms) see attached
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#10
|
|||
|
|||
Thank you gmayor! It's perfect. I'll use it. You guys are gods among men.
|
#11
|
|||
|
|||
gmayor, how should I change the code if I want it to add a second page full of identical rows all at once?
|
Tags |
macro, protected |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Delete Rows in Protected Table with Form Fields | Elan05 | Word VBA | 23 | 09-11-2014 12:47 PM |
Spellcheck macro for protected form fields needed | rharvey1215 | Word | 12 | 03-31-2014 06:47 PM |
Add rows in protected table with Form Fields | Apriljade | Word | 2 | 02-26-2014 06:42 AM |
Inserting spreadsheet data rows as form fields in a document | b3nz | Word | 3 | 03-31-2013 07:47 PM |
Editing Password protected form fields in Word 2007 | tamilan | Word | 2 | 02-16-2010 09:45 AM |