+ Reply to Thread
Results 1 to 3 of 3

exit a form getting "Runtime Error 91: Object variable or with block variable not set"

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    exit a form getting "Runtime Error 91: Object variable or with block variable not set"

    I have a bit of code I have used for years and recently under a slightly different application we have a bug. in the following code, if cell B4 = "z, row" we need to display the message box and close the form. however, regardless us what I try to Exit Sub I get "Runtime Error 91: Object variable or with block variable not set"

    Private Sub UserForm_Initialize()
    Dim lastrow As Integer
    Dim frmTop As Integer
    Dim cbnOfSt As Integer
    Dim varAnswer As String
    
    'bail out code
     If Range("B4") = "z, row" Then
        varAnswer = MsgBox("This worksheet has only one employee. A new employee must be added before the current employee can be removed", _
        vbOK, "Minimum Employee Warning")
        
            If varAnswer = vbOK Then
                    Unload frmRemEmpl, some results with "unload me" 
                    Exit Sub ' RESULTS IN --->"Runtime Error 91: Object variable or with block variable not set"
            End If
        Else
     End If
    
    'form code here
    
    End Sub
    I know this is something obvious but not obvious to me. please help
    Robert

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: exit a form getting "Runtime Error 91: Object variable or with block variable not set"

    You cannot unload the userform from within the UserForm_Initialize procedure.

    You could...
    1.) check condition before you initialize the userform

    2.) or check the condition in the UserForm_Activate event. The _Activate event is called after the form has been initialized e.g....

    Private Sub UserForm_Activate()
    
     If Range("B4") = "z, row" Then
        If MsgBox("This worksheet has only one employee. A new employee must be added before the current employee can be removed", _
           vbOK, "Minimum Employee Warning") = vbOK Then Unload Me
     End If
    End Sub
    
    Private Sub UserForm_Initialize()
    
    'form code here
    
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: exit a form getting "Runtime Error 91: Object variable or with block variable not set"

    Thanks AlphaFrog! that works perfect.

    Quote Originally Posted by AlphaFrog View Post
    You cannot unload the userform from within the UserForm_Initialize procedure.

    You could...
    1.) check condition before you initialize the userform

    2.) or check the condition in the UserForm_Activate event. The _Activate event is called after the form has been initialized e.g....

    Private Sub UserForm_Activate()
    
     If Range("B4") = "z, row" Then
        If MsgBox("This worksheet has only one employee. A new employee must be added before the current employee can be removed", _
           vbOK, "Minimum Employee Warning") = vbOK Then Unload Me
     End If
    End Sub
    
    Private Sub UserForm_Initialize()
    
    'form code here
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Getting "Object variable or With block variable not set" error with Find function
    By enphynity in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2013, 05:47 PM
  2. [SOLVED] Runtime Error "91" - Object Variable or With Block Variable not set
    By damidre in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-05-2013, 04:45 AM
  3. Getting an "Object variable or With block variable not set" error on this script
    By Lilivati in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-11-2012, 06:20 PM
  4. Replies: 1
    Last Post: 09-07-2012, 12:08 AM
  5. The pesky "Object variable or With block variable not set" error
    By pjmulero in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2012, 05:58 PM
  6. Error msgs: "Object varible or with block variable not set"; "subscript out of range"
    By menyanthe in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-26-2009, 04:58 PM
  7. Keep getting "Object Variable or With block variable not set" error with macro
    By MSFSC in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-17-2009, 09:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1