Combo box

[pkeegs]

I have set up a word template to include a combobox as per the instructions
on the article at http://support.microsoft.com/?kbid=306258. Below is the
code I have entered per the article with my own database ("FriendsPlus") and
source field ("CarePlan") entered. The macro does not seem to recognise the
first two Dim statements ("User defined types not defined"). What needs to be
done to make the macro function.

I would also like to activate the Macro wherever I place the cursor rather
than in a Field. What changes would I need in the macro to achieve that?

Option Explicit

Private Sub UserForm_Initialize()

Dim dbDatabase As Database
Dim rsFriendsPlus As Recordset
Dim i As Integer
Dim aResults()

' This code activates the Database connection.Change
' the path to reflect your database.
Set dbDatabase = OpenDatabase("E:\Friends Plus\FriendsPlus.mdb")

' This code opens the Customers table. Change the Table
' to reflect the desired table.
Set rsFriendsPlus = dbDatabase.OpenRecordset("CarePlan", dbOpenSnapshot)

i = 0

With rsFriendsPlus
' This code populates the combo box with the values
' in the CompanyName Field.

Do Until .EOF
ComboBox1.Colum(0, i) = .Fields("CarePlan")
.MoveNext
i = i + 1
Loop

End With

End Sub

Private Sub ComboBox1_Change()

ActiveDocument.FormFields("Text1").Result = ComboBox1.Value

End Sub

Private Sub Cmdclose_Click()

End

End Sub


Regards

 

[pkeegs]

Hi again - I meant to say that in placing the cursor in the document I would
like to activate the Form with a keystroke (ctrl+c)so that a user can select
from the list.

Regards again.

 

[pkeegs]

I have solved part of my problem. I had clicked on the Reference "Microsoft
DAO 3.6 Object Library" but had failed to tick it. Ensuring the tick was
there solved the problem of the Dim statements. I would however like to be
able to activate the ComboBox with a keystroke at the cursor point in the
Word text if anyone can help.

 

[Jay Freedman]

In the article you worked from, the macro gocombobox() that displays
the userform is set as the entry macro for a form field. But you can
launch that macro (or any macro) in several different ways. One of
them is to assign a keyboard shortcut.

If you have Word 2003 or earlier, the process is at
http://www.word.mvps.org/FAQs/Customization/AsgnCmdOrMacroToHotkey.htm.
If it's Word 2007, go to Office button > Word Options > Customize and
click the Keyboard Customize button and then follow the same procedure
from step 2.

The code you have will put the answer into the Text1 form field
regardless of where the cursor is when you launch the macro. If that's
not what you want, please explain further.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[pkeegs]

Thanks for the reply Jay - What I want to do is create a small list in a form
from a larger list (the combobox source). I would like to place the cursor at
the start of a line, use a keyboard shortcut to launch the macro and select
from the combobox form and then move the cursor to the next line and repeat.
I realised after I had started working with the form field option that I
would have to have a separate field & macro for each selection. I am sure
that I can do it the other way. The source list in the database has more than
30 items and will be added to/modified over time. I am comfortable with
assigning a hotkey to the macro and am very familiar with forms and VBA in
Excel, but Word seems to have quite a different protocol and language.

Regards

 

[Jay Freedman]

I think I understand now. The change in the coding is relatively
minor, but there is one point that needs attention first.

If the form document is a protected form because you have other form
fields that need to be there, things get much more complicated -- you
can't place the cursor anywhere in such a form except in the form
fields, unless you separate the document into sections (by inserting
section breaks) and leave one or more sections unprotected. If you
don't need form fields for any other purpose, then leave the whole
thing unprotected.

The modifications in the code are:

- Remove the ComboBox1_Change() procedure entirely.

- Change the CmdClose_Click() procedure to

Private Sub CmdClose_Click()
Selection.Text = ComboBox1.Value & vbCr
Unload Me
End Sub

If the cursor is a collapsed point in the (unprotected) document, not
in a form field, then the item from the combo box will be inserted
there, followed by a paragraph mark. If some text is selected, that
will be replaced by the combo box item and paragraph mark.

This sort of thing can be tweaked in a number of ways, so if there's
something else you need, just ask.

 

[pkeegs]

That's brilliant Jay. Having got this far I would like to improve on it. So I
have a close form button (CmdClose) button with the original code, as well I
included a button to post the selection from the dropdown (CmdPost) without
the Unload Me line. However I would like the code to enter the selection in
the document and then move the cursor down a line and enable me to post
another selection so that I can enter a number of items without rerunning the
Macro. I have tried running Record Macro to obtain the code to do this but
nothing is working. Any solutions?

 

[Jay Freedman]

Use this:

Private Sub cmdPost_Click()
Selection.Text = ComboBox1.Value & vbCr
Selection.Collapse wdCollapseEnd
End Sub

The Selection.Text line puts a paragraph mark after the text from the
combo box, but at that point the Selection is still the entire thing
that was just inserted. The new statement, Selection.Collapse, sets
the cursor at the single point after the insertion, ready to accept a
new one.

 

[pkeegs]

Hi again Jay - I've finally solved my own problem with the addition of the
following code into my CmdPost macro "Selection.MoveRight Unit:=wdCharacter,
Count:=1"
Thanks for all the support.