Error 91: Object variable or With block variable not set

[EnPasDialektos]

Excel 2003...I created a form with nine textbox controls, wrote subs for keyboard events, even logged activity to an external file. I also created Workbook_Open and Workbook_BeforeClose subs to autoload and autounload the form.

Throughout the process, I was able to open the workbook and have the userform loaded and shown in vbModal status. Suddenly, at some point just after I had a userform that was ready for real use, the error message began popping up: Error 91: Object variable or With block variable not set. The error always ponited to the form reference in either the Workbook_Open or Workbook_BeforeClose subs.

What happened? I did not change the workbook level macros at all during this process.

Thanks in advance for your response.

 

[SkipVought]

hi,

It is always highly advisable and necessary to see the code in question, in context.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

And also – when you present the code – try to have it align properly. You may just help yourself see the problem (missing End If or stuff like that)

With abcd
    If xyz Then
        ....
    Else
        1234
    End If
    If Something Then
        kkkk
    End If
End With

Have fun.

---- Andy

 

[EnPasDialektos]

Yup, presenting the code would help. Here it is, with proper indentation.

These are the workbook level events to start up and shut down frmDataEntry.

Private Sub Workbook_Open()

   Load frmDataEntry
   frmDataEntry.Show vbModal

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

   frmDataEntry.Hide
   Unload frmDataEntry

End Sub

And here are the events and subs in the form code section.

' +------------------------------------------------+
' |                 Declarations                   |
' +------------------------------------------------+
Const rowFirstRow As String = "5"
Const colLastName As String = "B"
Const colFirstName As String = "C"
Const colMiddleName As String = "D"
Const colAddressLine1 As String = "E"
Const colAddressLine2 As String = "F"
Const colCity As String = "G"
Const colState As String = "H"
Const colZip As String = "I"
Const colPhoneNumber As String = "J"
Dim previousFieldValue As String
Dim rowCounter As Integer
Dim KeystrokeCounter As Integer
Dim tabIndexNbr As Integer
Dim previousKeyWasTab As Boolean
Dim wksDataEntryList As New Worksheet
Dim fsoLogfileHandle As Object
Dim logFile As Object


' +------------------------------------------------+
' |                    Forms                       |
' +------------------------------------------------+
Private Sub UserForm_Initialize()

   logFile.WriteLine "UserForm_Initialize: Enter"
   Set wksDataEntryList = Worksheets("DataEntryList")
   KeystrokeCounter = 0
   previousFieldValue = ""
   rowCounter = rowFirstRow
   tabIndexNbr = 0
   lblKeystrokeCount.Caption = Trim(CStr(KeystrokeCounter))
   Set fsoLogfileHandle = CreateObject("Scripting.FileSystemObject")
   Set logFile = fsoLogfileHandle.CreateTextFile("C:\Documents and Settings\jeblanch\My Documents\Personal\KeystrokeCounter.log", True)
   logFile.WriteLine "frmDataEntry initialized."
   ClearFields
   logFile.WriteLine "UserForm_Initialize: Exit"

End Sub

Private Sub UserForm_Terminate()

   logFile.WriteLine "UserForm_Terminate: Enter"
   KeystrokeCounter = 0
   lblKeystrokeCount.Caption = Trim(CStr(KeystrokeCounter))
   logFile.WriteLine "Keystroke Counter terminated."
   ClearFields
   logFile.WriteLine "UserForm_Terminate: Exit"
   logFile.Close

End Sub

' +------------------------------------------------+
' |                   Buttons                      |
' +------------------------------------------------+
Private Sub btnResetCount_Click()

   logFile.WriteLine "btnResetCount_Click: Enter"
   KeystrokeCounter = 0
   lblKeystrokeCount.Caption = Trim(CStr(KeystrokeCounter))
   ClearFields
   tabIndexNbr = 0
   
   With wksDataEntryList
      .Range(colLastName & rowFirstRow, colPhoneNumber & Trim(CStr(rowCounter))).Clear
   End With
   rowCounter = CInt(rowFirstRow)
   logFile.WriteLine "btnResetCount_Click: Exit"
   
End Sub

Private Sub btnCloseForm_Click()

   logFile.WriteLine "btnCloseForm_Click: Enter"
   KeystrokeCounter = 0
   lblKeystrokeCount.Caption = Trim(CStr(KeystrokeCounter))
   ClearFields
   frmDataEntry.Hide
   logFile.WriteLine "btnCloseForm_Click: Exit"

End Sub

' +------------------------------------------------+
' |                Phone Number                    |
' +------------------------------------------------+
Private Sub tbxPhoneNumber_Enter()

   logFile.WriteLine "tbxPhoneNumber_Enter: Enter"
   With tbxPhoneNumber
      tabIndexNbr = .TabIndex
      previousFieldValue = .Text
   End With
   logFile.WriteLine "tbxPhoneNumber_Enter: tabIndexNbr = " & CStr(tabIndexNbr) & ", previousFieldValue = " & tbxPhoneNumber.Text
   logFile.WriteLine "tbxPhoneNumber_Enter: Exit"
   
End Sub

Private Sub tbxPhoneNumber_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxPhoneNumber_KeyDown: Enter"
   
   ProcessKeyDown KeyCode, Shift
   
   logFile.WriteLine "tbxPhoneNumber_KeyDown: KeyCode = " & CStr(KeyCode) & ", Shift = " & CStr(Shift)
   logFile.WriteLine "tbxPhoneNumber_KeyDown: Exit"

End Sub

Private Sub tbxPhoneNumber_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   logFile.WriteLine "tbxPhoneNumber_KeyPress: Enter"
   logFile.WriteLine "tbxPhoneNumber_KeyPress: KeyAscii = " & CStr(KeyAscii) & ", tbxPhoneNumber.Value = " & tbxPhoneNumber.Text
   
   tbxPhoneNumber.Value = ProcessKeyPress(KeyAscii, tbxPhoneNumber.Text)
   
   logFile.WriteLine "tbxPhoneNumber_KeyPress: tbxPhoneNumber.Value = " & tbxPhoneNumber.Text
   logFile.WriteLine "tbxPhoneNumber_KeyPress: Exit"

End Sub

Private Sub tbxPhoneNumber_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxPhoneNumber_KeyUp: Enter"
   logFile.WriteLine "tbxPhoneNumber_KeyUp: KeyAscii = " & CStr(KeyCode) & ", tbxPhoneNumber.Value = " & tbxPhoneNumber.Text
   
   tbxPhoneNumber.Value = ProcessKeyUp(KeyCode, tbxPhoneNumber.Text)

   logFile.WriteLine "tbxPhoneNumber_KeyUp: tbxPhoneNumber.Value = " & tbxPhoneNumber.Text
   logFile.WriteLine "tbxPhoneNumber_KeyUp: Exit"
   
End Sub

Private Sub tbxPhoneNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   logFile.WriteLine "tbxPhoneNumber_Exit: Enter"

   With tbxPhoneNumber
      If StrComp(.Text, previousFieldValue, vbTextCompare) Then
         wksDataEntryList.Range(colPhoneNumber & Trim(CStr(rowCounter))).Value = .Text
      End If
   End With
   
   logFile.WriteLine "tbxPhoneNumber_Exit: Exit"

End Sub

' +------------------------------------------------+
' |                   Zip                          |
' +------------------------------------------------+
Private Sub tbxZip_Enter()

   logFile.WriteLine "tbxZip_Enter: Enter"
   
   With tbxZip
      tabIndexNbr = .TabIndex
      previousFieldValue = .Text
   End With
   
   logFile.WriteLine "tbxZip_Enter: tabIndexNbr = " & CStr(tabIndexNbr) & ", previousFieldValue = " & tbxZip.Text
   logFile.WriteLine "tbxZip_Enter: Exit"
   
End Sub

Private Sub tbxZip_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxZip_KeyDown: Enter"
   
   ProcessKeyDown KeyCode, Shift

   logFile.WriteLine "tbxZip_KeyDown: KeyCode = " & CStr(KeyCode) & ", Shift = " & CStr(Shift)
   logFile.WriteLine "tbxZip_KeyDown: Exit"

End Sub

Private Sub tbxZip_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   logFile.WriteLine "tbxZip_KeyPress: Enter"
   logFile.WriteLine "tbxZip_KeyPress: KeyAscii = " & CStr(KeyAscii) & ", tbxZip.Value = " & tbxZip.Text
   
   tbxZip.Value = ProcessKeyPress(KeyAscii, tbxZip.Text)

   logFile.WriteLine "tbxZip_KeyPress: tbxZip.Value = " & tbxZip.Text
   logFile.WriteLine "tbxZip_KeyPress: Exit"

End Sub

Private Sub tbxZip_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxZip_KeyUp: Enter"
   logFile.WriteLine "tbxZip_KeyUp: KeyAscii = " & CStr(KeyCode) & ", tbxZip.Value = " & tbxZip.Text
   
   tbxZip.Value = ProcessKeyUp(KeyCode, tbxZip.Text)

   logFile.WriteLine "tbxZip_KeyUp: tbxZip.Value = " & tbxZip.Text
   logFile.WriteLine "tbxZip_KeyUp: Exit"
   
End Sub

Private Sub tbxZip_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   logFile.WriteLine "tbxZip_Exit: Enter"

   With tbxZip
      If StrComp(.Text, previousFieldValue, vbTextCompare) Then
         wksDataEntryList.Range(colZip & Trim(CStr(rowCounter))).Value = .Text
      End If
      
   End With

   logFile.WriteLine "tbxZip_Exit: Exit"

End Sub

' +------------------------------------------------+
' |                  State                         |
' +------------------------------------------------+
Private Sub tbxState_Enter()

   logFile.WriteLine "tbxState_Enter: Enter"
   
   With tbxState
      tabIndexNbr = .TabIndex
      previousFieldValue = .Text
   End With
   
   logFile.WriteLine "tbxState_Enter: tabIndexNbr = " & CStr(tabIndexNbr) & ", previousFieldValue = " & tbxState.Text
   logFile.WriteLine "tbxState_Enter: Exit"
   
End Sub

Private Sub tbxState_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxState_KeyDown: Enter"
   
   ProcessKeyDown KeyCode, Shift

   logFile.WriteLine "tbxState_KeyDown: KeyCode = " & CStr(KeyCode) & ", Shift = " & CStr(Shift)
   logFile.WriteLine "tbxState_KeyDown: Exit"

End Sub

Private Sub tbxState_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   logFile.WriteLine "tbxState_KeyPress: Enter"
   logFile.WriteLine "tbxState_KeyPress: KeyAscii = " & CStr(KeyAscii) & ", tbxState.Value = " & tbxState.Text
   
   tbxState.Value = ProcessKeyPress(KeyAscii, tbxState.Text)

   logFile.WriteLine "tbxState_KeyPress: tbxState.Value = " & tbxState.Text
   logFile.WriteLine "tbxState_KeyPress: Exit"

End Sub

Private Sub tbxState_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxState_KeyUp: Enter"
   logFile.WriteLine "tbxState_KeyUp: KeyAscii = " & CStr(KeyCode) & ", tbxState.Value = " & tbxState.Text
   
   tbxState.Value = ProcessKeyUp(KeyCode, tbxState.Text)

   logFile.WriteLine "tbxState_KeyUp: tbxZip.Value = " & tbxState.Text
   logFile.WriteLine "tbxState_KeyUp: Exit"
   
End Sub

Private Sub tbxState_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   logFile.WriteLine "tbxState_Exit: Enter"

   With tbxState
      If StrComp(.Text, previousFieldValue, vbTextCompare) Then
         wksDataEntryList.Range(colState & Trim(CStr(rowCounter))).Value = .Text
      End If
      
   End With

   logFile.WriteLine "tbxState_Exit: Exit"

End Sub

' +------------------------------------------------+
' |                   City                         |
' +------------------------------------------------+
Private Sub tbxCity_Enter()

   logFile.WriteLine "tbxCity_Enter: Enter"
   
   With tbxCity
      tabIndexNbr = .TabIndex
      previousFieldValue = .Text
   End With
   
   logFile.WriteLine "tbxCity_Enter: tabIndexNbr = " & CStr(tabIndexNbr) & ", previousFieldValue = " & tbxCity.Text
   logFile.WriteLine "tbxCity_Enter: Exit"
   
End Sub

Private Sub tbxCity_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxCity_KeyDown: Enter"
   
   ProcessKeyDown KeyCode, Shift

   logFile.WriteLine "tbxCity_KeyDown: KeyCode = " & CStr(KeyCode) & ", Shift = " & CStr(Shift)
   logFile.WriteLine "tbxCity_KeyDown: Exit"

End Sub

Private Sub tbxCity_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   logFile.WriteLine "tbxCity_KeyPress: Enter"
   logFile.WriteLine "tbxCity_KeyPress: KeyAscii = " & CStr(KeyAscii) & ", tbxCity.Value = " & tbxCity.Text
   
   tbxCity.Value = ProcessKeyPress(KeyAscii, tbxCity.Text)

   logFile.WriteLine "tbxCity_KeyPress: tbxZip.Value = " & tbxCity.Text
   logFile.WriteLine "tbxCity_KeyPress: Exit"

End Sub

Private Sub tbxCity_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxCity_KeyUp: Enter"
   logFile.WriteLine "tbxCity_KeyUp: KeyAscii = " & CStr(KeyCode) & ", tbxCity.Value = " & tbxCity.Text
   
   tbxCity.Value = ProcessKeyUp(KeyCode, tbxCity.Text)

   logFile.WriteLine "tbxCity_KeyUp: tbxZip.Value = " & tbxCity.Text
   logFile.WriteLine "tbxCity_KeyUp: Exit"
   
End Sub

Private Sub tbxCity_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   logFile.WriteLine "tbxCity_Exit: Enter"

   With tbxCity
      If StrComp(.Text, previousFieldValue, vbTextCompare) Then
         wksDataEntryList.Range(colCity & Trim(CStr(rowCounter))).Value = .Text
      End If
      
   End With

   logFile.WriteLine "tbxCity_Exit: Exit"

End Sub

' +------------------------------------------------+
' |              Address Line 1                    |
' +------------------------------------------------+
Private Sub tbxAddressLine1_Enter()

   logFile.WriteLine "tbxAddressLine1_Enter: Enter"
   
   With tbxAddressLine1
      tabIndexNbr = .TabIndex
      previousFieldValue = .Text
   End With
   
   logFile.WriteLine "tbxAddressLine1_Enter: tabIndexNbr = " & CStr(tabIndexNbr) & ", previousFieldValue = " & tbxAddressLine1.Text
   logFile.WriteLine "tbxAddressLine1_Enter: Exit"
   
End Sub

Private Sub tbxAddressLine1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxAddressLine1_KeyDown: Enter"
   
   ProcessKeyDown KeyCode, Shift

   logFile.WriteLine "tbxAddressLine1_KeyDown: KeyCode = " & CStr(KeyCode) & ", Shift = " & CStr(Shift)
   logFile.WriteLine "tbxAddressLine1_KeyDown: Exit"

End Sub

Private Sub tbxAddressLine1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   logFile.WriteLine "tbxAddressLine1_KeyPress: Enter"
   logFile.WriteLine "tbxAddressLine1_KeyPress: KeyAscii = " & CStr(KeyAscii) & ", tbxAddressLine1.Value = " & tbxAddressLine1.Text
   
   tbxAddressLine1.Value = ProcessKeyPress(KeyAscii, tbxAddressLine1.Text)

   logFile.WriteLine "tbxAddressLine1_KeyPress: tbxZip.Value = " & tbxAddressLine1.Text
   logFile.WriteLine "tbxAddressLine1_KeyPress: Exit"

End Sub

Private Sub tbxAddressLine1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxAddressLine1_KeyUp: Enter"
   logFile.WriteLine "tbxAddressLine1_KeyUp: KeyAscii = " & CStr(KeyCode) & ", tbxAddressLine1.Value = " & tbxAddressLine1.Text
   
   tbxAddressLine1.Value = ProcessKeyUp(KeyCode, tbxAddressLine1.Text)

   logFile.WriteLine "tbxAddressLine1_KeyUp: tbxZip.Value = " & tbxAddressLine1.Text
   logFile.WriteLine "tbxAddressLine1_KeyUp: Exit"
   
End Sub

Private Sub tbxAddressLine1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   logFile.WriteLine "tbxAddressLine1_Exit: Enter"

   With tbxAddressLine1
      If StrComp(.Text, previousFieldValue, vbTextCompare) Then
         wksDataEntryList.Range(colAddressLine1 & Trim(CStr(rowCounter))).Value = .Text
      End If
      
   End With

   logFile.WriteLine "tbxAddressLine1_Exit: Exit"

End Sub

' +------------------------------------------------+
' |              Address Line 2                    |
' +------------------------------------------------+
Private Sub tbxAddressLine2_Enter()

   logFile.WriteLine "tbxAddressLine2_Enter: Enter"
   
   With tbxAddressLine2
      tabIndexNbr = .TabIndex
      previousFieldValue = .Text
   End With
   
   logFile.WriteLine "tbxAddressLine2_Enter: tabIndexNbr = " & CStr(tabIndexNbr) & ", previousFieldValue = " & tbxAddressLine2.Text
   logFile.WriteLine "tbxAddressLine2_Enter: Exit"
   
End Sub

Private Sub tbxAddressLine2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxAddressLine2_KeyDown: Enter"
   
   ProcessKeyDown KeyCode, Shift

   logFile.WriteLine "tbxAddressLine2_KeyDown: KeyCode = " & CStr(KeyCode) & ", Shift = " & CStr(Shift)
   logFile.WriteLine "tbxAddressLine2_KeyDown: Exit"

End Sub

Private Sub tbxAddressLine2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   logFile.WriteLine "tbxAddressLine2_KeyPress: Enter"
   logFile.WriteLine "tbxAddressLine2_KeyPress: KeyAscii = " & CStr(KeyAscii) & ", tbxAddressLine2.Value = " & tbxAddressLine2.Text
   
   tbxAddressLine2.Value = ProcessKeyPress(KeyAscii, tbxAddressLine2.Text)

   logFile.WriteLine "tbxAddressLine2_KeyPress: tbxZip.Value = " & tbxAddressLine2.Text
   logFile.WriteLine "tbxAddressLine2_KeyPress: Exit"

End Sub

Private Sub tbxAddressLine2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxAddressLine2_KeyUp: Enter"
   logFile.WriteLine "tbxAddressLine2_KeyUp: KeyAscii = " & CStr(KeyCode) & ", tbxAddressLine2.Value = " & tbxAddressLine2.Text
   
   tbxAddressLine2.Value = ProcessKeyUp(KeyCode, tbxAddressLine2.Text)

   logFile.WriteLine "tbxAddressLine2_KeyUp: tbxZip.Value = " & tbxAddressLine2.Text
   logFile.WriteLine "tbxAddressLine2_KeyUp: Exit"
   
End Sub

Private Sub tbxAddressLine2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   logFile.WriteLine "tbxAddressLine2_Exit: Enter"

   With tbxAddressLine2
      If StrComp(.Text, previousFieldValue, vbTextCompare) Then
         wksDataEntryList.Range(colAddressLine2 & Trim(CStr(rowCounter))).Value = .Text
      End If
      
   End With

   logFile.WriteLine "tbxAddressLine2_Exit: Exit"

End Sub

' +------------------------------------------------+
' |                First Name                      |
' +------------------------------------------------+
Private Sub tbxFirstName_Enter()

   logFile.WriteLine "tbxFirstName_Enter: Enter"
   
   With tbxFirstName
      tabIndexNbr = .TabIndex
      previousFieldValue = .Text
   End With
   
   logFile.WriteLine "tbxFirstName_Enter: tabIndexNbr = " & CStr(tabIndexNbr) & ", previousFieldValue = " & tbxFirstName.Text
   logFile.WriteLine "tbxFirstName_Enter: Exit"
   
End Sub

Private Sub tbxFirstName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxFirstName_KeyDown: Enter"
   
   ProcessKeyDown KeyCode, Shift

   logFile.WriteLine "tbxFirstName_KeyDown: KeyCode = " & CStr(KeyCode) & ", Shift = " & CStr(Shift)
   logFile.WriteLine "tbxFirstName_KeyDown: Exit"

End Sub

Private Sub tbxFirstName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   logFile.WriteLine "tbxFirstName_KeyPress: Enter"
   logFile.WriteLine "tbxFirstName_KeyPress: KeyAscii = " & CStr(KeyAscii) & ", tbxFirstName.Value = " & tbxFirstName.Text
   
   tbxFirstName.Value = ProcessKeyPress(KeyAscii, tbxFirstName.Text)

   logFile.WriteLine "tbxFirstName_KeyPress: tbxZip.Value = " & tbxFirstName.Text
   logFile.WriteLine "tbxFirstName_KeyPress: Exit"

End Sub

Private Sub tbxFirstName_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxFirstName_KeyUp: Enter"
   logFile.WriteLine "tbxFirstName_KeyUp: KeyAscii = " & CStr(KeyCode) & ", tbxFirstName.Value = " & tbxFirstName.Text
   
   tbxFirstName.Value = ProcessKeyUp(KeyCode, tbxFirstName.Text)

   logFile.WriteLine "tbxFirstName_KeyUp: tbxZip.Value = " & tbxFirstName.Text
   logFile.WriteLine "tbxFirstName_KeyUp: Exit"
   
End Sub

Private Sub tbxFirstName_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   logFile.WriteLine "tbxFirstName_Exit: Enter"

   With tbxFirstName
      If StrComp(.Text, previousFieldValue, vbTextCompare) Then
         wksDataEntryList.Range(colFirstName & Trim(CStr(rowCounter))).Value = .Text
      End If
      
   End With

   logFile.WriteLine "tbxFirstName_Exit: Exit"

End Sub

' +------------------------------------------------+
' |                Last Name                       |
' +------------------------------------------------+
Private Sub tbxLastName_Enter()

'  Capture the text in the control, to check for change
'  upon exiting the control
   logFile.WriteLine "tbxLastName_Enter: Enter"
   
   With tbxLastName
      tabIndexNbr = .TabIndex
      previousFieldValue = .Text
   End With
   
   logFile.WriteLine "tbxLastName_Enter: tabIndexNbr = " & CStr(tabIndexNbr) & ", previousFieldValue = " & tbxLastName.Text
   logFile.WriteLine "tbxLastName_Enter: Exit"
   
End Sub

Private Sub tbxLastName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxLastName_KeyDown: Enter"
   
   ProcessKeyDown KeyCode, Shift

   logFile.WriteLine "tbxLastName_KeyDown: KeyCode = " & CStr(KeyCode) & ", Shift = " & CStr(Shift)
   logFile.WriteLine "tbxLastName_KeyDown: Exit"

End Sub

Private Sub tbxLastName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   logFile.WriteLine "tbxLastName_KeyPress: Enter"
   logFile.WriteLine "tbxLastName_KeyPress: KeyAscii = " & CStr(KeyAscii) & ", tbxLastName.Value = " & tbxLastName.Text
   
   tbxLastName.Value = ProcessKeyPress(KeyAscii, tbxLastName.Text)

   logFile.WriteLine "tbxLastName_KeyPress: tbxZip.Value = " & tbxLastName.Text
   logFile.WriteLine "tbxLastName_KeyPress: Exit"

End Sub

Private Sub tbxLastName_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxLastName_KeyUp: Enter"
   logFile.WriteLine "tbxLastName_KeyUp: KeyAscii = " & CStr(KeyCode) & ", tbxLastName.Value = " & tbxLastName.Text
   
   tbxLastName.Value = ProcessKeyUp(KeyCode, tbxLastName.Text)

   logFile.WriteLine "tbxLastName_KeyUp: tbxZip.Value = " & tbxLastName.Text
   logFile.WriteLine "tbxLastName_KeyUp: Exit"
   
End Sub

Private Sub tbxLastName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   
'  Check the current contents of the control and compare with the
'  contents at time of entry
   logFile.WriteLine "tbxLastName_Exit: Enter"

   If StrComp(tbxLastName.Text, previousFieldValue, vbTextCompare) Then
      wksDataEntryList.Range(colLastName & Trim(CStr(rowCounter))).Value = tbxLastName.Text
   End If
   
   logFile.WriteLine "tbxLastName_Exit: Exit"

End Sub

' +------------------------------------------------+
' |                Middle Name                     |
' +------------------------------------------------+
Private Sub tbxMiddleName_Enter()

   logFile.WriteLine "tbxMiddleName_Enter: Enter"
   
   With tbxMiddleName
      tabIndexNbr = .TabIndex
      previousFieldValue = .Text
   End With
      
   logFile.WriteLine "tbxMiddleName_Enter: tabIndexNbr = " & CStr(tabIndexNbr) & ", previousFieldValue = " & tbxMiddleName.Text
   logFile.WriteLine "tbxMiddleName_Enter: Exit"
   
End Sub

Private Sub tbxMiddleName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxMiddleName_KeyDown: Enter"
   
   ProcessKeyDown KeyCode, Shift

   logFile.WriteLine "tbxMiddleName_KeyDown: KeyCode = " & CStr(KeyCode) & ", Shift = " & CStr(Shift)
   logFile.WriteLine "tbxMiddleName_KeyDown: Exit"

End Sub

Private Sub tbxMiddleName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

   logFile.WriteLine "tbxMiddleName_KeyPress: Enter"
   logFile.WriteLine "tbxMiddleName_KeyPress: KeyAscii = " & CStr(KeyAscii) & ", tbxMiddleName.Value = " & tbxMiddleName.Text
   
   tbxMiddleName.Value = ProcessKeyPress(KeyAscii, tbxMiddleName.Text)

   logFile.WriteLine "tbxMiddleName_KeyPress: tbxZip.Value = " & tbxMiddleName.Text
   logFile.WriteLine "tbxMiddleName_KeyPress: Exit"

End Sub

Private Sub tbxMiddleName_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   logFile.WriteLine "tbxMiddleName_KeyUp: Enter"
   logFile.WriteLine "tbxMiddleName_KeyUp: KeyAscii = " & CStr(KeyCode) & ", tbxMiddleName.Value = " & tbxMiddleName.Text
   
   tbxMiddleName.Value = ProcessKeyUp(KeyCode, tbxMiddleName.Text)

   logFile.WriteLine "tbxMiddleName_KeyUp: tbxZip.Value = " & tbxMiddleName.Text
   logFile.WriteLine "tbxMiddleName_KeyUp: Exit"
   
End Sub

Private Sub tbxMiddleName_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   logFile.WriteLine "tbxMiddleName_Exit: Enter"

   With tbxMiddleName
      If StrComp(.Text, previousFieldValue, vbTextCompare) Then
         wksDataEntryList.Range(colMiddleName & CStr(rowCounter)).Value = .Text
      End If
      
   End With

   logFile.WriteLine "tbxMiddleName_Exit: Exit"

End Sub

' +------------------------------------------------+
' |                  Utilities                     |
' +------------------------------------------------+
Private Sub ClearFields()

   logFile.WriteLine "ClearFields: Enter"
   
   tbxLastName.Value = ""
   tbxFirstName.Value = ""
   tbxMiddleName.Value = ""
   tbxAddressLine1.Value = ""
   tbxAddressLine2.Value = ""
   tbxCity.Value = ""
   tbxState.Value = ""
   tbxZip.Value = ""
   tbxPhoneNumber.Value = ""
   
   logFile.WriteLine "ClearFields: Exit"

End Sub

Private Sub ProcessKeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal shiftCode As Integer)

   logFile.WriteLine "ProcessKeyDown: Enter"
   logFile.WriteLine "ProcessKeyDown: KeyCode = " & CStr(KeyCode) & ", shiftCode = " & CStr(shiftCode) & ", previousKeyWasTab = " & previousKeyWasTab
   
   If KeyCode = vbKeyTab Then
      tabIndexNbr = (tabIndexNbr + 1) Mod 9
      logFile.WriteLine "ProcessKeyDown: tabIndexNbr = " & CStr(tabIndexNbr)
      Select Case tabIndexNbr
         Case 0
            tbxLastName.SetFocus
            rowCounter = rowCounter + 1
            ClearFields
         Case 1
            tbxFirstName.SetFocus
         Case 2
            tbxMiddleName.SetFocus
         Case 3
            tbxAddressLine1.SetFocus
         Case 4
            tbxAddressLine2.SetFocus
         Case 5
            tbxCity.SetFocus
         Case 6
            tbxState.SetFocus
         Case 7
            tbxZip.SetFocus
         Case 8
            tbxPhoneNumber.SetFocus
         Case Default
            ' Do Nothing
      End Select
      
   End If

   logFile.WriteLine "ProcessKeyDown: Exit"

End Sub

Private Function ProcessKeyPress(ByVal KeyAscii As MSForms.ReturnInteger, ByVal fieldText As String) As String

   logFile.WriteLine "ProcessKeyPress: KeyAscii = " & Chr(KeyAscii) & ", fieldText = """ & fieldText & """"

'    MsgBox "ProcessKeyPress: KeyAscii = " & Chr(KeyAscii) & ", tabIndexNbr = " & CStr(tabIndexNbr), vbOKOnly, "FYI!"
'   If KeyAscii = Asc(vbTab) Then
'      If Len(fieldText) > 0 Then
'         fieldText = Replace(fieldText, vbTab, "", 1)
'      End If
'   Else
      KeystrokeCounter = KeystrokeCounter + 1
      lblKeystrokeCount.Caption = Trim(CStr(KeystrokeCounter))
'   End If
   ProcessKeyPress = fieldText

   logFile.WriteLine "ProcessKeyPress: Exit...fieldText = """ & fieldText & """"

End Function

Private Function ProcessKeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal fieldText As String) As String

   logFile.WriteLine "ProcessKeyUp: Enter"
   logFile.WriteLine "ProcessKeyUp: KeyCode = " & CStr(KeyCode) & ", fieldText = """ & fieldText & """"
   
   If KeyCode = Asc(vbTab) Then
      previousKeyWasTab = True
      If Len(fieldText) > 0 Then
         fieldText = Replace(fieldText, vbTab, "", 1)
      End If
   End If
   ProcessKeyUp = fieldText
   
   logFile.WriteLine "ProcessKeyUp: Exit...fieldText = """ & fieldText & """"

End Function

 

[combo]

You try to use non-initialised object, move the first marked line after the second one:

Dim logFile As Object

' +------------------------------------------------+
' |                    Forms                       |
' +------------------------------------------------+
Private Sub UserForm_Initialize()

   [!]logFile.WriteLine "UserForm_Initialize: Enter"[/!]
   '...
   [!]Set logFile = fsoLogfileHandle.CreateTextFile("C:\Documents and Settings\jeblanch\My Documents\Personal\KeystrokeCounter.log", True)[/!]

combo