Runtime Error 91

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

Open in new window

Sara HedtlerInsurance DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
You have to define oWs on line 157 for the code to work. At the moment it is commented out.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Why have you commented out the line#157? The worksheet variable oWs is not set anywhere else on the module.
Also what is UpdateTotalValues at line#165? Where it is defined?
0
Fabrice LambertFabrice LambertCommented:
As other said, your oWs variable should be initialised with a Set statement, but you commented the line doing it.

Plus, the line #157 is supposed to initialise oWs to Sheet8, but the Sheet8 variable is not declared (declaration is commented), and worst, never initialised.

Finally, do you really need that much global variables ?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Sara HedtlerInsurance DirectorAuthor Commented:
I've attached it, the other project is working just fine so i'm not sure why this one wont.  trucking.xlsm
0
NorieAnalyst Assistant Commented:
This shouldn't be in the code.
Dim Sheet8 As Worksheet

Open in new window

If I comment that out, or remove it, then the code works fine - after I also comment/remove this part.
oWs.Application.WorksheetFunction.Sum ("G")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you look at the code underneath the UserForm AutoFleetForm, the variable oWs is properly set in the UserForm Initialize event.
Look at the line#2
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

Open in new window

0
Sara HedtlerInsurance DirectorAuthor Commented:
Norie, that worked perfectly.  Thank you so much.  THANK YOU ALL!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.