I have a file that when it loads I have this code to force the 'Admin Login' UserForm to load.
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
The code for running the report is rather long winded but it ends with this:
Any help or ideas would be gratefully received!
Thanks.
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.