Ever wanted to add bullets in Microsoft Access 2003? I have usually inserted a dash, but playing around recently found I could create a button to insert a bullet. Here is the solution.
Assume you have a textbox called txtInfo. You need to track where the cursor is in the textbox. To do this create a place to record the position.
Dim intCursorPosition As Integer
In the On Click and On Keydown events for the textbox do the following.
Private Sub txtInfo_Click()
intCursorPosition = Me.txtInfo.SelStart
End Sub
Private Sub txtInfo_KeyDown(KeyCode As Integer, Shift As Integer)
intCursorPosition = Me.txtInfo.SelStart
End Sub
Put a button on the form beside the textbox. Call it btnBullet Post the following code into the On Click event
Private Sub btnBullet_Click()
Me.txtInfo.SetFocus
Me.txtInfo = Left(Me.txtInfo, intCursorPosition) & vbCrLf & Chr(149) & “ ” & Mid(Me.txtInfo, intCursorPosition + 1, Me.txtInfo.SelLength – intCursorPosition)
Me.txtInfo.SetFocus
Me.txtInfo.SelStart = intCursorPosition + 5
End Sub
How it works
The On Click and Keydown events will have told you where the cursor was located prior to the button being selected. That information is stored in intCursorPosition. The first thing that needs to happen is to return the focus to the textbox.
You now construct the contents of the textbox by taking the text to the left of the cursor position, adding a return, adding Chr(149) which is a bullet, adding two spaces, and finally adding the text to the right of the cursor position. The rest of the code is to put the cursor after the bullet and two blank spaces.
Tags: Microsoft Access bullets, Microsoft Access Developer, Microsoft Access Development, Microsoft Access VBA
There is always another way. After using the function for a while, I was getting an occasional error. Mainly after pasting in text. Have now come up with another solution. I am using Ctrl D to insert a bullet. Why Ctrl D? D for “dot”. Did not want to confuse it with Ctrl B which is often used for bold. You can make it any combination you want. Here is the code.
In the Form Keydown paste this code.
‘—————————————————————————————
‘ Procedure : Form_KeyDown
‘ Author : Neville Turbit
‘ Date : 4/06/2010
‘ Purpose : Form_KeyDown only runs if the form’s .KeyPreview property is True.
‘—————————————————————————————
‘
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strText As String
On Error GoTo Error_Form_KeyDown
strText = vbCrLf & Chr(149) & ” ”
Select Case True
Case ((Shift = acCtrlMask) And (KeyCode = vbKeyD))
funInsert (strText)
End Select
Exit_Form_KeyDown:
On Error GoTo 0
Exit Sub
Error_Form_KeyDown:
MsgBox “An unexpected situation arose in your program.” & funCrLf & _
“Please write down the following details:” & funCrLf & funCrLf & _
“Module Name: Form_Form1″ & funCrLf & _
“Type: VBA Document” & funCrLf & _
“Calling Procedure: Form_KeyDown” & funCrLf & _
“Error Number: ” & Err.Number & funCrLf & _
“Error Descritption: ” & Err.Description
Resume Exit_Form_KeyDown
Resume
End Sub
Now create a sub called funInsert. I have just created it in the form but you could make it a public sub and put it in a module if you wish.
‘—————————————————————————————
‘ Procedure : funInsert
‘ Author : Neville Turbit
‘ Date : 4/06/2010
‘ Purpose : Insert the characters at the cursor in the active control. Return true if characters
‘ were inserted. strToInsert is the string or character(s) you want inserted at the cursor
‘ To test this you must be in the control. It must have the focus
‘—————————————————————————————
‘
Public Function funInsert(strToInsert As String) As Boolean
Dim lngTextLength As Long ‘Number of characters in the text
Dim strBefore As String ‘Text before the cursor
Dim strAfter As String ‘Text after the cursor
Dim intCursorPosition As Integer ‘Where cursor is.
On Error GoTo Error_funInsert
If strToInsert <> vbNullString Then ‘ Something to insert
With Screen.ActiveControl
If .Enabled And Not .Locked Then ‘ Permitted to insert
lngTextLength = Len(.Text)
If lngTextLength < = 32767& - Len(strToInsert) Then 'SelStart can't cope with more than
' 32k characters.
intCursorPosition = .SelStart ' Where the cursor is now
If intCursorPosition > 1 Then
strBefore = Left$(.Text, intCursorPosition)
End If
If intCursorPosition + .SelLength < lngTextLength Then ‘ Characters after
strAfter = Mid$(.Text, intCursorPosition + .SelLength + 1)
End If
.Value = strBefore & strToInsert & strAfter ‘ Before + insert + after
.SelStart = intCursorPosition + Len(strToInsert) ‘ Position the cursor
funInsert = True ‘ Successful. Return true
End If
End If
End With
End If
Exit_funInsert:
On Error GoTo 0
Exit Function
Error_funInsert:
MsgBox “An unexpected situation arose in your program.” & funCrLf & _
“Please write down the following details:” & funCrLf & funCrLf & _
“Module Name: Form_Form1″ & funCrLf & _
“Type: VBA Document” & funCrLf & _
“Calling Procedure: funInsert” & funCrLf & _
“Error Number: ” & Err.Number & funCrLf & _
“Error Descritption: ” & Err.Description
Resume Exit_funInsert
Resume
End Function
Remember you need to set the form Key Preview Event to Yes.