*NOTE: This code has been updated to work much better since original publication*
When developing simple plain text "Email Merge" capabilities in a few databases, I recognized the need for the user to be able to choose available merge fields from a combo box to place into the message text. This is actually very easy to achieve.
This is a simple example, though I'm sure you will be able to adapt it easily to accommodate multiple "insert" options, etc. The main thing to keep in mind is that after any action that will change either the content of the textbox or the cursor position, you should call the "CheckCursorPos" function.
To see this in action...
1. Create a form (frmInsert).
2. Place the following controls on the form: -----TextBox (txtMessage)- draw it large, to accomodate multiple lines, set the "Enter Key Behavior" property ("Other" tab) to "New Line in Field" -----Combo Box (cmbInsert) - set the "Rowsource" property to a list of the values you want the user to have the option of inserting. This could be anything from your database or a custom list. For the current example just put in the following:
3. Open up the form's module and paste in this code:
Option Compare Database Option Explicit
Dim lngCursorPos As Long Dim strText1 As String Dim strInsert As String Dim strText2 As String
Function CheckCursorPos() lngCursorPos = Me.txtMessage.SelStart 'Debug.Print "lngCursorPos: " & lngCursorPos End Function
Private Sub cmbInsert_AfterUpdate() Dim lngNewCursorPos As Long
Private Sub txtMessage_KeyUp(KeyCode As Integer, Shift As Integer) Call CheckCursorPos End Sub
4. Try it out: ---1. Open the form in form view. ---2. Type some text into the "txtMessage" box. ---3. Place the cursor (ie click) anywhere in the middle of the text you typed. ---4. Chose an item from the combo box The text from the combo box should be inserted into the text box at the cursor location.