VBA Error - Run-time error '91': Object variable or With block variable not set

Question

I am getting error code 91 but can't determine what I need to fix.  Everything looks good but I am clearly not right.  This code populates a list box that will populate controls to add training when a line is double ckicked in the box.

Any ideas as to what I can do to fix this?

Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

'declare the variables

    Dim ID As String

    Dim I As Integer

    Dim findvalue As Range

'get the select value from the listbox

    For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

'set the listbox column

            ID = lstLookup.List(I, 14)

        End If

    Next I

'find the value in the range

 Set findvalue = Sheet2.Range("H:H").Find(What:=ID, LookIn:=xlValues).Offset(0, -6)

'add the values to the userform controls

    cNum = 10

    For X = 1 To cNum

    Me.Controls("Reg" & X).Value = findvalue

        Set findvalue = findvalue.Offset(0, 1)

    Next

'disable the controls to make the user select an option

    With Me

    .cmdAdd.Enabled = False

    .cmdAdd.BackColor = RGB(225, 225, 225)

    .cmdEdit.Enabled = False

    .cmdEdit.BackColor = RGB(225, 225, 225)

    .cmdTraining.Enabled = False

    .cmdTraining.BackColor = RGB(225, 225, 225)

    .optAdd = False

    .optEdit = False

    .optTraining = False

    End With

End Sub

Answer 1

Re: get error message

First thing I spotted was there is no exit from the For/Next loop after the item is found so the loop continues on.

'----
For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

            ID = lstLookup.List(I, 14)

            Exit For                               '<<<<<

        End If

    Next I

'---

Also, if the item is not found (findvalue is nothing) then

you need to generate a Msgbox and Exit.

'---
NLtL

https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O

(free excel programs)

Answer 2

Re: get error message

First thing I spotted was there is no exit from the For/Next loop after the item is found so the loop continues on.

'----
For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

            ID = lstLookup.List(I, 14)

            Exit For                               '<<<<<

        End If

    Next I

'---

Also, if the item is not found (findvalue is nothing) then

you need to generate a Msgbox and Exit.

'---
NLtL

https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O

(free excel programs)

Thanks for the info.  I am really new at this so pardon me if I ask a dumb question (or two).  I changed the code to include an error command. Since I get an error I assume this is incorrect on my part.

Revised Code:<<

Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

'declare the variables

    Dim ID As String

    Dim I As Integer

    Dim findvalue As Range

    'error block

On Error GoTo errHandler:

'get the select value from the listbox

    For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

'set the listbox column

            ID = lstLookup.List(I, 14)

Exit For

End If

    Next I

'find the value in the range

 Set findvalue = Sheet2.Range("H:H").Find(What:=ID, LookIn:=xlValues).Offset(0, -6)

'add the values to the userform controls

    cNum = 10

    For X = 1 To cNum

    Me.Controls("Reg" & X).Value = findvalue

        Set findvalue = findvalue.Offset(0, 1)

    Next

'disable the controls to make the user select an option

        With Me

            .cmdAdd.Enabled = False

            .cmdAdd.BackColor = RGB(225, 225, 225)

            .cmdEdit.Enabled = False

            .cmdEdit.BackColor = RGB(225, 225, 225)

            .cmdTraining.Enabled = False

            .cmdTraining.BackColor = RGB(225, 225, 225)

            .optAdd = False

            .optEdit = False

            .optTraining = False

        End With

'error block

On Error GoTo 0

Exit Sub

errHandler::

MsgBox "An Error has Occurred "

End Sub

Answer 3

Re: get error message

First thing I spotted was there is no exit from the For/Next loop after the item is found so the loop continues on.

'----
For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

            ID = lstLookup.List(I, 14)

            Exit For                               '<<<<<

        End If

    Next I

'---

Also, if the item is not found (findvalue is nothing) then

you need to generate a Msgbox and Exit.

'---
NLtL

https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O

(free excel programs)

Sorry 

Corrected revised code. Still get error below:

Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

'declare the variables

    Dim ID As String

    Dim I As Integer

    Dim findvalue As Range

    'error block

      On Error GoTo errHandler:

'get the select value from the listbox

    For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

'set the listbox column

            ID = lstLookup.List(I, 14)

  If lstLookup.Selected(I) = "" Then

MsgBox "No data found"

Exit For

               End If

    Next I

'find the value in the range

 Set findvalue = Sheet2.Range("H:H").Find(What:=ID, LookIn:=xlValues).Offset(0, -6)

'add the values to the userform controls

    cNum = 10

    For X = 1 To cNum

    Me.Controls("Reg" & X).Value = findvalue

        Set findvalue = findvalue.Offset(0, 1)

    Next

'disable the controls to make the user select an option

        With Me

            .cmdAdd.Enabled = False

            .cmdAdd.BackColor = RGB(225, 225, 225)

            .cmdEdit.Enabled = False

            .cmdEdit.BackColor = RGB(225, 225, 225)

            .cmdTraining.Enabled = False

            .cmdTraining.BackColor = RGB(225, 225, 225)

            .optAdd = False

            .optEdit = False

            .optTraining = False

        End With

'error block

On Error GoTo 0

Exit Sub

errHandler::

MsgBox "An Error has Occurred "

End Sub

Answer 4

re:  still have code problems

I can't run or compile the code as I don't have the Control.
Strongly suggest the use of Option Explicit
The following looks about right...
'---
Private Sub l1stLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo errHandler                     '<<<<
'declare the variables
    Dim ID As String
    Dim I As Integer
    Dim cNum As Long, X As Long         '<<<<
    Dim findvalue As Range
    'error block
    On Error GoTo errHandler:
'get the select value from the listbox
    For I = 0 To 1stLookup.ListCount - 1
        If 1stLookup.Selected(I) = True Then
         'set the listbox column
          ID = 1stLookup.List(I, 14)
          Exit For
        End If
    Next I
    If VBA.Len(ID) < 1 Then          '<<<<
        MsgBox "No data found"
        Cancel = True                      '<<<<
        Exit Sub                               '<<<<
    End If

'find the value in the range
 Set findvalue = Sheet2.Range("H:H").Find(What:=ID, LookIn:=xlValues).Offset(0, -6)
'add the values to the userform controls
    cNum = 10
    For X = 1 To cNum
    Me.Controls("Reg" & X).Value = findvalue
        Set findvalue = findvalue.Offset(0, 1)
    Next

'disable the controls to make the user select an option
 With Me

    'IS THIS ENABLED SOMEWHERE ELSE OR DOES IT MATTER?  '<<<<
     .cmdAdd.Enabled = False 
     .cmdAdd.BackColor = RGB(225, 225, 225)
     .cmdEdit.Enabled = False
     .cmdEdit.BackColor = RGB(225, 225, 225)
     .cmdTraining.Enabled = False
     .cmdTraining.BackColor = RGB(225, 225, 225)
     .optAdd = False
     .optEdit = False
     .optTraining = False
 End With
Exit Sub                                '<<<<

errHandler:

    MsgBox "An Error has Occurred "

    Cancel = True
End Sub
'---

Note:  A msgbox with the error details could help resolve problems in any code set.

'Something like this would work...

VBA.MsgBox "Error " & VBA.Err.Number & " - " & VBA.Err.Description & _
"   ", vbCritical, "Name of Routine "

'---
NLtL https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O

Answer 5

I should be able to double click the line item in the list box and that data fills the controls underneath, then the code will grey out the boxes and force the user to select one of the options at the bottom.  Here is the  user form on top of the range, criteria, and copy to locations in the worksheet.  Blue is formula driven on the "range" side and all is copied to the right by the use of an advanced filter (below)  After adding everything I started getting error 91"Object variable or With Block not set" through the debugger, and the data will still not load on double click from the list box.

Sub AdvFilter()

'Run the advanced filter

With Sheet2

    Range("B8:R30000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _

        Range("HistData!Criteria"), CopyToRange:=Range("AD8:AT30000"), Unique:= _

        False

        End With

End Sub

THe code you referenced is in the code for each of the option buttons, as well as the "DoubleClick" event.

Added error code:

Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

'declare the variables

    Dim ID As String

    Dim I As Integer

    Dim findvalue As Range

'get the select value from the listbox

    For I = 0 To lstLookup.ListCount - 1

        If lstLookup.Selected(I) = True Then

'set the listbox column

        ID = lstLookup.List(I, 14)

        If lstLookup.Selected(I) = "" Then

        MsgBox "No data found"

        Exit For

               End If

        End If

    Next I

'find the value in the range

 Set findvalue = Sheet2.Range("H:H").Find(What:=ID, LookIn:=xlValues).Offset(0, -6)

'add the values to the userform controls

    cNum = 10

    For X = 1 To cNum

    Me.Controls("Reg" & X).Value = findvalue

        Set findvalue = findvalue.Offset(0, 1)

    Next

'disable the controls to make the user select an option

    With Me

    .cmdAdd.Enabled = False

    .cmdAdd.BackColor = RGB(225, 225, 225)

    .cmdEdit.Enabled = False

    .cmdEdit.BackColor = RGB(225, 225, 225)

    .cmdTraining.Enabled = False

    .cmdTraining.BackColor = RGB(225, 225, 225)

    .optAdd = False

    .optEdit = False

    .optTraining = False

    End With

'error block

    On Error GoTo 0

    Exit Sub

errHandler::

    MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

           & Err.Number & vbCrLf & Err.Description & vbCrLf & _

           "Please notify the administrator"

End Sub