Populate text boxes based on user's selection in another List box

S

Sandi V

I have a template that populates listboxes based on values in the VBA code,
and when user selects a value, it gets passed to bookmarks in the resulting
document. There are also text boxes where users currently type in a
WriterName, WriterTitle, phone, etc.

I want to change the WriterName text box to a listbox. I want it to be
populated by the results of an Access query. Then, after the user selects a
WriterName, I want WriterTitle, Phone, and Email text boxes to populate
accordingly. (would it also be possible for the form to "remember" what
Writername the user chose last time?)

I'm not sure what this process might be called, so I'm unsure how to search
for it. Below is the code as it currently stands. Any help is appreciated.
Thanks!
Sandi


' This is the Initialize event procedure for LetterMSB1Form1
'
Public Sub LetterMSB1Form1_Initialize()
'Main form initialization

'Initialize Listboxes
Delivery_Initialize
DirectDial_Initialize
ToName_Initialize
ToAddress_Initialize
WriterName_Initialize
ReText_Initialize
ToSalute_Initialize
Salutation_Initialize
Closing_Initialize
Enclosure_Initialize
CCBox_Initialize


End Sub
Private Sub Delivery_Initialize()
'Initialize Delivery Listbox

'Initialize Flags
DeliveryCheck.Value = False
Delivery.Enabled = False
DeliveryEnabled = False

'Clear List
Delivery.Clear

Delivery.Style = fmStyleDropDownList
Delivery.MatchEntry = fmMatchEntryFirstLetter
Delivery.AddItem ("BY ELECTRONIC FILING")
Delivery.AddItem ("BY FEDERAL EXPRESS")
Delivery.AddItem ("BY FACSIMILE")
Delivery.AddItem ("BY FACSIMILE AND MAIL")
Delivery.AddItem ("BY HAND DELIVERY")
Delivery.AddItem ("BY MESSENGER")
Delivery.AddItem ("VIA ELECTRONIC FILING")
Delivery.AddItem ("VIA FEDERAL EXPRESS")
Delivery.AddItem ("VIA FACSIMILE")
Delivery.AddItem ("VIA FACSIMILE AND MAIL")
Delivery.AddItem ("VIA HAND DELIVERY")
Delivery.AddItem ("VIA MESSENGER")
Delivery.ListIndex = 0

'Disable and change background style for Delivery Combobox
DeliveryEnabled = False
Delivery.Enabled = False
Delivery.BackStyle = fmBackStyleTransparent

End Sub

Private Sub DirectDial_Initialize()
'Initialize DirectDial Listbox

'Initialize Flags
DirectDialCheck.Value = False
DirectDial.Enabled = False
DirectDialEnabled = False

'Clear Field
DirectDial.Value = ""
DirectDialValue = ""

'Disable and change background style for DirectDial Editbox
DirectDialEnabled = False
DirectDial.Enabled = False
DirectDial.BackStyle = fmBackStyleTransparent

End Sub

Private Sub ToName_Initialize()
'Initialize Name

ToFirstMiddleName.Value = ""
ToFirstMiddleNameValue = ""
ToLastName.Value = ""
ToLastNameValue = ""
ToNameValue = ""

End Sub

Private Sub ToAddress_Initialize()
'Initialize ToAddress

ToAddress.Value = ""
ToAddressValue = ""

End Sub

Private Sub WriterName_Initialize()
'Initialize WriterName

WriterTitle.Value = ""
WriterTitleValue = ""
WriterName.Value = ""
WriterNameValue = ""

End Sub

Private Sub ReText_Initialize()
'Initialize ReText

ReText.Value = ""
ReTextValue = ""

End Sub

Private Sub ToSalute_Initialize()
'Initialize ToSalute

ToSaluteValue = "None"

ToSalute.Clear
ToSalute.Style = fmStyleDropDownList
ToSalute.MatchEntry = fmMatchEntryFirstLetter
ToSalute.AddItem ("None")
ToSalute.AddItem ("Mr.")
ToSalute.AddItem ("Mrs.")
ToSalute.AddItem ("Miss")
ToSalute.AddItem ("Ms.")
ToSalute.AddItem ("Sir")
ToSalute.AddItem ("Madam")
ToSalute.AddItem ("Madam/Sir")
ToSalute.AddItem ("Dr.")
ToSalute.ListIndex = 0

End Sub

Private Sub Salutation_Initialize()
'Initialize Salutation Listbox

SalutationCheck.Value = False
Salutation.Value = "Dear :"

End Sub

Private Sub Salutation_Update()
'ReInitialize Salutation Listbox

ToSaluteValue = ToSalute.Value
If SalutationCheck = True Then
If ToSalute.Value = "None" Then
Salutation.Value = ("Dear " + ToFirstMiddleName.Value + ":")
Else
Salutation.Value = ("Dear " + ToSalute.Value + " " +
ToLastName.Value + ":")
End If
Else
Salutation.Value = "Dear :"
End If

End Sub

Private Sub Closing_Initialize()
'Initialize Closing Listbox

Closing.Clear
Closing.Style = fmStyleDropDownList
Closing.MatchEntry = fmMatchEntryFirstLetter
Closing.AddItem ("Sincerely,")
Closing.AddItem ("Sincerely yours,")
Closing.AddItem ("Best wishes,")
Closing.AddItem ("Regards,")
Closing.AddItem ("Respectfully,")
Closing.AddItem ("Respectfully yours,")
Closing.AddItem ("Very truly yours,")
Closing.ListIndex = 0

End Sub

Private Sub Enclosure_Initialize()
'Initialize Enclosure Listbox

EnclosureValue = "None"

Enclosure.Clear
Enclosure.Style = fmStyleDropDownList
Enclosure.MatchEntry = fmMatchEntryFirstLetter
Enclosure.AddItem ("None")
Enclosure.AddItem ("Enclosure")
Enclosure.AddItem ("Enclosures")
Enclosure.AddItem ("Attachment")
Enclosure.AddItem ("Attachments")
Enclosure.ListIndex = 0

End Sub

Private Sub CCBox_Initialize()
'Initialize CCBox Editbox

CCBox.Value = ""
CCBoxValue = ""
CCBoxLineCount = 0

End Sub

Private Sub WriterInitials_Initialize()
'Initialize Writer Initials

WriterInitials.Value = ""
WriterInitials = ""
WriterInitials = 0

End Sub
Private Sub TypistInitials_Initialize()
'Initialize Typist Initials

TypistInitials.Value = ""
TypistInitials = ""
TypistInitials = 0

End Sub



Private Sub DeliveryCheck_Click()
'Enable or Disable the Delivery Method Line

Delivery.Enabled = DeliveryCheck.Value
DeliveryEnabled = DeliveryCheck.Value

If DeliveryCheck.Value = True Then
'Change Delivery back to opaque background
Delivery.BackStyle = fmBackStyleOpaque
Else
'Change Delivery to a transparent background
Delivery.BackStyle = fmBackStyleTransparent
End If

End Sub

Private Sub DirectDialCheck_Click()
'Enable or Disable the DirectDial

'Initialize flags
DirectDial.Enabled = DirectDialCheck.Value
DirectDialEnabled = DirectDialCheck.Value

If DirectDialCheck.Value = True Then
'Change DirectDial back to opaque background
DirectDial.BackStyle = fmBackStyleOpaque
Else
'Change DirectDial to a transparent background
DirectDial.BackStyle = fmBackStyleTransparent
End If

End Sub

Private Sub Enclosure_Change()
'Capture changes made to the Enclosure value

EnclosureValue = Enclosure.Value

End Sub

Private Sub OK_Click()
'OK Button Clicked, Accept Entries

'Hide the form and continue with macro...
LetterMSB1Form1.Hide

'Get Info
DirectDialValue = DirectDial.Value
ToAddressValue = ToAddress.Value
WriterNameValue = WriterName.Value
WriterTitleValue = WriterTitle.Value
SalutationValue = Salutation.Value
TheDate = Format(Date, "mmmm d, yyyy")
WriterInitialsValue = WriterInitials.Value
TypistInitialsValue = TypistInitials.Value


'Identify Delivery Selection
'Ensure ListBox contains list items
If Delivery.ListCount >= 1 Then
'If no selection, choose last list item.
If Delivery.ListIndex = -1 Then
Delivery.ListIndex = Delivery.ListCount - 1
End If
DeliveryValue = Delivery.List(Delivery.ListIndex)
End If

'Identify Closing Selection
If Closing.ListCount >= 1 Then
'If no selection, choose last list item.
If Closing.ListIndex = -1 Then
Closing.ListIndex = Closing.ListCount - 1
End If
ClosingValue = Closing.List(Closing.ListIndex)
End If

'Identify Enclosure Selection
If Enclosure.ListCount >= 1 Then
'If no selection, choose last list item.
If Enclosure.ListIndex = -1 Then
Enclosure.ListIndex = Enclosure.ListCount - 1
End If
EnclosureValue = Enclosure.List(Enclosure.ListIndex)
End If

'CCBox Selection
CCBox.SetFocus

'The line count is necessary to determine positioning
CCBoxLineCount = CCBox.LineCount
CCBoxValue = CCBox.Value

'To Full Name Selection
If ToSalute.Value = "None" Then
ToNameValue = ToFirstMiddleName.Value + " " + ToLastName.Value
Else
ToNameValue = ToSalute.Value + " " + ToFirstMiddleName.Value + " " +
ToLastName.Value
End If

If DirectDialCheck = True Then
Selection.GoTo What:=wdGoToBookmark, Name:="DirectDial"
Selection.TypeText DirectDialValue
End If

If DeliveryCheck = True Then
Selection.GoTo What:=wdGoToBookmark, Name:="Delivery"
Selection.TypeText DeliveryValue
End If

Selection.GoTo What:=wdGoToBookmark, Name:="TheDate"
Selection.TypeText TheDate

Selection.GoTo What:=wdGoToBookmark, Name:="ToName"
Selection.TypeText ToNameValue

Selection.GoTo What:=wdGoToBookmark, Name:="ToAddress"
Selection.TypeText ToAddressValue

Selection.GoTo What:=wdGoToBookmark, Name:="Salutation"
Selection.TypeText SalutationValue

Selection.GoTo What:=wdGoToBookmark, Name:="Closing"
Selection.TypeText ClosingValue

Selection.GoTo What:=wdGoToBookmark, Name:="WriterName"
Selection.TypeText WriterNameValue

Selection.GoTo What:=wdGoToBookmark, Name:="WriterTitle"
Selection.TypeText WriterTitleValue

Selection.GoTo What:=wdGoToBookmark, Name:="AuthorInitials"
Selection.TypeText WriterInitialsValue

Selection.GoTo What:=wdGoToBookmark, Name:="TypistInitials"
Selection.TypeText TypistInitialsValue


If LetterMSB1Form1.Enclosure.Value <> "None" Then
Selection.GoTo What:=wdGoToBookmark, Name:="Enclosure"
Selection.TypeText EnclosureValue
End If


Selection.GoTo What:=wdGoToBookmark, Name:="CCBox"
If CCBoxValue <> "" Then

Selection.TypeText Text:="cc:" & vbTab & CCBoxValue

If CCBoxLineCount > 1 Then
Dim Counter
Counter = 0 ' Initialize variable.
While Counter < CCBoxLineCount - 1 ' Test value of Counter.
Counter = Counter + 1 ' Increment Counter.
Selection.HomeKey Unit:=wdLine
Selection.Style = ActiveDocument.Styles("TR cc 2")
Selection.MoveUp Unit:=wdLine, Count:=1
Wend ' End While loop when Counter > CCBoxLineCount
End If

End If

ReTextValue = ReText.Value
Selection.GoTo What:=wdGoToBookmark, Name:="ReText"
Selection.TypeText Text:=ReTextValue


'Insert Second Page Heading

'Create Second Page

Selection.GoTo What:=wdGoToBookmark, Name:="Start"
Selection.InsertBreak Type:=wdPageBreak


If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Or
ActiveWindow.ActivePane.View.Type _
= wdMasterView Then
ActiveWindow.ActivePane.View.Type = wdPageView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

Selection.TypeText Text:=ToNameValue
Selection.TypeParagraph
Selection.TypeText Text:=TheDate

ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

'Remove Second Page

Selection.GoTo What:=wdGoToBookmark, Name:="Start"
Selection.Delete Unit:=wdCharacter, Count:=1



' Position insertion point at start point
Selection.GoTo What:=wdGoToBookmark, Name:="Start"

' Turn table gridlines off
ActiveWindow.View.TableGridlines = Not ActiveWindow.View.TableGridlines

' Unload the UserForm
Unload LetterMSB1Form1



End Sub
Private Sub Cancel_Click()
'Cancel the Macro

CancelState = True
LetterMSB1Form1_Terminate

Unload LetterMSB1Form1

End Sub

Private Sub LetterMSB1Form1_Terminate()
'Notify User of Termination

Dim Count As Integer

For Count = 1 To 100
Beep
Next

End Sub

Private Sub SalutationCheck_Change()
'Update Salutation when the checkbox is changed

SalutationCheckValue = SalutationCheck.Value
Salutation_Update

End Sub

Private Sub ToLastName_Change()
'Update Salutation when ToLastName is changed

Salutation_Update

End Sub

Private Sub ToSalute_Change()
'Update Salutation when Salute Changes

Salutation_Update

End Sub
 
G

Gordon Bentley-Mix

G'day Sandi!

I can't answer your entire question, but I can give you some direction on
part of it. Take a look at Greg Maxey's Populate UserForm ListBox article
(http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm) - especially the
part on using external data sources. It might help you with the process for
auto-populating the writer details based on selecting the writer's name. I'm
not sure how to make it work with an Access data source, but I'm sure it can
be done.

As for getting the template to "remember" the value selected previously,
I've done something similar before (which I've shown to Greg and he's
modified a bit but I have yet to look at what he's done). Email me and I'll
see what I can work out.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.
 
S

Sandi V

Hola, Gordon! Great to 'see' you again. Greg's methods look promising.
I'll try it out and meet up with you tomorrow. Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro won't run on certain machines but on mostly others 2
Insert AutoText entries based on selected options in UsrForm cbobo 4
vba to determine if text attribute (eg. of a table) is "hidden" 4
Activex Control in Word Document 9
Combo box won't populate 8
Hide and show components 6
Pasting an image using MACROS 0
Pasting a picture 1

Top