ListBox problem

[LEU]

My form has a list box that Doug Robbins and Jonathan West helped me with.
The problem I am having is when I first open my document and then open my
form the value in my bookmark called ‘bkProtitle’ in the document does not
load into the ListBox in my form. With the form open if I pick from my list
and save it then every time I reopen my form with the document opened the
item I originally picked is highlighted. If I save and close the document and
then reopen it the bookmark does not load into the form again. Here are my
macros for loading and saving the info in my form.

Private Sub UserForm_Initialize()
Dim myType
Set oFF = ActiveDocument.FormFields
myType = Split(" |CONTINUOUS|INFORMATION|REFERENCE", "|")
With Cmb1
.List = myType
.ListIndex = 0
.MatchRequired = True
End With

Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range, m As
Long, n As Long
' Modify the path in the following line so that it matches where you
Saved Clients.Doc
Application.ScreenUpdating = False
' Open the file containing the client details
'Set sourcedoc = Documents.Open(FileName:="u:\Procedure Master
Templates\Procedure Types.doc")
Set sourcedoc = Documents.Open(FileName:="D:\Procedure Master
Templates\Procedure Types.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim myArray() As Variant
'Load client data into MyArray
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 the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges

On Error Resume Next
Set oBMs = ActiveDocument.Bookmarks
Text1.Value = oBMs("bknbr").Range.Text
Text2.Value = oBMs("bkRevision").Range.Text
Text3.Value = oBMs("bktitle").Range.Text
Cmb1.Value = oBMs("bkUse").Range.Text
Text4.Value = oBMs("bkDIC").Range.Text
Text5.Value = oBMs("bkPCN").Range.Text
Text6.Value = oBMs("bkQPR").Range.Text
Text7.Value = oBMs("bkExt1").Range.Text
Text8.Value = oBMs("bkSponsor").Range.Text
Text9.Value = oBMs("bkExt2").Range.Text
Text10.Value = oBMs("bkDate").Range.Text
ListBox1.Value = oBMs("bkProTitle").Range.Text
End Sub

Private Sub cmdOK_Click()
FillBookmark "bknbr", Text1.Text
FillBookmark "bknbr2", Text1.Text
FillBookmark "bknbr3", Text1.Text
FillBookmark "bknbr4", Text1.Text
FillBookmark "bknbr5", Text1.Text
FillBookmark "bkRev", Text2.Text
FillBookmark "bkRev1", Text2.Text
FillBookmark "bkRevision", Text2.Text
FillBookmark "bktitle", Text3.Text
FillBookmark "bktitle1", Text3.Text
FillBookmark "bktitle2", Text3.Text
FillBookmark "bkuse", Cmb1.Value
FillBookmark "bkuse1", Cmb1.Value
FillBookmark "bkuse2", Cmb1.Value
FillBookmark "bkDic", Text4.Text
FillBookmark "bkPCN", Text5.Text
FillBookmark "bkQPR", Text6.Text
FillBookmark "bkExt1", Text7.Text
FillBookmark "bkSponsor", Text8.Text
FillBookmark "bkExt2", Text9.Text
FillBookmark "bkDate", Text10.Text

Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
If ListBox1.Value <> "" Then
Addressee = Addressee & ListBox1.Value & vbCr
End If
Next i
FillBookmark "bkProTitle", Addressee

Me.Hide

End Sub

LEU

 

[Doug Robbins - Word MVP]

I would suggest that instead of using Bookmarks to display the information,
you write the information to Document Variables and then use { DOCVARIABLE }
fields in the document to display the information there. Then if you want
to reload the data into the form, you obtain the data from the Document
Variable.

The following code from a CommandButton Click Event and a UserForm
Initialize event does the above (in addition to other things)

Private Sub cmdContinue_Click()
Dim logrange As Range, logtable As Table
Dim LogNum As Long, rownum As Long
Dim NewDoc As Document
On Error GoTo CATCH

Set NewDoc = ActiveDocument
With NewDoc
cmbCompany.BoundColumn = 1
.Variables("varCompanyCode") = cmbCompany.Value
If txtAddressee = "" Then
.Variables("varAddressee").Value = " "
Else
.Variables("varaddressee").Value = Trim(txtAddressee)
End If
If cmbCompany.Value <> "OT" Then
cmbCompany.BoundColumn = 2
.Variables("varcompany").Value = Trim(cmbCompany.Value)
Else
If txtCompany = "" Then
.Variables("varCompany").Value = " "
Else
.Variables("varCompany").Value = Trim(txtCompany)
End If
End If
If txtstreet = "" Then
.Variables("varaddress").Value = " "
Else
.Variables("varaddress").Value = Trim(txtstreet)
End If
If txtCity = "" Then
.Variables("varcity").Value = " "
Else
.Variables("varcity").Value = Trim(txtCity)
End If
If TxtState = "" Then
.Variables("varstate").Value = " "
Else
.Variables("varstate").Value = Trim(TxtState)
End If
If txtZip = "" Then
.Variables("varzip").Value = " "
Else
.Variables("varzip").Value = Trim(txtZip)
End If
If txtCountry = "" Then
.Variables("varcountry").Value = " "
Else
.Variables("varcountry").Value = Trim(txtCountry)
End If
If txtSubject = "" Then
.Variables("varSubject").Value = " "
Else
.Variables("varsubject").Value = Trim(txtSubject)
End If
If txtSignatory = "" Then
.Variables("varsignatory").Value = " "
Else
.Variables("varsignatory").Value = Trim(txtSignatory)
End If
If txtSalutation = "" Then
.Variables("varSalutation").Value = " "
Else
.Variables("varSalutation").Value = Trim(txtSalutation)
End If
End With

If Val(NewDoc.Variables("varLogNum").Value) = 0 Then
With Word.Application
' .Visible = False
Dim Log As Word.Document
Set Log = .Documents.Open("c:\Documents and Settings\Doug
Robbins\logout.doc")
End With

With Log
Dim i As Long
cmbCompany.BoundColumn = 8
i = cmbCompany.Value
Set logtable = .Tables(i)
rownum = logtable.Rows.Count
Set logrange = logtable.Cell(rownum, 1).Range
logrange.End = logrange.End - 1
LogNum = logrange
LogNum = LogNum + 1
logtable.Rows.Add
rownum = rownum + 1
logtable.Cell(rownum, 1).Range = LogNum
NewDoc.Variables("varLogNum").Value = Format(LogNum, "000#")
NewDoc.Fields.Update
cmbCompany.BoundColumn = 1
NewDoc.SaveAs "C:\Documents\L-GAPB-" & cmbCompany.Value & "-" &
Format(LogNum, "000#")
With logtable
.Cell(rownum, 2).Range = Format(Date, "MMM dd, yyyy")
.Cell(rownum, 3).Range = NewDoc.Variables("varaddressee").Value
.Cell(rownum, 4).Range = NewDoc.Variables("varcompany").Value
.Cell(rownum, 5).Range = NewDoc.Variables("varsubject").Value
.Cell(rownum, 6).Range = NewDoc.Variables("varsignatory").Value
.Cell(rownum, 7).Range = NewDoc.FullName
End With
.Save
.Close
End With

Set Log = Nothing
NewDoc.Bookmarks("text").Range.Select
Else
NewDoc.Save
End If

Me.hide

EXITHERE:
On Error Resume Next
Word.Application.Visible = True
Exit Sub

CATCH:
Err.Raise Err.Number, Err.Source, Err.Description
Resume EXITHERE





End Sub

Private Sub UserForm_Initialize()
lblDate.Caption = Format(Date, "MMMM d, yyyy")
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
Saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="C:\Documents and Settings\Doug
Robbins\Companies.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
cmbCompany.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
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
cmbCompany.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
With ActiveDocument
If Val(.Variables("varlognum").Value) > 0 Then
txtCompany.Visible = True
txtAddressee = .Variables("varAddressee").Value
txtSalutation = .Variables("varSalutation").Value
txtCompany = .Variables("varCompany").Value
txtstreet = .Variables("varstreet").Value
txtCity = .Variables("varcity").Value
txtZip = .Variables("varzip").Value
txtCountry = .Variables("varcountry").Value
txtSubject = .Variables("varsubject").Value
txtSignatory = .Variables("varsubject").Value
Else
txtCompany.Visible = False
End If
End With

End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[LEU]

Hi Doug,

Thank you again for all our help.

I came up with a creative way to fix the problem. What I did was create a
new form with just the listbox in it. I only really need to get into it one
time. Once my document type is set it should not change. One thing I'm
curious about is how this new form is displayed. When my old form is called
it appears over the top of my document and you can see the document behind
it. With the new form it looks like it is opening a new screen. Is this
normal because of getting the list information from a separate document or
did I set something when creating the new form?

LEU