Strange Error

[Al & Cay Grant]

Hi Folks,

I added the following code to my project to calculate a persons age and
automatically update a text box on the user form.

Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)

' CALCULATES AGE

If Not IsDate(TextBox4.Value) Then
Cancel = True
Exit Sub
End If
TextBox5.Value = Int(DateDiff("d", TextBox4, Now) / 365.25)

End Sub



Problem now is that I get requested member of collection does not exist in
the
following code at the point I have marked with a "--->>>>"

I think I am getting it because the code is switching to a new document
which
does not contain the requested bookmark.

The code among other things switches between two documents and for each loop
pastes a string into each document, so the code switches between documents.

If anyone knows a easier way to do this lemme know.

Formatting the string for each loop is also a little tricky.

The documents could be saved first if it makes things easier, but it would
be better
if they were then saved into a new directory named after textbox2.

The headers from the table in the data document (offences.doc) looks like
| OFFENCE | ACT | PENALTY | CHARGE TEXT | JURISDICTION |

I am pretty new to all this programming so, please, be gentle!!

-Al


Private Sub UserForm_Initialize()
Dim Sourcedoc As Document, i As Integer, j As Integer, myitem As Range, m As
Long, n As Long
Application.ScreenUpdating = False
Set Sourcedoc = Documents.Open(FileName:="C:\Documents and
Settings\Alistair\Application Data\Microsoft\Templates\Offences.doc")
' GET NUMBER OF OFFENCES = NUMBER OF ROWS - 1
i = Sourcedoc.Tables(1).Rows.Count - 1
' NUMBER OF COLUMNS
j = 3
' SET NUMBER OF COLUMNS IN LISTBOX TO MATCH
' NUMBER OF COLUMNS IN TABLE OF OFFENCES
ListBox1.ColumnCount = j

' DEFINE ARRAY TO BE LOADED WITH OFFENCES DATA
Dim MyArray() As Variant
'LOAD OFFENCE DATA INTO MY ARRAY
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = Sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' LOAD DATA INTO LISTBOX1
ListBox1.List() = MyArray
' CLOSE FILE CONTAINING LIST OF OFFENCES
Sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
Dim i As Long, j As Long, CrimeandPunishment As String, SpecimenCharge As
String, Sourcedoc As Document _
, k As Long



' WINDOWS COUNT
Documents.Add Template:="H:\Office\Templates\normal.dot", NewTemplate:=False
k = Windows.Count
Windows(k - 1).Activate

' ITEMS ARE INDEXED FROM ZERO
' LOOPS THROUGH ALL ITEMS
For i = 0 To ListBox1.ListCount - 1
'...AND CHECK WETHER EACH SELECTED
If ListBox1.Selected(i) Then
' IF SELECTED DISPLAY ITEM IN MSGBOX
CrimeandPunishment = ""
SpecimenCharge = ""
For j = 0 To ListBox1.ColumnCount - 2
ListBox1.BoundColumn = j

If j = 1 Then
SpecimenCharge = "JUSTICE CODE=" & CrimeandPunishment
CrimeandPunishment = CrimeandPunishment & vbCr & vbCr
SpecimenCharge = SpecimenCharge & vbCr & "ACT & SECTION=" &
ListBox1.List(i, j)
SpecimenCharge = SpecimenCharge & vbCr & "PENALTY=" &
ListBox1.List(i, j + 1)

End If

CrimeandPunishment = CrimeandPunishment & ListBox1.List(i, j)

Set Sourcedoc = Documents.Open(FileName:="C:\Documents and
Settings\Alistair\Application Data\Microsoft\Templates\Offences.doc")

SpecimenCharge = SpecimenCharge & vbCr & "CHARGE TEXT=" &
Sourcedoc.Tables(1).Cell(i + 2, j + 3).Range
Sourcedoc.Close SaveChanges:=wdDoNotSaveChanges

Next j

CrimeandPunishment = CrimeandPunishment & vbCr & "Penalty:" &
ListBox1.List(i, ListBox1.ColumnCount - 1) & vbCr & vbCr
End If

If ListBox1.Selected(i) Then
Windows(k - 1).Activate

------->>> ActiveDocument.Bookmarks("Offences").Range.InsertBefore
(CrimeandPunishment)


MsgBox (SpecimenCharge)
Windows(k).Activate
ActiveDocument.Content.InsertAfter (SpecimenCharge)



End If

Next i


' APPLY FORMATTING TO CHARGES

Windows(k - 1).Activate
For i = 1 To ActiveDocument.Bookmarks("Offences").Range.Paragraphs.Count
ActiveDocument.Bookmarks("Offences").Range.Paragraphs(i).Range.Font.Bold
= True

ActiveDocument.Bookmarks("Offences").Range.Paragraphs(i).Range.Font.AllCaps
= True
i = i + 4
Next i




UserForm1.Hide

Windows(k - 1).Activate

With ActiveDocument
.Bookmarks("name").Range _
.InsertBefore TextBox1
.Bookmarks("street").Range _
.InsertBefore TextBox2
.Bookmarks("town").Range _
.InsertBefore TextBox3
.Bookmarks("DOB").Range _
.InsertBefore TextBox4
.Bookmarks("age").Range _
.InsertBefore TextBox5
.Bookmarks("occ").Range _
.InsertBefore TextBox6
.Bookmarks("exhibits").Range _
.InsertBefore TextBox9
.Bookmarks("witnesses").Range _
.InsertBefore TextBox10
End With



End Sub

 

[Mark Tangard]

Al,

Without wading through all the code -- it helps to distill your
pasted code down to just the lines you know are relevant to the
problem -- I can safely tell you that yes, that error will very
definitely come up if you ask for a member of a collection (in
this case the collection ActiveDocument.Bookmarks) that isn't
there because you've switched documents.

An easy way around that when you're writing code that switches
from document to document is to assign each document to an
object variable and use it whenever you need to address one
of the collections specific to it. For example:

Set doc1 = ActiveDocument
Set doc2 = Documents.Add
doc1.Activate
:
: <--- work in the first doc
:
doc2.Activate
:
: <--- work in the second doc
:

However, note that you don't always have to *be* in a document
(= have it activated) to manipulate its content. For example,
this will take the text at the bookmark 'abc' in the second doc
above and insert it at the start of the first one, without you
ever needing to (re)activate the latter:

doc2.Range.InsertBefore doc1.Bookmarks("abc").Range.Text

Object variables are a bit of an obstacle for beginners, and
it's easy to fall into the trap of using 'ActiveDocument' to
mean, essentially, the document you happen to be *thinking* about at the moment.

 

[Al & Cay Grant]

Thanks for that Mark.

I am now using the set doc statements and my code is much more reliable
))



- Al

 

[Bob S]

Hi Folks,

I added the following code to my project to calculate a persons age and
automatically update a text box on the user form.

Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)

' CALCULATES AGE

If Not IsDate(TextBox4.Value) Then
Cancel = True
Exit Sub
End If
TextBox5.Value = Int(DateDiff("d", TextBox4, Now) / 365.25)

End Sub

Note that this code does not produce the conventional answer in a
certain case.

Bob S