How to create a dialog box to-select branch then return branch address in footer

[JT]

I am trying to create 50 generic templates for 23 branches.

How can I create a dialog box in a word template when user
selects a branch name then it returns the actual branch
street address in the footer in the following format:

Branch A
Address line A
Suburb A
State A Postcode

Any help is appreciated.

 

[Peter Hewett]

Hi JT

Probably the easiest way to do this is to load your Forms ComboBox by reading
a simple text file, something like a CSV (Comma Separated Value). You can the
load the fields of each line into the different columns of your ComboBox.

The data in your file would look like:

Branch A,Address line A,Suburb A,State A Postcode


When you click the Forms OK button it uses the selected ComboBox entry to
fill out bookmarks in the footer.

Post back if you need more info. Also check out the following link:
http://word.mvps.org/FAQs/Userforms/index.htm
http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

Please post again if you require further assistance.

HTH + Cheers - Peter

 

[Word Heretic]

G'day "JT" <[email protected]>,

Use AutoText entries my friend.

Steve Hudson - Word Heretic Sydney Australia
Tricky stuff with Word or words

Email: steve at wordheretic.com


JT reckoned:

 

[JT]

Hi Peter,

Thanks for your help. Unfortunately I am a novice in form
designing and know next to nothing in VBA. I need more
help if you don't mind.
1. do you mean to create a seperate file in Excel and save
it in CSV format that contains the whole list of address?
2.How do I load the fields into the combobox?
3. how do I write the code to fill out bookmarks from the
combobox selected fields?

Thanks,
JT

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

This routine loads a listbox with client details stored in a table in a
separate
document (which makes it easy to maintain with additions, deletions etc.),
that document being saved as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
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:="e:\worddocs\Clients.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
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will deal with
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.

You can use the same technique to populate a combobox if you would rather
use that than a listbox.
--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Peter Hewett]

Hi JT

Dougs posted a neat version that uses a table in a Word document. But as an
option here's a version that uses a comma delimited text file:

Private Sub btnOK_Click()
Dim ilngCol As Long
Dim lngColumns As Long
Dim strAddress As String

With cboAddress

' The user must have selected something in the ComboBox
If .ListIndex = -1 Then Exit Sub

' Build address string
lngColumns = CLng(.Tag) - 1
For ilngCol = 0 To lngColumns
strAddress = strAddress & .List(.ListIndex, ilngCol)
If ilngCol < lngColumns Then strAddress = strAddress & vbCr
Next
End With

' Do something with strAddress
MsgBox strAddress
End Sub

Private Sub UserForm_Initialize()
LoadAddressFile
End Sub

Public Sub LoadAddressFile()
Const cFileToProcessList As String = "F:\My Templates\Addresses.txt"

Dim hFile As Long
Dim strInput As String

' Allocate next available file number
hFile = FreeFile

' Read the file conataining the Path/File names of the
' Word documents you need to process
Open cFileToProcessList For Input Access Read As hFile
Do Until EOF(hFile)

' Read the path/name of the next Word document to process
Line Input #hFile, strInput

' You've read in the path and file name of
AddToListBox strInput
Loop
Close #hFile
End Sub

Private Sub AddToListBox(ByVal strInfo As String)
Const cDelimiter As String = ","

Dim lngCol As Long
Dim strAddressBit As String

' Parse out the string, each token is separated by a comma
With cboAddress
Do Until LenB(strInfo) = 0
strAddressBit = Snip(strInfo, cDelimiter)
If lngCol = 0 Then
.AddItem strAddressBit
Else
.List(.ListCount - 1, lngCol) = strAddressBit
End If
lngCol = lngCol + 1
Loop

' Set the number of columns so that we can build the
' correct output string when the OK Button is selected
.Tag = lngCol
End With
End Sub

Private Function Snip(ByRef strIn As String, _
ByVal strDelimiter) As String
Dim lngPos As Long

lngPos = InStr(strIn, strDelimiter)
If lngPos = 0 Then
Snip = strIn
strIn = vbNullString
Else
Snip = Left$(strIn, lngPos - 1)
strIn = Mid$(strIn, lngPos + 1)
End If
End Function


Like the Word table it's easy to maintain, but it's also very small. The
above example uses a ComboBox control called "cboAddress". The text file it
uses is called "F:\My Templates\Addresses.txt", just change this to
whatever you want.

The data looks something like this:

Branch A,Address line A,Suburb A,State A Postcode
Branch B,Address line B,Suburb B,State B Postcode
Branch C,Address line C,Suburb C,State C Postcode
Branch D,Address line D,Suburb D,State D Postcode
Branch E,Address line E,Suburb E,State E Postcode
Branch F,Address line F,Suburb F,State F Postcode


HTH + Cheers - Peter