troubleshooting Question

Runtime Error 91

Avatar of Sara Hedtler
Sara Hedtler asked on
Microsoft ExcelMicrosoft Office
7 Comments1 Solution127 ViewsLast Modified:
I keep getting an error when opening a form.  Runtime Error 91 "Object Variable or With Block Variable Not Set".  The code is a copy and past from another workbook and that workbook doesn't have any issues.

Option Explicit
Dim oWs As Worksheet
Dim rData As Range
'Dim Sheet8 As Worksheet
Dim Unit As Single
Dim Year As Date
Dim Make As String
Dim Model As String
Dim Vin As String
Dim Driver As String
Dim lRw As Long
Function IsClear() As Boolean
    If txtVehNumber.Text = "" And _
       txtYear.Text = "" And _
       txtMake.Text = "" And _
       txtModel.Text = "" And _
       cboDescription.Text = "" And _
       txtVin.Text = "" And _
       txtValue = "" And _
       cboOwnership.Text = "" And _
       cmdGaraging.Text = "" And _
       txtDriver.Text = "" Then
       IsClear = True
    End If
End Function
Sub LoadTextBox()
Dim oTxt As Object
Dim I As Long, J As Long
Dim bFoundit As Boolean
Dim rRng As Range
Dim iX As Integer

If ListBox1.ListIndex <> -1 Then
    I = ListBox1.ListIndex
    
    '---> Loop Through the TextBoxes in the Form
    For Each oTxt In Me.Controls
        bFoundit = False
        If TypeName(oTxt) = "TextBox" Or TypeName(oTxt) = "ComboBox" Then
            '---> Get The Coresponding Column of the Item
            For J = 1 To ListBox1.ColumnCount
                If oWs.Range(ListBox1.RowSource).Cells(0, J) = oTxt.Tag Then
                    bFoundit = True
                    Exit For
                End If
            Next J
            If bFoundit Then oTxt.Text = ListBox1.List(I, J - 1)
        End If
    Next oTxt
End If
    txtValue = Format(txtValue, "$#,###")
End Sub
Private Sub cmdAdd_Click()
If Not IsClear Then
    If MsgBox("Ready to Save new Record ?", vbQuestion + vbYesNo, "Add Data") = vbYes Then
    
        lRw = rData.Rows.Count + 1
        WriteToSheet
        Set rData = oWs.Range("A1").CurrentRegion
        Clear
        UpdateTotalValues
    Else
        MsgBox "Operation cancelled by user", vbInformation, "Add Data"
    End If
Else
    MsgBox "All Fields are empty !!! Please fill in some Data to Add a new Record.", vbExclamation, "Add Data"
End If
        
End Sub
Sub Clear()
        txtVehNumber.Text = ""
        txtYear.Text = ""
        txtMake.Text = ""
        txtModel.Text = ""
        cboDescription.Text = ""
        txtVin.Text = ""
        txtValue = ""
        txtDriver.Text = ""
        cboOwnership = ""
        cmdGaraging = ""
End Sub
Private Sub cmdClose_Click()
    Unload Me
End Sub
Private Sub cmdDelete_Click()
'If Not IsClear Then
    If MsgBox("Are you ready to Delete Item " & txtVehNumber & " on row# " & lRw & " ?", vbQuestion + vbYesNo, "Delete Record") = vbYes Then
       DeleteFmSheet
        MsgBox "Record " & txtVehNumber & " on row# " & lRw & " Successfully Deleted From Database.", vbInformation, "Delete From Sheet"
        Clear
        UpdateTotalValues
    End If
Else
    MsgBox "All Fields are empty, No record has been selected. Please select a Record for deletion first.", vbExclamation, "Delete Data"
End If

End Sub
Private Sub cmdSave_Click()
    WriteToSheet
    MsgBox "Record " & txtVehNumber & " on row# " & lRw & " Successfully saved to Database.", vbInformation, "Write to Sheet"
    Clear
    UpdateTotalValues
End Sub
Private Sub ListBox1_Click()
    LoadTextBox
    lRw = ListBox1.ListIndex + 2
End Sub
Private Sub txtValue_AfterUpdate()
    txtValue = Format(txtValue, "$#,###")
End Sub
Private Sub UserForm_Activate()
    'WriteToSheet
End Sub
Sub DeleteFmSheet()
    Dim sRowSource As String
    
    '---> Temporary Disable Rowsource to perform Update
    sRowSource = ListBox1.RowSource
    ListBox1.RowSource = ""
    
        If lRw < 1 Then lRw = rData.Rows.Count + 1
        With oWs
            .Range(lRw & ":" & lRw).EntireRow.Delete
        End With
        
    '---> Enable Rowsource
    ListBox1.RowSource = sRowSource

End Sub
Sub WriteToSheet()
    Dim sRowSource As String
    
    '---> Temporary Disable Rowsource to perform Update
    sRowSource = ListBox1.RowSource
    ListBox1.RowSource = ""

    If lRw < 1 Then lRw = rData.Rows.Count + 1
    With oWs
        .Cells(lRw, 1).Value = txtVehNumber
        .Cells(lRw, 2).Value = txtYear
        .Cells(lRw, 3).Value = txtMake
        .Cells(lRw, 4).Value = txtModel
        .Cells(lRw, 5).Value = cboDescription
        .Cells(lRw, 6).Value = txtVin
        .Cells(lRw, 7).Value = txtValue.Value
        .Cells(lRw, 9).Value = cboOwnership
        .Cells(lRw, 8).Value = txtDriver
        .Cells(lRw, 10).Value = cmdGaraging
        
    End With
    
    '---> Enable Rowsource
    ListBox1.RowSource = sRowSource

End Sub
Private Sub UserForm_Initialize()
  '  Set oWs = Sheet8
    Set rData = oWs.Range("A1").CurrentRegion
    With Me
        .cboDescription.List = Array("TRACTOR", "PICKUP", "PRIVATE PASSANGER", "VAN", "TRAILER")
        .cboOwnership.List = Array("COMPANY UNIT", "OWNER OPERATOR", "LEASED", "OTHER")
        .cboDescription.ListIndex = -1
        .cboOwnership.ListIndex = -1
    End With
    UpdateTotalValues
    oWs.Application.WorksheetFunction.Sum ("G")
End Sub
ASKER CERTIFIED SOLUTION
NorieAnalyst Assistant
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros