Inserting text at a bookmark with a Userform

[Karen Sigel]

I’m creating a letterhead template that inserts one of 15 addresses at
a bookmark in the document’s footer. I’ve used Greg Maxey’s tutorials
to create a functioning userform and populate a combobox with the
addresses (they’re in an Excel file). I think I’m very close, yet
can’t figure out how to do these few things:

1. Make the address find the bookmark and populate it (I thought I
knew how to do this, but not so much, and I can’t make heads or tails
of the tutorials I’ve found.)

2. Right now, my combo box lists the actual addresses that are in the
spreadsheet. How do I make it show the city name instead? As
background, my spreadsheet is set up with two columns, one with the
address, the other with the name of the city it's in. Each cell that
holds an address is defined as the name of the city. The group of
populated rows in that column is defined as “OfficeAddresses.”

3. How can I add or edit an office address in the Excel file and have
that “magically” appear in the userform?

4. I’m told I can set up the templates so that each office will have
its own address show up as the default selection (even the first would
be great). How can I do that?

Here’s my code so far:

Private Sub UserForm_Initialize()

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

' Open the database
Set db = OpenDatabase("J:\RC\41\letterhead\office addresses.xls",
False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `OfficeAddresses`")

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

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

' Load the ComboBox with the retrieved records
cboOfficeAddress.Column = rs.GetRows(NoOfRecords)

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub

Private Sub cboOfficeAddress_Change()

With cboOfficeAddress
.AddItem "CityA"
.AddItem "CityB"
.AddItem "CityC"
.AddItem "CityD"
.AddItem "CityE"
.AddItem "CityF"
.AddItem "CityG"
.AddItem "CityH"
.AddItem "CityI"
.AddItem "CityJ"
.AddItem "CityK"
.AddItem "CityL"
.AddItem "CityM"
.AddItem "CityN"
.AddItem "CityO"
End With

End Sub

Private Sub btnCancel_Click()
Unload Me
End Sub

Private Sub btnOK_Click()
Unload Me
End Sub


Thanks for any assistance-
Karen