Runtime Error 91

Sara Hedtler
Sara Hedtler used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You have to define oWs on line 157 for the code to work. At the moment it is commented out.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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?
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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 ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Sara HedtlerInsurance Director

Author

Commented:
I've attached it, the other project is working just fine so i'm not sure why this one wont.  trucking.xlsm
Analyst 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

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Sara HedtlerInsurance Director

Author

Commented:
Norie, that worked perfectly.  Thank you so much.  THANK YOU ALL!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial