Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-19-2014, 12:20 PM
saundrals saundrals is offline Automatically insert new row? Windows 7 64bit Automatically insert new row? Office 2010 64bit
Novice
Automatically insert new row?
 
Join Date: Jun 2014
Posts: 4
saundrals is on a distinguished road
Default Automatically insert new row?

Hi Everyone,


I'm trying to make a form that contains a table in which people will fill in record information in each row. each row represents a separate record. I can only get so many rows to fit on a page... is there any way to have a row automatically insert itself when they get to the last row? We had an extra page of rows, but we would rather not waste paper (this form will be printed hundreds of times). There are two other pages after the page with the table. Any ideas?
Reply With Quote
  #2  
Old 06-19-2014, 03:49 PM
macropod's Avatar
macropod macropod is offline Automatically insert new row? Windows 7 32bit Automatically insert new row? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

See 'Add a row to a table in a protected form' at: http://www.gmayor.com/word_vba_examples.htm.
See also: https://www.msofficeforums.com/word-...html#post38461
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 06-24-2014, 09:35 AM
saundrals saundrals is offline Automatically insert new row? Windows 7 64bit Automatically insert new row? Office 2010 64bit
Novice
Automatically insert new row?
 
Join Date: Jun 2014
Posts: 4
saundrals is on a distinguished road
Default

Thank you!
Now I am using second macro from the first link you gave me. It is successfully adding the next row (yay!) but is not copying the form fields and drop downs from the row above... here is the macro I'm using. can someone point out were I am going wrong?
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 
Dim sPassword As String 
sPassword = "GRIN" 
 'password to protect/unprotect form
With ActiveDocument 
    .Unprotect Password:=sPassword 
     'Unprotect document
    Set oTable = Selection.Tables(2) 
     '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).Result '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 = "Col12" & i & "Row9" & CurRow 'eg Col1Row2
            .Execute 'apply the changes
        End With 
    Next i 
     'Select the formfield in the last cell of the previous row
    oLastCell.FormFields(1).Select 
    With Dialogs(wdDialogFormFieldOptions) 
        .Exit = "" 'and remove the exit macro
        .Execute 'apply the changes
         'but note that this clears the value from the cell
    End With 
    oLastCell.FormFields(1).Result = sResult 'so restore the result of the cell
    .Protect NoReset:=True, _ 
    Password:=sPassword, _ 
    Type:=wdAllowOnlyFormFields 
     'Reprotect the form
    .FormFields("Col1Row" _ 
    & CurRow).Select 'and select the next field to be completed
End With 
End Sub 
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 
    Dim sPassword As String 
    sPassword = "GRIN" 
     'password to protect/unprotect form
    With ActiveDocument 
        .Unprotect Password:=sPassword 
         'Unprotect document
        Set oTable = Selection.Tables(2) 
         '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).Result '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 = "Col12" & i & "Row9" & CurRow 'eg Col1Row2
                .Execute 'apply the changes
            End With 
        Next i 
         'Select the formfield in the last cell of the previous row
        oLastCell.FormFields(1).Select 
        With Dialogs(wdDialogFormFieldOptions) 
            .Exit = "" 'and remove the exit macro
            .Execute 'apply the changes
             'but note that this clears the value from the cell
        End With 
        oLastCell.FormFields(1).Result = sResult 'so restore the result of the cell
        .Protect NoReset:=True, _ 
        Password:=sPassword, _ 
        Type:=wdAllowOnlyFormFields 
         'Reprotect the form
        .FormFields("Col1Row" _ 
        & CurRow).Select 'and select the next field to be completed
    End With 
End Sub

Last edited by macropod; 06-24-2014 at 04:01 PM. Reason: Added code tags & formatting
Reply With Quote
  #4  
Old 06-24-2014, 04:08 PM
macropod's Avatar
macropod macropod is offline Automatically insert new row? Windows 7 32bit Automatically insert new row? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

Cross-posted at: http://www.vbaexpress.com/forum/show...198#post311198
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

Without placing too fine appoint on it, the code you've posted is for formfields, not content controls. Your thread title at VBAX refers to content controls but the body refers to formfields. These are not the same. Which are you using?

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert page automatically Glitch Word VBA 10 08-01-2018 06:46 AM
Automatically insert new row? Insert Filename automatically when open vicmar Word VBA 8 08-31-2017 02:49 AM
Automatically insert the name of the user MiniMum97 Word 1 04-12-2016 03:40 AM
Automatically insert new row? automatically insert an image into a document hanrattyc Word 1 05-13-2013 04:27 PM
Automatically insert new row? How to automatically insert DAY instead of date? kylera Word 3 06-28-2012 12:38 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:41 AM.


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