help with 91 error

[rickblunt]

Hello,
I am getting an object variable or with block variable not set error when I add this line "Unload DataEntryElGr" to my code and I can't figure out why, the code runs perfect until I add it....

The sub is in the "DataEntryElGr" userform, The purpose of this string is simply to close the form instead of having to click on the "X". I appreciate any input - thanks,

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim Lastrow As Long
    'this is from the DEVICE ID column
    
    If ThisWorkbook.Sheets("Emergency Lighting Log").Range("M1").Value = 0 Then
    MsgBox " All emergency lighting has been inspected in this area, please continue on to the next inspection area. "
    
    EmergencyLightArea.Show
    
[COLOR=#ff0000]    Unload DataEntryElGr[/COLOR]
        
       
    Else
      
        Lastrow = Cells(Rows.Count, "L").End(xlUp).Row
         For i = 1 To Lastrow
             If Cells(i, 15).Value = "" Then ListBox1.AddItem Cells(i, 12).Value
         Next
         Me.TextBox4.Text = CStr(ThisWorkbook.Sheets("Emergency Lighting Log").Range("M1").Value)
    
        TextBox1.SetFocus
    End If
    
    
End Sub

 

[Norie]

You can't refer directly to a userform with its name but you can refer to a userform in its own code by using the Me keyword, so change that line to this.

Unload Me

 

[rickblunt]

Thanks Norie - I had often seen the "Me" statement, I thought it was just another technique for code. Now I know that it is for a specific purpose, to refer to objects in its own sub - cool. Thanks once again for more knowledge, I really appreciate it.

 

[rickblunt]

Uh oh - still getting that error even after changing the string to Unload Me.....

 

[rickblunt]

OK, it appears that the issue is that I am using this string inside of the "UserForm_Initialize ()" event, when I place the string in another sub (like CommandButton1_Click()) then it runs fine. Anyone know the nuances of this?

thanks

 

[Norie]

Which userform is this code for?

What happens if you swap the Show and Unload around?

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim Lastrow As Long
    'this is from the DEVICE ID column
    
    If ThisWorkbook.Sheets("Emergency Lighting Log").Range("M1").Value = 0 Then
        MsgBox " All emergency lighting has been inspected in this area, please continue on to the next inspection area. "
    
        Unload Me
    
        EmergencyLightArea.Show
          
    Else
      
        Lastrow = Cells(Rows.Count, "L").End(xlUp).Row
        For i = 1 To Lastrow
             If Cells(i, 15).Value = "" Then ListBox1.AddItem Cells(i, 12).Value
        Next
         
        Me.TextBox4.Text = CStr(ThisWorkbook.Sheets("Emergency Lighting Log").Range("M1").Value)
    
        TextBox1.SetFocus

    End If
    
    
End Sub

 

[rickblunt]

Hi Norie, thanks for trying to help me on this. I tried that and had no luck, even swapped the entire sub around to make it <>0 so the message box would be in the Else section (just in case) and had no luck. I just ran across an article about dimming the userform because it is an initialize event, but I have never dimmed a userform before. I guess a userform is an object? I tried that and had no luck anyway.... Its funny that it works fine in other types of subs....

So I am guessing that because it is an "initialize" event VBA prevents you from making it "terminate" the userform.

Basically all I am trying to do is that if the user calls this userform the very first time and there are no inspection to be done it just closes out right then and not continue on. I think i will have to figure out a different way of doing it, I need to keep the intialize event because of the lastrow syntax....

 

[Norie]

How are they 'calling' the userform in the first place?

 

[rickblunt]

I have a command button that I use to call for this userform. I normally do not need the initialize event, but in this case I have a textbox on it the userform that gives me a count on the number of remaining items that need to be inspected. I want the textbox to show the count value the first time that the userform is launched, otherwise it would just be a zero.

 

[rickblunt]

On my userform I have several buttons, but there are two in particular that uses this string; a PASS button and a FAIL button. I am using this form to inspect emergency lighting, so during the inspection I do not close out the form each time the sub is run, I just leave the form open until all of the devices have been inspected. On this form is a text box and a list box that shows the user which ones are still left to do and how many are left. Below is the sub for the PASS button, if the count gets to zero the messagebox pops up and the userform closes out just fine. no errors, no fuss.

I could run without the initialize event, but if I do the first time you launch the userform you would get a zero count and no devices in the listbox, until you cycled the sub for the first time.

In the overall scheme of things this is not a big deal, Currently when I omit it from the initialize event all the user has to do is manually close the userform when it pops up. This just might end up being a quirk with my workbook, but it would be nice to get rid of it so its all nice and clean

Here is the sub for the PASS button

Private Sub PassButton_Click()
 Dim Found As Range
 Dim i As Long
 Dim Lastrow As Long
 
'This string finds the device that you are inspecting on the WS, confirms that you have the correct one,
'that the bar code was read correctly, and enters in the inspection observations for that device.
    If Me.TextBox1.Value = "" Then
        MsgBox "Emergency Light ID was not read, rescan and try again. ", , "Rescan and try again"
    Else
    'this is from the DEVICE ID column
        Set Found = Sheets("Emergency Lighting Log").Range("L:L").Find(What:=Me.TextBox1.Value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
        If Found Is Nothing Then
            MsgBox "No match for " & Me.TextBox1.Value, , "No Match Found"
        Else
            Found.Offset(0, 7).Value = Me.TextBox2.Value
            Found.Offset(0, 3).Value = Now()
            Found.Offset(0, 8).Value = "PASS"
            
       
            If CheckBox1.Value = True Then
            Found.Offset(0, 4) = "X"
            End If
            If CheckBox2.Value = True Then
            Found.Offset(0, 5) = "X"
            End If
            If CheckBox3.Value = True Then
            Found.Offset(0, 6) = "X"
            
         End If
        End If
    End If
    
    CheckBox1.Value = False
    CheckBox2.Value = False
    CheckBox3.Value = False
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox1.SetFocus
    
       
 'This string gives me the remaining amount of devices left to be inspected.
    Me.TextBox4.Text = CStr(ThisWorkbook.Sheets("Emergency Lighting Log").Range("M1").Value)
    
 'This string gives me the device names of the remaining inspections.  This is from the DEVICE ID column.
    ListBox1.Clear
    Lastrow = Cells(Rows.Count, "L").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 15).Value = "" Then ListBox1.AddItem Cells(i, 12).Value
    Next i
        
  If ThisWorkbook.Sheets("Emergency Lighting Log").Range("M1").Value = 0 Then
    MsgBox " All emergency lighting has been inspected in this area, continue on to the next inspection area. "
    
    Unload DataEntryElGr
    EmergencyLightArea.Show
    
        
  End If
  
End Sub