Deleting table based on contents of a bookmark

[Peter]

I appreciate the help received from Doug and Jean-Guy in
response to my November 11 post concerning deleting tables
based on the contents of a particular bookmark in a forty-
page Word 2000 form used to collect information from
faculty about their activities.

The code works perfectly, and is included at the end of
this post for reference.

Four questions about our project have come up that I am
unable to answer, no doubt because this is my first vba
project. I have looked through the MVPS Word FAQs, and the
subject lines in the vba.beginners and vba.general lists,
but haven't found solutions yet, though I may have
overlooked a posting that would be helpful.

1. How do I include my vba code as part of a Word 2000
form emailed as an attachment?

I tried to email a test version of the form to a colleague
and myself so we could test it, but found that no vba code
was included along with the attachment when I opened the
form and then the VB Editor (from Tools->Macro->VB
Editor). I believe I correctly saved the code along with
the document (not in Normal) before sending the email with
attachment, but there was no sign of any vba code.

I emailed myself the code separately, and pasted it into
the code window of the VB Editor, and then it works fine.
But this is too complicated for the faculty to do.

I've read 'Distributing macros to other users' and 'What
do I do with macros sent to me by other newsgroup readers
to help me out?' but neither really addresses how one
sends a Word form as an attachment when the form includes
vba. The fact that I haven't found any FAQ references to
what must be a common activity, leads me to conclude that
I'm missing something really obvious!

2. How do I make a macro button run the vba code
associated with my form?

The code works fine when I run it from within the VB
Editor using the 'Step Into' button where I can observe
its operation, and also runs fine from the 'Run' button,
but only when the cursor is at the first line of the Sub()
in the code window.

I'm not clear about how to attach my macro button to the
code it is supposed to activate: when I click on the macro
button, the cursor simply moves into the first field in my
form, if I double click, the cursor moves into the second
field in the form, but in either case does not run the Sub
code as I would have expected. I do of course have the
name of the vba routine in the macrobutton. The macro
button is located at the end of section 3 (please see
following question).

3. How do I run the 'delete empty tables' code on only
sections 2 and 3 of the three-part form?

The form is in three sections, with a section break
following each of the first two sections with each new
section starting on the next page (Insert->Break-

PageBreak->Next Page). We want to run the 'delete empty

table' code only on sections two and three, and include
the related numeric total (from Cell 1,1 of any remaining
tables) at the end of section two or three, rather than
the one GrandTotal that is generated now.

4. In the tables that remain after running the 'delete
empty tables' code, how do I delete rows that haven't been
used and therefore have no text in their text bookmarks,
along with completely empty 'spacer rows' (for
readability) located between rows containing text?

After looking at the results from running the code as it
is now, it occurred to us that a cleaner document would
result, if within a table that _contains_ text in its
first text bookmark (which is always row 3, column 2), it
would be possible to delete any rows that follow but that
may not contain any text. These rows may be completely
empty 'spacer rows' inserted for readability between rows
with text bookmarks, or simply another row like row 3 that
contains a text bookmark but no text in the bookmark.
Tables may contain between 2 and 16 rows having text
bookmarks.

I think the code to do this would need to:

a. Look for the last row in a table that has text in
column 2 (not just the existence of a text field, but a
text field with a Len of > 5 which seems to be the default
length of my text bookmarks for reasons I don't understand
at this point)

b. Delete rows (if any) in the table that follow this last
row.

Thank you very much for any assistance you are able to
provide with these four questions.

Peter

The current version of the code provided by Doug with
additions by Jean-Guy follows:

Sub FSR()

Dim myrange As Range, i As Integer, GTotal As Long
ActiveDocument.Fields.Update
ActiveDocument.Unprotect
For i = ActiveDocument.Tables.Count To 1 Step -1
Set myrange = ActiveDocument.Tables(i).Cell(3, 2).Range
myrange.End = myrange.End - 1

' Check row 3 column 2 to see if empty-default empty
length is 5
If Len(myrange) = 5 Then
Set myrange = myrange.Tables(1).Range
myrange.SetRange Start:=myrange.Start, _
End:=myrange.End + 1
myrange.Delete
End If
Next i

ActiveDocument.Protect wdAllowOnlyFormFields, NoReset
ScholTotal = 0
For i = 1 To ActiveDocument.Tables.Count
ScholTotal = ScholTotal + ActiveDocument.Tables(i).Cell
(1, 1).Range.FormFields(1).Result
Next i
ActiveDocument.FormFields("ScholTotal").Result =
ScholTotal

End Sub

 

[JGM]

Hi Peter,

Let me try to give you some pointers based on my limited experience
(Compared to Doug's...)
(Note that all the code I suggest below is thouroughly untested... I am
writing it on the fly...)

I appreciate the help received from Doug and Jean-Guy in
response to my November 11 post concerning deleting tables
based on the contents of a particular bookmark in a forty-
page Word 2000 form used to collect information from
faculty about their activities.

The code works perfectly, and is included at the end of
this post for reference.

Four questions about our project have come up that I am
unable to answer, no doubt because this is my first vba
project. I have looked through the MVPS Word FAQs, and the
subject lines in the vba.beginners and vba.general lists,
but haven't found solutions yet, though I may have
overlooked a posting that would be helpful.

1. How do I include my vba code as part of a Word 2000
form emailed as an attachment?

I presume that your code resides in a template (*.dot) and that you created
the form based on that template. When you do that, while working on the same
computer, the doc knows where to get the macros, but they are not part of
the doc itself. The doc only contains a reference to the template. Which is
why it does not work when you email it, since you are emailing a doc with
references to a template that resides on your machine, so your users'
application cannot find it...
In your case, I would suggest that once your code is satisfactory and
complete, that you import it into the doc itself and that you then detach
the doc from the template and attach it to Normal.dot (Tools > Templates and
add-ins...), to create on your machine a situation similar to your users'...
This should work, but bear in mind that if your users have their security
set to high (Word 2000 and higher) the macros are automatically disabled.

I tried to email a test version of the form to a colleague
and myself so we could test it, but found that no vba code
was included along with the attachment when I opened the
form and then the VB Editor (from Tools->Macro->VB
Editor). I believe I correctly saved the code along with
the document (not in Normal) before sending the email with
attachment, but there was no sign of any vba code.

I emailed myself the code separately, and pasted it into
the code window of the VB Editor, and then it works fine.
But this is too complicated for the faculty to do.

I've read 'Distributing macros to other users' and 'What
do I do with macros sent to me by other newsgroup readers
to help me out?' but neither really addresses how one
sends a Word form as an attachment when the form includes
vba. The fact that I haven't found any FAQ references to
what must be a common activity, leads me to conclude that
I'm missing something really obvious!

2. How do I make a macro button run the vba code
associated with my form?

I do not know what is the problem here... If the code is where you say it
is, if you typed the macro name accurately, if you have saved the whole
thing... then the MACROBUTTON should work when you double click on it.

The code works fine when I run it from within the VB
Editor using the 'Step Into' button where I can observe
its operation, and also runs fine from the 'Run' button,
but only when the cursor is at the first line of the Sub()
in the code window.

I'm not clear about how to attach my macro button to the
code it is supposed to activate: when I click on the macro
button, the cursor simply moves into the first field in my
form, if I double click, the cursor moves into the second
field in the form, but in either case does not run the Sub
code as I would have expected. I do of course have the
name of the vba routine in the macrobutton. The macro
button is located at the end of section 3 (please see
following question).

3. How do I run the 'delete empty tables' code on only
sections 2 and 3 of the three-part form?

Like this:

Dim DocRange As Range
Dim myDoc As Document

Set myDoc = ActiveDocument
Set DocRange = myDoc.Range

DocRange.SetRange myDoc.Sections(2).Range.Start, myDoc.Range.End

Then, in your code replace the ActiveDocument in

For i = ActiveDocument.Tables.Count To 1 Step -1

where it should be:
For i = DocRange.Tables.Count To 1 Step -1
and in

Set myrange = ActiveDocument.Tables(i).Cell(3, 2).Range where it should be
Set myrange = DocRange.Tables(i).Cell(3, 2).Range

and so on... It's getting quite late, I hope I am still making sense!

The form is in three sections, with a section break
following each of the first two sections with each new
section starting on the next page (Insert->Break-
table' code only on sections two and three, and include
the related numeric total (from Cell 1,1 of any remaining
tables) at the end of section two or three, rather than
the one GrandTotal that is generated now.

4. In the tables that remain after running the 'delete
empty tables' code, how do I delete rows that haven't been
used and therefore have no text in their text bookmarks,
along with completely empty 'spacer rows' (for
readability) located between rows containing text?

Three comments here:

1) I think that it would be easier to use paragrpah formatting to enhance
readability, as in "Space before" and "Space after".
Then, at the end, when you want to print and squeeze stuff together to save
paper, reduce those spaces. The easiest is to have two paragraph styles .
One for readability and one for Printing. Your macro would only need to
replace all former style by the latter. It would make for code easier to
write and maintain.

2) For the rows where the bookmarks are empty... If you have decided to use
something like I suggested above, then half your job is already done!
For the rows with empty comments, first you have to test for all the
relevant cells in the table to make sure that the bookmarks are empty. You
can do that by adding another loop that will iterate through the cells in
the current table (Tables(i)...). You will need to get the row count (I
think that column count is static, right?).
Something like:

myRowCount = DocRange.Tables(i).Rows.Count

'Then iterate through the cells:
Dim CellRange as Range
Dim MyCounter as Integer
For j = 4 to myRowCount
For k = 1 to 4 '4 = hypothetical column count...
MyCounter = 0
Set CellRange = DocRange.Tables(i).Cell(j, k).Range
'Here use code similar to what you already have
If CellRange = 'empty then
'you may need some kind of counter everytime you find an empty
'boookmark in a row , if you can have 2 bookmark in a row, and both
'are empty then MyCounter = 2
MyCounter = MyCounter + 1
End If
Next k
If MyCounter = 2 then
DocRange.Tables(i).Rows(j).Delete

'if it is guaranteed that as soon that you find an empty row
'that all rows that follow are also empty
'But I would be wary of this... what if a user skipped a row without
'realizing it? YOu would end up deleting rows with text...
'Anyway, you know best who your users are...
'you may want to use code that will speed up the whole thing,
'something like
For h = myRowCount To j Step -1
DocRange.Tables(i).Rows(h).Delete
Next h
GoTo GetNextTable
'or
j = myRowCount 'This will end the first loop, I think...
End If
Next j
GetNextTable:
etc.

3) Unless I am mistaken, I see that you are using

If Len(myrange) = 5 Then

to check whether a bookmark is empty...
What if it contains a 5 characters, like "Maybe" or "N / A"? Is that
considered empty?
You were wondering why the "Empty" Bookmark had a length of 5.
Well, when you insert a formfield in a document, the default text is 5
spaces, or a length of 5.
Try using this instead, or some variation on the theme, depending on your
actual situation:

If Trim(myrange.Text) = "" Then

Which really means that there is no text at all in the bookmark... Anyway,
test it and see what works best for you, unless you deem it impossible that
a user would happen to have 5 valid characters in a bookmark... Just a
thought!

HTH
Cheers!

 

[Peter]

Jean-Guy,

Thank you very much for taking the time to put together
your detailed suggestions. I have started working with
your ideas today, and will get as far as I can before
requesting further assistance.

Peter