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

--
Message posted from http://www.ExcelForum.com

So
Application.Run "UserScreen"

should be before Screenupdating is set to false. You can reset
ScreenUpdating as often as you wish.


---
Message posted from http://www.ExcelForum.com/