VBA: Run-Time Error 91 help

K1600

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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,363
Members
415,969
Latest member
Rey99

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