VBA runtime 91 error

[Rasscal]

When deleting an entry from my DB using a vba userform I received an error:

"An error has occurred the error number 91 object variable or with block variable not set"
With my limited knowledge of vba I'm struggling to understand what the issue is, please could somebody advise?

My data writes to the history log (sheet3) but then the error message is displayed.

thanks

Private Sub cmdDelete_Click()

    'declare the variables
    Dim findvalue As Range 'done
    Dim cDelete As VbMsgBoxResult 'done
    Dim cNum As Integer 'done
    Dim DataSH As Worksheet 'done
    Dim Addme As Range 'done
    Dim HistorySH As Worksheet 'done
    Dim x As Integer

    'error statement
    On Error GoTo errHandler:
    'hold in memory and stop screen flicker
    Application.ScreenUpdating = False
    Set DataSH = Sheet1
    Set HistorySH = Sheet3
    Set Addme = HistorySH.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)

    'check for values
    If txtID.Value = "" Or txtLocation.Value = "" Then
    MsgBox "There Is No Data To Delete"
    Exit Sub
    End If
    'give the user a chance to change their mind
    cDelete = MsgBox("Are You Sure That you Want To Delete This Pallet", vbYesNo + vbDefaultButton2, "Are you sure?")
    If cDelete = vbYes Then
    'find the row
    Set findvalue = DataSH.Range("B:B").Find(What:=Me.txtID.Value, _
    LookIn:=xlValues, LookAt:=xlWhole)

    'update the values in the HistoryLog

    With HistorySH
    'add the unique reference ID then all other values
    Addme.Offset(0, -1) = txtID.Value
    Addme.Offset(0, 0).Value = "Deleted"
    Addme.Offset(0, 1).Value = Me.txtSize
    Addme.Offset(0, 2).Value = Me.txtLocation
    Addme.Offset(0, 3).Value = Me.cboType
    Addme.Offset(0, 4).Value = Me.txtDescription
    Addme.Offset(0, 5).Value = Me.txtContact
    Addme.Offset(0, 6).Value = Me.txtDate.Value
    Addme.Offset(0, 7).Value = Format(Now(), "hh:mm:")
    Addme.Offset(0, 8 .Value = UserName()

    End With

    'delete the entire row
    findvalue.EntireRow.Delete
    End If
    'clear the controls
    cNum = 7
    For x = 1 To cNum
    Me.Controls("txt" & x).Value = ""
    Next
    'unprotect all sheets for the advanced filter
    'Unprotect_All
    'filter the data
    DataSH.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheet6.Range("$L$1:$L$2"), CopyToRange:=Sheet6.Range("$N$1:$w$1"), _
    Unique:=False
    'if no data exists then clear the rowsource
    If Sheet6.Range("N2").Value = "" Then
    lstData.RowSource = ""
    Else
    'add the filtered data to the rowsource
    lstData.RowSource = Sheet6.Range("outdata").Address(external:=True)
    End If

    'sort the database by "Location"
    DataSH.Select
    With DataSH
    .Range("DataTable").Sort Key1:=Range("E9"), Order1:=xlAscending, Header:=xlGuess
    End With

    'sort the HistoryLog by "ID"
    HistorySH.Select
    With HistorySH
    .Range("DataTable2").Sort Key1:=Range("B9"), Order1:=xlAscending, Header:=xlGuess

    End With
    'Protect all sheets
    'Protect_All
    'return to sheet
    Sheet2.Select
    'error block
    On Error GoTo 0
    Exit Sub
    errHandler:
    'Protect all sheets if error occurs
    'Protect_All
    'show error information in a messagebox
    MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " & _
    Err.Number & vbCrLf & Err.Description & vbCrLf & "Please notify the administrator"

End Sub

 

[DanteAmor]

On which line does the code stop?


In this line a parenthesis is missing

Addme.Offset(0, 8[COLOR=#ff0000][B])[/B][/COLOR].Value = UserName()

You can explain what it is
UserName()

 

[igold]

If you have a limited knowledge of VBA, then one of the things you should start doing is to use "Option Explicit" at the very top of your code. Amongst other things, this will force you to declare all your variables.

When I put your code in the Visual Basic Editor it does not compile because of multiple undeclared variables, and invalid keyword use. That said, I think the particular error you are getting is on this line:

[COLOR=#333333]Set findvalue = DataSH.Range("B:B").Find(What:=Me.txtID.Value, _[/COLOR]
[COLOR=#333333]LookIn:=xlValues, LookAt:=xlWhole)[/COLOR]

I do not believe that is your only error, but without seeing your worksheet, it's hard to know.

EDIT: Thanks @DanteAmor, I saw that too but neglected to mention it.

 

[DanteAmor]

The error is the delete line.

[COLOR=#ff0000]findvalue.EntireRow.Delete[/COLOR]

If it does not find the data then the object findvalue is empty.
You can verify it this way:

1. Remove the On Error statement. It is advisable to verify each possible error.
2. Check the changes in colors.

Option Explicit


Private Sub cmdDelete_Click()
  'declare the variables
  Dim findvalue As Range 'done
  Dim cDelete As VbMsgBoxResult 'done
  Dim cNum As Integer 'done
  Dim DataSH As Worksheet 'done
  Dim Addme As Range 'done
  Dim HistorySH As Worksheet 'done
  Dim x As Integer
  
  'error statement
  'On Error GoTo errHandler:
  'hold in memory and stop screen flicker
  Application.ScreenUpdating = False
  Set DataSH = sheet1
  Set HistorySH = Sheet3
  Set Addme = HistorySH.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
  
  'check for values
  If txtID.Value = "" Or txtLocation.Value = "" Then
    MsgBox "There Is No Data To Delete"
    Exit Sub
  End If
  'give the user a chance to change their mind
  cDelete = MsgBox("Are You Sure That you Want To Delete This Pallet", vbYesNo + vbDefaultButton2, "Are you sure?")
  If cDelete = vbYes Then
    'find the row
    Set findvalue = DataSH.Range("B:B").Find(What:=Me.txtID.Value, LookIn:=xlValues, LookAt:=xlWhole)
    'update the values in the HistoryLog
    With HistorySH
      'add the unique reference ID then all other values
      Addme.Offset(0, -1) = txtID.Value
      Addme.Offset(0, 0).Value = "Deleted"
      Addme.Offset(0, 1).Value = Me.txtSize
      Addme.Offset(0, 2).Value = Me.txtLocation
      Addme.Offset(0, 3).Value = Me.cboType
      Addme.Offset(0, 4).Value = Me.txtDescription
      Addme.Offset(0, 5).Value = Me.txtContact
      Addme.Offset(0, 6).Value = Me.txtDate.Value
      Addme.Offset(0, 7).Value = Format(Now(), "hh:mm:")
      '[COLOR=#ff0000]Addme.Offset(0, 8[SIZE=3][B])[/B][/SIZE].Value = UserName()[/COLOR]
    End With
    
    'delete the entire row
[COLOR=#0000ff]    If Not findvalue Is Nothing Then[/COLOR]
      findvalue.EntireRow.Delete
[COLOR=#0000ff]    Else[/COLOR]
      MsgBox "Id does not exist"
[COLOR=#0000ff]    End If[/COLOR]
  End If
  'clear the controls
[COLOR=#ff0000]'  cNum = 7[/COLOR]
[COLOR=#ff0000]'  For x = 1 To cNum[/COLOR]
[COLOR=#ff0000]'    Me.Controls("txt" & x).Value = ""[/COLOR]
[COLOR=#ff0000]'  Next[/COLOR]
'If the controls do not have a number in the name then you have to put the full name
[COLOR=#0000ff]  txtID.Value = ""[/COLOR]
[COLOR=#0000ff]  txtSize.Value = ""[/COLOR]
[COLOR=#0000ff]  txtLocation.Value = ""[/COLOR]
  'unprotect all sheets for the advanced filter
  'Unprotect_All
  'filter the data
  DataSH.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheet6.Range("$L$1:$L$2"), CopyToRange:=Sheet6.Range("$N$1:$w$1"), Unique:=False
  'if no data exists then clear the rowsource
  If Sheet6.Range("N2").Value = "" Then
    lstData.RowSource = ""
  Else
    'add the filtered data to the rowsource
    lstData.RowSource = Sheet6.Range("outdata").Address(external:=True)
  End If
  
  'sort the database by "Location"
  DataSH.Select
  With DataSH
    .Range("DataTable").Sort Key1:=Range("E9"), Order1:=xlAscending, Header:=xlGuess
  End With
  
  'sort the HistoryLog by "ID"
  HistorySH.Select
  With HistorySH
    .Range("DataTable2").Sort Key1:=Range("B9"), Order1:=xlAscending, Header:=xlGuess
  End With
  'Protect all sheets
  'Protect_All
  'return to sheet
  Sheet2.Select
End Sub

 

[Rasscal]

Sorry the parenthesis is missing because I posted somewhere else & using my phone it became an emoji for some reason ( I forgot to add it back in)

Username() is a call back from a module that will display the environment user

 

[Rasscal]

Thanks For your help, I will check this out tomorrow.

i didn’t understand the cNum & controls you highlighted, the tutorial I was using for reference didn’t cover these, but it makes a little more sense now.

 

[DanteAmor]

Do not use the On Error instruction, that causes any error to jump at the end of the program and you will not know in which line you have the problem.