VBA: Run-Time Error 91 help

K1600

Board Regular
Joined
Oct 20, 2017
Messages
153
I have a file that when it loads I have this code to force the 'Admin Login' UserForm to load.

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, "Admin Dashboard"
    
    End If

'Loads UserForm immediatly on file being opened and centres it 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 vbModal 'vbModal stops access to spreadsheet in background where as vbModeLess allows it to still be accessed
    End With

End Sub

This works fine and without issue when it first loads. The issue comes when I run a report which is part of the macro's in the workbook which at the end it closes the UserForms and reverts to a worksheet named 'Charts' in the workbook which now will display the results of the report. However, it then gives me a Run-Time Error 91 with "the specified dimension is not valid for the current chart type" as the error. When I De-Bug this it takes me to the UsrFrmAdminLogin.Show vbModal line on the Workbook_Open sub. I have had this working without issue previously but I have clearly changed something somewhere whilst trying to do an update and can't work out where I am going wrong.

The code for running the report is rather long winded but it ends with this:

VBA Code:
'Copy Column A with 'paste special' of 'values' to prevent formulas causing issues with count
    Range("A:A").Copy
    Cells(1, 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False     'Gets rid of selection from copy function
    Range("A1").Select

MsgBox "The report has completed and is now available to view", vbInformation, "Admin Dashboard"

Application.Visible = True
Sheets("Charts").Select
Unload UsrFrmAdminLogin
End Sub

Any help or ideas would be gratefully received!

Thanks.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,718
Members
414,013
Latest member
tnobbs

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