Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-28-2019, 07:22 AM
ohi ohi is offline Need assistance Adding Row to Table with Permission Windows 7 64bit Need assistance Adding Row to Table with Permission Office 2007
Novice
Need assistance Adding Row to Table with Permission
 
Join Date: Dec 2014
Posts: 6
ohi is on a distinguished road
Default 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
Attached Files
File Type: dotm SAMPLE TEMPLATE.dotm (57.7 KB, 11 views)
Reply With Quote
  #2  
Old 05-29-2019, 03:37 AM
macropod's Avatar
macropod macropod is online now Need assistance Adding Row to Table with Permission Windows 7 64bit Need assistance Adding Row to Table with Permission 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

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]
Reply With Quote
  #3  
Old 05-29-2019, 06:27 AM
ohi ohi is offline Need assistance Adding Row to Table with Permission Windows 7 64bit Need assistance Adding Row to Table with Permission Office 2007
Novice
Need assistance Adding Row to Table with Permission
 
Join Date: Dec 2014
Posts: 6
ohi is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 05-29-2019, 03:18 PM
macropod's Avatar
macropod macropod is online now Need assistance Adding Row to Table with Permission Windows 7 64bit Need assistance Adding Row to Table with Permission 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

Quote:
Originally Posted by ohi View Post
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.
You would use my code instead of your existing code, not in addition to it.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-30-2019, 10:03 AM
ohi ohi is offline Need assistance Adding Row to Table with Permission Windows 7 64bit Need assistance Adding Row to Table with Permission Office 2007
Novice
Need assistance Adding Row to Table with Permission
 
Join Date: Dec 2014
Posts: 6
ohi is on a distinguished road
Default

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!
Reply With Quote
  #6  
Old 05-30-2019, 05:16 PM
kilroy kilroy is offline Need assistance Adding Row to Table with Permission Windows 10 Need assistance Adding Row to Table with Permission Office 2016
Competent Performer
 
Join Date: Sep 2016
Location: Southern Ontario
Posts: 118
kilroy is on a distinguished road
Default

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
Attached Files
File Type: dotm New Suspect Sheet R0.dotm (55.3 KB, 5 views)
Reply With Quote
  #7  
Old 05-30-2019, 07:20 PM
kilroy kilroy is offline Need assistance Adding Row to Table with Permission Windows 10 Need assistance Adding Row to Table with Permission Office 2016
Competent Performer
 
Join Date: Sep 2016
Location: Southern Ontario
Posts: 118
kilroy is on a distinguished road
Default

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
Attached Files
File Type: dotm New Suspect Sheet R1.dotm (58.7 KB, 6 views)
Reply With Quote
Reply

Thread Tools
Display Modes


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
Need assistance Adding Row to Table with Permission adding table to a particular place hklein Word VBA 2 07-25-2011 01:45 AM

Other Forums: Access Forums

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