Run-Time Error 91 - Object variable or With block variable not set

K1600

Board Regular
Joined
Oct 20, 2017
Messages
70
I've got a file that when it opens it does a Application.Visible = False and then loads my Login UserForm allowing the user to login.

Once the user has logged in the login UserForm hides rather than unloads as the other UserForms pick information from it during the rest of the process. I am having an issue that once I have run a report using coding Application.Visible = True is added to allow the user to then see the data that has been obtained from the report however when after excel is visible again the login UserForm is re-appearing on the screen. If I add a line of code to unload it then I get the above Run-Time Error. If I DeBug it, it takes me to the line with UsrFrmAdminLogin.Show vbModal.

I can also replicate the error by clicking the red 'X' on the Login UserForm before logging in, which leads me to think that the issue is with this rather then somewhere else in my code.

The code I am using on the workbook opening is below. Could this be something to do with me not being able to pre-approve the macro's and as such having to choose "Enable" once the document opens?

Thanks in advance for any help.

VBA Code:
Private Sub Workbook_Open()
    

'Hide excel after launch so only userform displays
    Application.Visible = False             'Add this to hide excel when UserForm loads

'Checks if outlook is open
Dim oOutlook As Object

    On Error Resume Next
    Set oOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0

    If oOutlook Is Nothing Then
        MsgBox "Please open Microsoft Outlook (e-mail application)." & vbCr & _
            "      You will need this open to use this system", vbExclamation
    End If

'Loads UserForm immediatly on file being opened
    UsrFrmAdminLogin.Show vbModal


End Sub
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

K1600

Board Regular
Joined
Oct 20, 2017
Messages
70
Is there code in your userform's Initialize event?
Yes, but only this.....

VBA Code:
Private Sub UserForm_Initialize()

'Centres UserForm on screen
    With UsrFrmAdminLogin
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With

End Sub
 
L

Legacy 456155

Guest
I'm thinking...

With Me
instead of
With UsrFrmAdminLogin

VBA Code:
Private Sub UserForm_Initialize()

'Centres UserForm on screen
    With Me
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With

End Sub
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
70

ADVERTISEMENT

I'm thinking...

With Me
instead of
With UsrFrmAdminLogin

VBA Code:
Private Sub UserForm_Initialize()

'Centres UserForm on screen
    With Me
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With

End Sub
No, that gives me the same again and debug takes me back to UsrFrmAdminLogin.Show vbModal.
 
L

Legacy 456155

Guest
Have you compiled your project? Any compile time errors?
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
70

ADVERTISEMENT

Have you compiled your project? Any compile time errors?
Sorry I am rather new to this world so not sure what compiling is however this seems to be the only error that I am getting across the whole setup. Please, pardon my ignorance.
 
L

Legacy 456155

Guest
That's ok. From your VBA editor, select the 'Debug' menu option and then click on 'Compile'. Tell me what happens then.
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
70
That's ok. From your VBA editor, select the 'Debug' menu option and then click on 'Compile'. Tell me what happens then.
When I go into the Debug menu 'Compile VBAProject' is available to click. I click it, it greys out but nothing else changes. Have I done something wrong?
 
L

Legacy 456155

Guest
No. If it greys out, that means that the project compiled without any compile errors. That's good news! :)

No problems are apparent to me from what you have disclosed. Please post all of your code or provide a link , if possible, so that I can take a look at your workbook.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,836
Messages
5,544,596
Members
410,624
Latest member
smartsanjiv64
Top