Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-10-2017, 05:41 PM
Pindar Pindar is offline Trying to add rows to a protected document with form fields Windows 7 64bit Trying to add rows to a protected document with form fields Office 2010 64bit
Novice
Trying to add rows to a protected document with form fields
 
Join Date: Jan 2017
Posts: 12
Pindar is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-10-2017, 11:21 PM
gmayor's Avatar
gmayor gmayor is offline Trying to add rows to a protected document with form fields Windows 10 Trying to add rows to a protected document with form fields Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #3  
Old 01-11-2017, 01:08 AM
macropod's Avatar
macropod macropod is online now Trying to add rows to a protected document with form fields Windows 7 64bit Trying to add rows to a protected document with form fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #4  
Old 01-11-2017, 04:53 PM
Pindar Pindar is offline Trying to add rows to a protected document with form fields Windows 7 64bit Trying to add rows to a protected document with form fields Office 2010 64bit
Novice
Trying to add rows to a protected document with form fields
 
Join Date: Jan 2017
Posts: 12
Pindar is on a distinguished road
Default

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?
Reply With Quote
  #5  
Old 01-11-2017, 05:13 PM
macropod's Avatar
macropod macropod is online now Trying to add rows to a protected document with form fields Windows 7 64bit Trying to add rows to a protected document with form fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #6  
Old 01-11-2017, 06:08 PM
Pindar Pindar is offline Trying to add rows to a protected document with form fields Windows 7 64bit Trying to add rows to a protected document with form fields Office 2010 64bit
Novice
Trying to add rows to a protected document with form fields
 
Join Date: Jan 2017
Posts: 12
Pindar is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 01-11-2017, 09:55 PM
macropod's Avatar
macropod macropod is online now Trying to add rows to a protected document with form fields Windows 7 64bit Trying to add rows to a protected document with form fields Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #8  
Old 01-11-2017, 10:46 PM
Pindar Pindar is offline Trying to add rows to a protected document with form fields Windows 7 64bit Trying to add rows to a protected document with form fields Office 2010 64bit
Novice
Trying to add rows to a protected document with form fields
 
Join Date: Jan 2017
Posts: 12
Pindar is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 01-11-2017, 10:47 PM
gmayor's Avatar
gmayor gmayor is offline Trying to add rows to a protected document with form fields Windows 10 Trying to add rows to a protected document with form fields Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

As I said earlier the macro I posted works with your document (as long as the document is protected for forms) see attached
Attached Files
File Type: doc EC.doc (114.5 KB, 45 views)
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #10  
Old 01-11-2017, 10:57 PM
Pindar Pindar is offline Trying to add rows to a protected document with form fields Windows 7 64bit Trying to add rows to a protected document with form fields Office 2010 64bit
Novice
Trying to add rows to a protected document with form fields
 
Join Date: Jan 2017
Posts: 12
Pindar is on a distinguished road
Default

Thank you gmayor! It's perfect. I'll use it. You guys are gods among men.
Reply With Quote
  #11  
Old 01-12-2017, 11:02 AM
Pindar Pindar is offline Trying to add rows to a protected document with form fields Windows 7 64bit Trying to add rows to a protected document with form fields Office 2010 64bit
Novice
Trying to add rows to a protected document with form fields
 
Join Date: Jan 2017
Posts: 12
Pindar is on a distinguished road
Default

gmayor, how should I change the code if I want it to add a second page full of identical rows all at once?
Reply With Quote
Reply

Tags
macro, protected



Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to add rows to a protected document with form fields 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:23 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft