A
alistair01
I am working with an excel spreadsheet which i want to execute macros o
opening. i have tried to put the code in the workbook open event but th
user screen maco is not executed.
when i remove the screenupdating the userscreen macro works, howeve
this is not suitable as the report macro is quite long and cycle
through a number of sheets. This problem also occurs wit
displayalerts=false
Please see the code below.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Application.Run "UserScreen"
Application.Run "Report"
End Sub
User screen is as folows:
Sheets("Alarm Analysis").Select
Application.Run "Set_User_Screen"
Application.Run "Background"
this is repeated for 9 other sheets
This is the report macro, the main function of the spreadsheet.
Sub Report()
Sheets("Alarm Analysis").Activate
ActiveWorkbook.RefreshAll
Sheets("Main Menu").Select
Dim DataWorkbook As Workbook
Dim SaveFileName As String
If MsgBox("Do you want to create a Report?", vbQuestion + vbYesNo
= vbYes Then
Application.Run "Info"
Set DataWorkbook = ActiveWorkbook
Sheets(Array("Alarm Analysis", "Alarm Type-Door", "Main Menu"
"Clearance Times", "Alarm Type-Intruder", _
"Alarm Type-Fire", "Alarm Type-Panic"
"Clearance-Incident Acknowledged", _
"Clearance-False Alarm", "Clearance-System Test"
"Clearance-User Input", _
"Alarm Activations", "Programs Activated", "Ful
Report", "AlarmClearancesC", "AlarmclTimeC", "AlarmtypeC"
"AlarmClearanceC", "ProgramActivationsC")).Copy
SaveFileName = "C:\Documents an
Settings\ajohnston01.VMSGLAS.000\My Documents\Report_" & format(Now
"dd_mm_yyyy") & ".xls"
ActiveWorkbook.saveas Filename:=SaveFileName
MsgBox "New Report Created and Saved As " & SaveFileName
'Application.Run ("ActiveWorkbook_Zip_Mail")
If MsgBox("Would you like to view the current report?"
vbQuestion + vbYesNo) = vbNo Then
ActiveWorkbook.Close
Workbooks("Titan Vision.xls").Activate
Application.Run "Clearall"
Application.Run "clearClearanceTimes"
Sheets("Main Menu").Select
End If
Else
Workbooks("Titan Vision.xls").Activate
ActiveWorkbook.Close
Sheets("Main Menu").Select
End If
Application.Run ("UserScreen")
Sheets("Main Menu").Activate
End Sub
Set user screen removes all commandbars and column and row headings.
Background selects a image to be used in each sheet.
Any help at all would be great
opening. i have tried to put the code in the workbook open event but th
user screen maco is not executed.
when i remove the screenupdating the userscreen macro works, howeve
this is not suitable as the report macro is quite long and cycle
through a number of sheets. This problem also occurs wit
displayalerts=false
Please see the code below.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Application.Run "UserScreen"
Application.Run "Report"
End Sub
User screen is as folows:
Sheets("Alarm Analysis").Select
Application.Run "Set_User_Screen"
Application.Run "Background"
this is repeated for 9 other sheets
This is the report macro, the main function of the spreadsheet.
Sub Report()
Sheets("Alarm Analysis").Activate
ActiveWorkbook.RefreshAll
Sheets("Main Menu").Select
Dim DataWorkbook As Workbook
Dim SaveFileName As String
If MsgBox("Do you want to create a Report?", vbQuestion + vbYesNo
= vbYes Then
Application.Run "Info"
Set DataWorkbook = ActiveWorkbook
Sheets(Array("Alarm Analysis", "Alarm Type-Door", "Main Menu"
"Clearance Times", "Alarm Type-Intruder", _
"Alarm Type-Fire", "Alarm Type-Panic"
"Clearance-Incident Acknowledged", _
"Clearance-False Alarm", "Clearance-System Test"
"Clearance-User Input", _
"Alarm Activations", "Programs Activated", "Ful
Report", "AlarmClearancesC", "AlarmclTimeC", "AlarmtypeC"
"AlarmClearanceC", "ProgramActivationsC")).Copy
SaveFileName = "C:\Documents an
Settings\ajohnston01.VMSGLAS.000\My Documents\Report_" & format(Now
"dd_mm_yyyy") & ".xls"
ActiveWorkbook.saveas Filename:=SaveFileName
MsgBox "New Report Created and Saved As " & SaveFileName
'Application.Run ("ActiveWorkbook_Zip_Mail")
If MsgBox("Would you like to view the current report?"
vbQuestion + vbYesNo) = vbNo Then
ActiveWorkbook.Close
Workbooks("Titan Vision.xls").Activate
Application.Run "Clearall"
Application.Run "clearClearanceTimes"
Sheets("Main Menu").Select
End If
Else
Workbooks("Titan Vision.xls").Activate
ActiveWorkbook.Close
Sheets("Main Menu").Select
End If
Application.Run ("UserScreen")
Sheets("Main Menu").Activate
End Sub
Set user screen removes all commandbars and column and row headings.
Background selects a image to be used in each sheet.
Any help at all would be great