Returning an Auto Text from a Combo Box

[SuzyQ]

I am setting up a user form for an employment contract. Amongst other
information the user needs to include will be the address block for the
relevant office that the person is being employed through. I have each
address block set up as auto text.

I have set up the code to insert the value at a specific bookmark however I
can't work out how to get it to insert a particular auto text at the bookmark

i.e if the combo value equaled Sydney it would insert the auto text "atxtsyd"

This is my code

Private Sub cmd_ok_Click()
Dim addressRange As Range
With ActiveDocument
If .Bookmarks.Exists("TestAddress") Then
Set addressRange = .Bookmarks("TestAddress").Range
Else
Set addressRange = Selection.Range
End If

addressRange.Text = cbotest.Value

***can you define what value it should equal at this point****


.Bookmarks.Add Name:="TestAddress", Range:=addressRange
End With
Me.Hide
End Sub

Thank you.

Susan

 

[Doug Robbins - Word MVP]

I would suggest that instead of using autotext, you store the items to be
listed in the combobox and the associated address details in a separate
document and then you populate the combobox with that data.

This routine loads a combobox 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 combobox (ComboBox1) 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:="c:\Company.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
ComboBox1.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 ComboBox1
ComboBox1.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 ComboBox1.ColumnCount
ComboBox1.BoundColumn = i
Addressee = Addressee & ComboBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
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, originally posted via msnews.microsoft.com

 

[SuzyQ]

Doug,

sorry I left out a vital piece of information - the autotexts are of a
positioned .jpg file. I have set up the address blocks to replicate the
printed version of our letterhead using the correct font (not available on
all computers). So your suggestion while makes sense doesn't work for me in
this scenario.

Susan

 

[Doug Robbins - Word MVP]

Assuming that your combobox contains two columns, one for the name of the
office location and the other for the name of the autotext entry, the
following should word

Private Sub cmd_ok_Click()
Dim addressRange As Range
With ActiveDocument
If .Bookmarks.Exists("TestAddress") Then
Set addressRange = .Bookmarks("TestAddress").Range
Else
Set addressRange = Selection.Range
End If
cbotest.BoundColumn = 2
addressRange.Select
Selection.Text = cbotest.Value
SendKeys "{F3}"
.Bookmarks.Add Name:="TestAddress", Range:=addressRange
End With
Me.Hide
End Sub

Selection.Text = "AddRow"





--
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, originally posted via msnews.microsoft.com

 

[SuzyQ]

I am so sorry but that is assuming I know how to set up a combobox with two
columns. I only know about setting up one column.

So Problem is:
I don't know how to set up a two column combo box.
I can't seem to find meaninful instructions/tutorials to help me
I don't know how to populate a combo box with auto texts - My computer
crashed the other day and lost the code I had found for getting a combo box
to populate with Auto Texts
I have spent over an hour with no success running searches to find the code
again.

So I am now just a frustrated graphic designer trying to get a handle of VBA
and feeling that it aint ever going to happen. I tried to do this two years
ago and decided that the time spent bashing my head against the computer
screen wasn't worth it. Foolishly I decided I wasn't going to be beaten this
time.

Now I have had my rant if you or anyone could point me in the direction of
some good tutorial/books/online resource to learn how to work with Combo
boxes or for that matter VBA.

 

[Doug Robbins - Word MVP]

Take a look at the code in the Private Sub UserForm_Initialize() routine
that I included in my first message.

Create a new document containing a two column table with the first row
containing the entries "Office Location" and "Autotext". Then in each row
of the table, enter the location of an office and it's associated autotext
name.

Save that document somewhere and modify this line of code in the Private Sub
UserForm_Initialize() routine so that the "c:\Company.doc" is replaced by
the path and filename of the document.

Set sourcedoc = Documents.Open(FileName:="c:\Company.doc")

Then, if you used that routine for a userform that contained a combobox with
the name of ComboBox1, when the userform is displayed, that combobox will be
populated with the list of office locations and their associated autotext
entries

Best to give the combobox a meaningful name such as cmbOffices and modify
the code in the Private Sub UserForm_Initialize() routine so that ComboBox1
is replaced by the name that you use.
--
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, originally posted via msnews.microsoft.com

 

[SuzyQ]

Thank you Doug,

putting your three posts together and talking it over with a colleauge who
has a programming background I have made progress.

If I wanted to have more than one combo box (i.e. one for each state) how
would I modify the code to populate all of them from the same file? Would you
have separate tables for each in the same file?

Susan

 

[SuzyQ]

Doug, I found your's and Greg Maxey's tutorial on Cascading List Boxes. I was
going to try that tomorrow. Do you think that will work with my need to use
Auto texts?

As always thank you for all your help!!!

Susan

 

[Doug Robbins - Word MVP]

I assume that you want to use multiple listboxes/comboboxes because you are
going to use one for the States and then depending upon the State selected
the cascading code would load the other listbox/combobox with the offices in
that State.

I would suggest that you name your comboboxes cboState and cboOffice

That being the case, the following routine should work
Private Sub cmd_ok_Click()
Dim addressRange As Range
With ActiveDocument
If .Bookmarks.Exists("TestAddress") Then
Set addressRange = .Bookmarks("TestAddress").Range
Else
Set addressRange = Selection.Range
End If
cboOffice.BoundColumn = 2
addressRange.Select
Selection.Text = cboOffice.Value
SendKeys "{F3}"
.Bookmarks.Add Name:="TestAddress", Range:=addressRange
End With
Me.Hide
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, originally posted via msnews.microsoft.com