Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-08-2013, 10:25 PM
Julia Julia is offline Using macro to add variable number of rows to a protected word table Windows 7 32bit Using macro to add variable number of rows to a protected word table Office 2010 32bit
Novice
Using macro to add variable number of rows to a protected word table
 
Join Date: Jan 2013
Posts: 1
Julia is on a distinguished road
Default Using macro to add variable number of rows to a protected word table

Hi there, I am trying to add a row or rows to a table in a protected word document. I have created the following macro that unprotects the document, adds the row, copies some drop-down fields into the row and then re-enforces the protection.

Sub Macro2()
'
' Macro2 Macro
'
'
ActiveDocument.Unprotect
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.InsertRows 1
Selection.Collapse Direction:=wdCollapseStart
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="2"
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.MoveRight Unit:=wdCharacter, Count:=5, Extend:=wdExtend
Selection.Copy
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.PasteAndFormat (wdPasteDefault)
Selection.MoveUp Unit:=wdLine, Count:=1

If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True
End If
End Sub

My issue is that I would like to run this macro (and other macros that add more lines) based on a number selected by the user in a drop-down box.

i.e. User selects #of items : '3' from a drop-down box. Based on the fact that '3' was selected, I want 3 rows added to the table.

It appears that I can only choose one macro to run on exit of the field, and not have a range of macros that are run based on the users selection.

Can anyone offer some assistance with this?



Thanks :-)
Reply With Quote
  #2  
Old 01-09-2013, 06:04 AM
gmaxey gmaxey is offline Using macro to add variable number of rows to a protected word table Windows 7 32bit Using macro to add variable number of rows to a protected word table Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,421
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Something like this:

Code:
Sub NewMultiRow()
Dim pTable As Word.Table
Dim bValid As Boolean
Dim curCursor As Long
Dim bCalcField As Boolean
Dim oRng1 As Word.Range
Dim oRng2 As Word.Range
Dim userInput As String
Dim rowsToAdd As Long
Dim rowAdd As Long
Dim oFF As Word.FormField
Dim oRowID As Long
Dim i As Long
Dim pNewName As String
Dim pNameSeparator As Long
Dim pRowIndex
Dim oBmName As String
Set pTable = ActiveDocument.Tables(1) 'As appropriate
             'Use Selection.Tables(1) if executing with an on exit macor
'Get user input of rows to add
bValid = False
Do
  userInput = InputBox("Enter number of rows to add", "Add Rows", 1)
  If userInput = vbNullString Then Exit Sub
  If userInput = "0" Then Exit Sub
  If IsNumeric(userInput) Then rowsToAdd = CLng(userInput)
  If rowsToAdd > 0 Then bValid = True
  If Not bValid Then
    MsgBox "You must use a positve numeric input e.g." & Chr(34) & "3" & Chr(34)
  End If
Loop Until bValid
'Minimize screen flicker
curCursor = System.Cursor
System.Cursor = wdCursorWait
Application.ScreenUpdating = False
'Determine if calculation fields are present and set a flag
On Error GoTo Err_Handler
Set oRng1 = pTable.Rows(pTable.Rows.Count - 2).Range '2 accounts for the trailing rows
bCalcField = False
For i = 1 To oRng1.FormFields.Count
  If oRng1.FormFields(i).TextInput.Type = wdCalculationText Then
    bCalcField = True
    Exit For
  End If
Next i
'Unprotect document.
If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
  ActiveDocument.Unprotect
End If
'Add individual rows
For rowAdd = 1 To rowsToAdd
  Set oRng1 = pTable.Rows(pTable.Rows.Count - 2).Range
  Set oRng2 = oRng1.Duplicate
  With oRng1
    .Copy
    .Collapse Direction:=wdCollapseEnd
    .Paste
  End With
  For i = 1 To oRng1.FormFields.Count
    oRowID = pTable.Rows.Count - 4 '4 accounts for the two leading and two
                                   'trailing.
    'Build and assign formfield bookmark names
    oRng1.FormFields(i).Select
    'Build new name
    pNewName = oRng2.FormFields(i).Name
    pNameSeparator = InStr(pNewName, "_Row")
    If pNameSeparator > 0 Then
      pNewName = Left(pNewName, pNameSeparator - 1)
    End If
    'Prevent assigning an existing bookmark name
    If ActiveDocument.Bookmarks.Exists(pNewName & "_Row" & oRowID) Then
      MsgBox "Invalid action.  A form field with the bookmark name " _
           & pNewName & "_" & oRowID _
           & " already appears this table.  Exiting this procedure."
      pTable.Rows(oRowID).Delete
      ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
      Exit Sub
    End If
      With Dialogs(wdDialogFormFieldOptions)
      .Name = pNewName & "_Row" & oRowID
      'Assign valid bookmark name to new formfield
      .Execute
    End With
    'This code could be used to clear previous on exit macros if used.
    'If oRng2.FormFields(i).ExitMacro = "NewMultiRow" Then
    '  oRng2.FormFields(i).ExitMacro = ""
    'End If
  Next
  'Call subroutine to build new calculation field
  If bCalcField Then
    BuildNewCalcFieldExpressions oRng1, oRng2
  End If
Next
pRowIndex = pTable.Rows.Count - rowsToAdd + 1 - 2 '2 accounts for the 2 trailing rows
oBmName = pTable.Rows(pRowIndex).Cells(1).Range.Bookmarks(1).Name
ActiveDocument.Bookmarks(oBmName).Range.Fields(1).Result.Select
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
'Restore visuals
Application.ScreenUpdating = True
System.Cursor = curCursor
Exit Sub
Err_Handler:
If Err.Number = 5991 Then
  MsgBox Err.Description
Else
  MsgBox "Unknown error."
End If
End Sub
Sub BuildNewCalcFieldExpressions(ByVal oRng1 As Range, oRng2 As Range)
'Construct any new calculation fields.  Credit for this section goes to fellow
'MVP Tony Jollans
Dim oFF As FormField
Dim strOldVar As String
Dim strNewVar As String
Dim strNewCalc As String
Dim ndx As Long
Dim ndx2 As Long
Dim lngVarPosit  As Long
Dim lngVarNextPosit  As Long
Dim bVariableFound As Boolean
Dim bVariableReplace As Boolean
For ndx = 1 To oRng1.FormFields.Count
  Set oFF = oRng1.FormFields(ndx)
  If oFF.Type = wdFieldFormTextInput Then
    If oFF.TextInput.Type = wdCalculationText Then
      strNewCalc = oFF.TextInput.Default
      For ndx2 = 1 To oRng2.FormFields.Count
        strOldVar = oRng2.FormFields(ndx2).Name
        lngVarPosit = 1
        Do While lngVarPosit > 0
          lngVarPosit = InStr(lngVarPosit, strNewCalc, strOldVar)
          bVariableFound = lngVarPosit > 0
          bVariableReplace = bVariableFound
          If bVariableReplace Then
            If lngVarPosit > 1 Then
              If Mid$(strNewCalc, lngVarPosit - 1) Like "[0-9A-Z_a-z]" Then
                bVariableReplace = False
              End If
            End If
          End If
          If bVariableReplace Then
            lngVarNextPosit = lngVarPosit + Len(strOldVar)
            If lngVarNextPosit <= Len(strNewCalc) Then
              If Mid$(strNewCalc, lngVarNextPosit) Like "[0-9A-Z_a-z]" Then
                bVariableReplace = False
              End If
            End If
          End If
          If bVariableReplace Then
            strNewVar = oRng1.FormFields(ndx2).Name
            strNewCalc = Left$(strNewCalc, lngVarPosit - 1) & strNewVar & Mid$(strNewCalc, lngVarNextPosit)
            lngVarPosit = lngVarPosit + Len(strNewVar)
          Else
            If bVariableFound Then
              lngVarPosit = lngVarPosit + Len(strOldVar)
            End If
          End If
        Loop
      Next ndx2
      oFF.Select
      With Dialogs(wdDialogFormFieldOptions)
        .TextDefault = strNewCalc
        .Execute
      End With
    End If
  End If
Next ndx
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using macro to add variable number of rows to a protected word table Mail Merge to create specific number of table rows flackend Mail Merge 4 12-01-2023 02:49 PM
Using macro to add variable number of rows to a protected word table Allow user to add rows to Word '03 table....how? PMR Word Tables 2 05-18-2012 06:16 AM
Help with VBA macro - Variable input sc30317 Excel Programming 0 08-31-2011 01:00 PM
Using macro to add variable number of rows to a protected word table adding rows to word table hklein Word VBA 4 07-18-2011 12:21 AM
Using macro to add variable number of rows to a protected word table Fixing number or rows in a table burnsie Word 2 07-12-2011 02:59 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:30 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