Re: "Not enough system resources to display completely"
Hi ade333
In order to help you, I have prepared a sample EXCEL workbook (see below).
Code explanation
1 - Create a workbook with two sheets:
MainSheet - Is the offending sheet, the one that contains controls or zoom <> 100%
DummySheet - A temporary which temporarily activated during save and load operations, saying, for example: "Please, wait..."
2 - On Workbook.
BeforeSave event:
1 - Save current active sheet;
2 - Activate DummySheet
3 - On Workbook.
AfterSave event:
1 - Restore current active sheet
4 - On Workook.
Open event:
1 - Activate workbook MainSheet
What you will get
Now, everytime you save the workbook:
1 - DummySheet is activated;
2 - The workbook is saved;
3 - Current (prior to saving) active sheet is reactivated.
Note: If saving is too fast, alternation between sheet is not noticiable. Therefore, I have added a small delay so you can see that DummySheet is being temporarily activated.
And everytime you load the workbook:
1 - DummySheet is shown;
2 - MainSheet is activated.
This my sample workbook source code
<CODE>Option Explicit
Dim CurrentSheet As Worksheet
Const MainSheetName = "MainSheet"
Const DummySheetName = "DummySheet"
Private Sub Workbook_Open()
ActivateSheetByName MainSheetName
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
SaveCurrentSheet
ActivateDummySheet
End Sub
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
RestoreCurrentSheet
End Sub
Private Sub RestoreCurrentSheet()
If Not (CurrentSheet Is Nothing) Then ' Restore to current sheet prior to saving
CurrentSheet.Activate
End If
Set CurrentSheet = Nothing
End Sub
Private Sub SaveCurrentSheet()
Set CurrentSheet = ActiveSheet ' Active sheet will be restored after save
End Sub
Private Sub ActivateDummySheet()
ActivateSheetByName DummySheetName ' Activate dummy sheet during save
Sleep 2 ' Wait for 2 seconds just to let you see that active sheet is temporarily DummySheet
End Sub
Private Sub ActivateSheetByName(ByVal SheetName As String)
Dim Sheet As Worksheet
Set Sheet = ThisWorkbook.Sheets(SheetName)
Sheet.Activate
End Sub
Private Sub Sleep(ByVal Seconds As Integer)
Application.Wait DateAdd("s", Seconds, Now)
End Sub
</CODE><CODE></CODE>
<CODE>Sample workbook available at http://sisgarbe.dyndns.org/sispub/download/obter.php?produto=JULIO-EXCEL-BUG01-SAMPLE</CODE>
<CODE></CODE>
<CODE></CODE>
<CODE>To see source code on sample workbook, just press ALT + F11</CODE>
<CODE>Hope this helps </CODE>
<CODE></CODE>