Stepping thru protected table cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

VBA Neophyte - 30 Jul 2007 13:48 GMT

I have a table which contains rows with different formats (some with merged
cells, some with split cells). It contains cells with form fields and cells
with plain text. It is protected.

There are some rows which, in cell 3, have a checkbox. If the checkbox
value in cell 3 = true, I want to copy cells 2, 4 and 5 of this row to a new
table at the end of the document or into a new table in a new separate
document. Cells 2 and 4 are plain text in; cell 5 is a form field.

I don't know how to do a copy statement for a range with some plain text
cells and a form field cell.

Also, this is pretty repetitive, so I was looking for something that would
just step through the table without having to specifically refer to formfield
Bookmark name, e.g. using a counter index to do the following:

i = count(rows in table)
use i to loop through the rows:
row(i) : if cell 3 contains a checkbox with value = Yes, copy contents of
cells 2, 4 and 5 to next line in new table*
(*create the table when you run into the first instance of cell 3
checkbox=true)

Reply to this Message

zkid - 31 Jul 2007 21:00 GMT

Okay, here's some code to get you started (by the way, I think you did a
great job of restating the issue - surprised no on else responded).

I'm going to have you figure out where exactly you want to paste the other
informaton (code below just copies the entire row to the end of the doc).
Due to the uneven table as a result of cell splits/merges, it's safer to
paste the entire row and then manipulate the cells within that pasted row
(assuming, at least, that the rows to be copied are formatted the same).

Dim iRow As Integer, myRange As Range

'The number in parens relates to the protected table's number -
'I suggest you keep it as the first table in the doc

'Setting a range will process only this table
Set myRange = ActiveDocument.Tables(1).Range

'You need to check all of the form fields in the correct range
For Each aField In myRange.FormFields
If aField.Type = wdFieldFormCheckBox Then
'Process only if True and in Column 3
If (aField.CheckBox.Value = True) And _
(myRange.Information(wdStartOfRangeColumnNumber) = 3) Then
'Need the row number too for copying correct table row
iRow = myRange.Information(wdEndOfRangeRowNumber)

'If protection is not passworded, then remove the password lingo
ActiveDocument.Unprotect Password:="myPassword"
myRange.Rows(iRow).Range.Copy

'The following will paste the row at the end of the current doc
Selection.EndKey Unit:=wdStory
Selection.PasteAndFormat (wdPasteDefault)
'put code here to manipulate pasted row to hold only needed info.
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End If
End If
Next aField

> I have a table which contains rows with different formats (some with merged
> cells, some with split cells). It contains cells with form fields and cells
[quoted text clipped - 18 lines]
> (*create the table when you run into the first instance of cell 3
> checkbox=true)

Reply to this Message

VBA Neophyte - 06 Aug 2007 20:56 GMT

zkid, thanks very much for the code, I understand a lot better how to
approach this. I'm also changing the design somewhat to make it more
standardized.

> Okay, here's some code to get you started (by the way, I think you did a
> great job of restating the issue - surprised no on else responded).
[quoted text clipped - 57 lines]
> > (*create the table when you run into the first instance of cell 3
> > checkbox=true)

Reply to this Message

zkid - 06 Aug 2007 23:16 GMT

You're most welcome. I hope it helps. I'll be gone for three weeks without
internet access, so I'll be unavailable for awhile. Good luck to you.

> zkid, thanks very much for the code, I understand a lot better how to
> approach this. I'm also changing the design somewhat to make it more
[quoted text clipped - 61 lines]
> > > (*create the table when you run into the first instance of cell 3
> > > checkbox=true)

Reply to this Message