one column listbox, multiple bookmarks, spread*** link

From: LizPCS (LizPCS.11v0el_at_nospam.WordForums.com)
Date: 02/18/04 

Date: Wed, 18 Feb 2004 15:25:10 -0600

My brain hurts again...I have been searching for a short while on this
and after looking at code for 5 hours a day for 3 days now and being a
beginner - its all starting to turn into mush in my head.
Here is what I want to do:
I have a userform with a list box that loads the listbox from a
spread*** using the MVP method from "Load a ListBox from a Named
Range in Excel using DAO". So that all works but my range is only one
column because that is all I want to show in the listbox.
For people like me who need some kind of reference - this is for a
letter and the list box is the author so I only want it to show the
initials of the author on the form. Then once an author is selected 5
different bookmarks on the document should be filled out. I want to
grab the data for the 5 different bookmarks from the 5 columns of the
spread***-for easy updating. Make sense?
Another question - the author may be someone other than a person on the
spread*** - in this case I would like to add another selection on the
list box in which case it would get the text from a text box on the
form to fill out a few of those bookmarks.
I thought I may be able to just do an if-then statement for the list
box - like if they select something in the list box then get the data
from the spread*** but if they enter something in the other author
text box then fill in the bookmarks with the text from the text box.
Hopefully that all makes sense.
Here is the code for the "Load a ListBox from a Named Range in Excel
using DAO" modified for my project:

Private Sub UserForm_Initialize()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
Set db = OpenDatabase("S:\rjmWORD\authors.xls", False, False,
"Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `initials`")

' Determine the number of retrieved records
With rs
MoveLast
NoOfRecords = .RecordCount
MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub

Any help would be greatly appreciated. And I will continue searching.
Thanks to everyone for all the help I've already found just browsing
here and the MVP site - you guys are awesome!
Liz

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/