help with 91 error

R

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
609
Office Version
  1. 2019
Platform
  1. Windows
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,


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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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.
Code: 
Unload Me
 
Upvote 0
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.
 
Upvote 0
Uh oh - still getting that error even after changing the string to Unload Me.....
 
Upvote 0
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
 
Upvote 0
Which userform is this code for?

What happens if you swap the Show and Unload around?
Code: 
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
 
Upvote 0
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....
 
Upvote 0
How are they 'calling' the userform in the first place?
 
Upvote 0
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.
 
Upvote 0
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

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Error 1004
2
Replies
18
Views
402
dmt32
D
K
  • Question Question
Help Needed
2
Replies
13
Views
334
kwavic
K
T
  • Solved
Populate Worksheet Rows Based on Userform Checkbox selection
2
Replies
11
Views
179
TellM1955
T
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
266
chazrab
C
A
  • Question Question
Modifying code for message box on userform based on selected combobox
Replies
0
Views
42
Ali M
A

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top