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

K1600

Board Regular
Joined
Oct 20, 2017
Messages
178
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

K1600

Board Regular
Joined
Oct 20, 2017
Messages
178
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
178

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
178

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
178
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,819
Messages
5,772,461
Members
425,760
Latest member
paphon

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
Top