Error Checking Text Boxes with Word VBA

[BobK4UVT]

I am building a pretty substantial (protected) form with Word in Office XP.

Many of the text box form fields will contain numerical values. I need to be able to check for a valid number with IsNumber() and also to check that a value is present. If not, an error message is to be issued to the user.

In the text box properties, I specify an exit macro to do the checking. I need to know the name of the textbox (bookmark name) as well as the value/result contained in it.

I know it sounds crazy, but I have yet to find a consistent way to do this for the selected textbox in VBA. There do not seem to be any objects I can use to get this info. Bookmarks are alphabetized & return an erroneous name -- not the one I'm currently in. Form field objects work great for checkboxes, but not textboxes --- i'd have to hardcode the box name. I'm trying to keep it general, but I'm unable to even determine the formfield index. Seems like this would be so basic as to be trivial, but Word just won't give me this info.

Anyone have any suggestions on how to solve this dilemma??

Many thanks in advance,

Bob

 

[Zathras]

I'm not a Word expert and I don't have XP, so this may or may not be of any use to you. But for what it's worth...

I don't have an exit macro property for my edit box, but I can trap the KeyDown event and restrict the user from entering anyhting but numbers. Right-click on the edit box (in design mode) and select "View Code." Then you can enter the following:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If Not IsValidForNumber(KeyCode, 0, 0) Then KeyCode = 0
End Sub

Repeat for each numeric text box in the document.

Then in a new code module, enter the following code:

Option Explicit
Function IsValidForNumber(ByVal KeyCode As Integer, _
           ALLOW_DECIMAL As Boolean, _
           ALLOW_NEGATIVE As Boolean) As Boolean
  If (KeyCode >= 48 And KeyCode <= 57) _
      Or (KeyCode >= 96 And KeyCode <= 105) _
      Or (ALLOW_DECIMAL And KeyCode = 110) _
      Or (ALLOW_NEGATIVE And KeyCode = 109) _
      Or (ALLOW_NEGATIVE And KeyCode = 189) _
      Or (KeyCode = 8) Or (KeyCode = 13) Then
    IsValidForNumber = True
  End If
End Function

The only way I could see to trap the tab key or the enter key was to use this for each text box (also in the code for ThisDocument):

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = 9 Then ProcessTabKey "TextBox1", Shift
  If KeyCode = 13 Then ProcessEnterKey "TextBox1", Shift
End Sub

With this code in the module:

Function ProcessEnterKey(WhichTextBox As String, Shift As Integer)
  MsgBox "Enter key pressed for " + WhichTextBox
End Function

Function ProcessTabKey(WhichTextBox As String, Shift As Integer)
  If Shift Then
    MsgBox "Shift-Tab key pressed for " + WhichTextBox
  Else
    MsgBox "Tab key pressed for " + WhichTextBox
  End If
End Function

But note that the user can still use the mouse to click in a different text box and bypass the edit processing for the box just modified. It gets really messy, but you could trap the mouse clicks and which object was clicked for every object on the document and save the object name in a global variable. Then when the object clicked is not the same as the one previously clicked, treat it as if the enter key was pressed for the previous object. You would also have to trap "Lost focus" for each edit box to handle the case where the user clicks somewhere in the document and not in another object.

Hope this helps.

 

[fumei]

The current selected form field is Selection.Formfields(1), and the Name is .Name. The value is .Result. The following goes through all form fields in the document. if it is a text box it grabs the Name and Result. You can then do whatever you want with them.

Sub TestFindBoxNameValue()
Dim strBoxName, strBoxValue As String
Dim i As Integer
Dim fCount
Selection.HomeKey Unit:=wdStory
i = ActiveDocument.FormFields.Count
For fCount = 1 To i
Selection.GoTo What:=wdGoToField, Which:=wdGoToNext, Count:=1, Name:=""
' check if form field is a textbox
If Selection.FormFields(1).Type = wdFieldFormTextInput Then
strBoxName = Selection.FormFields(1).Name
strBoxValue = Selection.FormFields(1).Result
'
' do your error trapping here
'
' need to move outside form field or this
' will select the same box
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Else
' do something else if NOT a text box???
End If
Next fCount
End Sub

 

[BobK4UVT]

Zathras --- many thanks for your insights! I'm still tinkering with it. Also still looking for a more generic solution, but no luck yet.

Fumei --- I had tried this to no avail --- and this is the direction I wanted to go in. I did try your code. When I get to the line "If Selection.FormFields(1).Type = wdFieldFormTextInput Then...", I get the error "The requested member of the collection does not exist". Seems like it is looking at the very first form field and not the current selection.

Anyway, I've tried things 20 ways from Sunday & keep bumping into this error. I can use the same code with all the check boxes I have and it works fine. But with a text box it seems to get real fussy. I'll keep trying.....

Many thanks, guys!!!