#1
|
|||
|
|||
Need assistance Adding Row to Table with Permission
Good morning good people of the internet!
I am using the following code to add a new report section to a report. This report needs to be added when there's multiple subjects to a report. Problem lies when someone accidentally adds a new section without meaning to, and there's no way to remove it because everything is locked down. Can someone assist me with adding a permission (Do you want to add a new row? Dialog) to this code? I didn't write this code myself, but found it somewhere. Any help would be greatly appreciated!! 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 Application.ScreenUpdating = False Dim RngSel As Range, RngSrc As Range, RngTgt As Range Dim FmFld As FormField, Prot As Variant If MsgBox("Add new row?", vbQuestion + vbYesNo) = vbYes Then Set RngSel = Selection.Cells(1).Range.FormFields(1).Range With ActiveDocument Prot = .ProtectionType If .ProtectionType <> wdNoProtection Then Prot = .ProtectionType .Unprotect Password:=sPassword End If 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 a range to the last cell0 Set oLastCell = oTable.Cell(iRow, iCol).Range 'Record the last cell field value Select Case oLastCell.FormFields(1).Type Case 70 'wdFieldFormTextInput sResult = oLastCell.FormFields(1).Result Case 71 'wdFieldFormCheckBox sResult = oLastCell.FormFields(1).CheckBox.Value Case 83 'wdFieldFormDropDown sResult = oLastCell.FormFields(1).DropDown.Value End Select '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).Select 'Remove the macro oLastCell.FormFields(1).ExitMacro = "" 'Restore the field value according to type Select Case oLastCell.FormFields(1).Type Case 70 oLastCell.FormFields(1).Result = sResult Case 71 oLastCell.FormFields(1).CheckBox.Value = sResult Case 83 oLastCell.FormFields(1).DropDown.Value = sResult End Select .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 With End If End Sub |
#2
|
||||
|
||||
For some simple code to add a new row - with a proper coding structure - see: https://www.msofficeforums.com/word-...html#post38312
Regardless, deleting a row added in error would require a significant re-write. Better to be careful rather than trying to work around carelessness. You could, of course, unprotect the document to delete the unwanted row manually.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hi Paul!
The problem lies with the end user, which is why I'd like a "you sure you want to add a row" permission as in your example. I would just love to add that part of your code in with greg's but can't seem to do it, as it seems to be beyond my capabilities. |
#4
|
||||
|
||||
You would use my code instead of your existing code, not in addition to it.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thanks Paul,
I also tried that just replacing the code and it just wasn't working. I will give it another go! thanks again for the hand! |
#6
|
|||
|
|||
After reading the template it seemed by adding a "row" you meant a whole new table for a second or third "Suspect" The attached template has buttons:
1. "New Suspect" 2. "Delete last Table" 3. "Print" Pressing the "new suspect" button unprotects the document, copies the data table and pastes it below the first one then re-protects the document. If you press it again it will keep adding a new table under the table so it's probably best to add all the new tables you need before you start filling them in as it copies everything including the text box contents. The "Delete last table" button doesn't actually delete the last table, it deletes whatever table the cursor is in. Pressing the button unprotects the document, deletes the data table that has the cursor in it and then re-protects the document. The "Print" button hides the buttons before printing and the reshows them. You'll need to give the document your own password and enter it into the code in two places where it says "sPassword = """ add it between the quote marks. Hope this helps. Regards You'll need to add your password to the code |
#7
|
|||
|
|||
Here is another version with a yes no message box to make sure you want to add another suspect table and one to make sure your cursor is in the table you want to delete
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Assistance please in adding more data to Existing chart. | snowboarder2 | Excel | 3 | 09-28-2017 11:50 PM |
adding a dropdown in a table | airfrm | OneNote | 1 | 08-08-2017 09:47 AM |
adding a dropdown in a table | airfrm | OneNote | 0 | 08-08-2017 05:09 AM |
Adding ContentControl to a table | milena | Word VBA | 2 | 04-20-2015 01:04 AM |
adding table to a particular place | hklein | Word VBA | 2 | 07-25-2011 01:45 AM |