after save event/procedure

[kev_06]

Is there any kind of method/event/procedure I can use so that I can mak
a sheet appear directly AFTER saving the book? I've trieed manipulatin
the beforesave event but didn't get the results I wanted

 

[Otto Moehrbach]

I don't understand the significance of selecting the desired sheet before or
after the save. If that doesn't matter, then put a statement like:
Sheets("SheetName").Select
in the Before_Save macro. The file will be saved and that sheet will be the
active sheet.
If it is significant that the sheet selection take place after the save,
then use the same Before_Save macro, set Cancel to True at the start of the
macro, then save the file, then set the save flag to True, then select the
sheet. Something like:
Cancel = True
Application.EnableEvents = False
ThisWorkbook.Save
ThisWorkbook.Saved = True
Application.EnableEvents = True
Sheets("SheetName").Select
Post back with more detail if this doesn't work for you. HTH Otto

 

[kev_06]

Since most of the people that view this workbook may not enable thei
macros at start, the workbook doesn't do anything. I've added a sheet
Sheet 1, that explains this and tells them to enable macros. I wan
this sheet to be the first one displayed whenever the workbook is open
This is fine when macros are enabled, but it isn't when they ar
disabled. So, I'm trying to write something that will save the workboo
in its initial state and then return to the sheet after the save ha
been completed.

The code:

Public strname As String

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean)
strname = ThisWorkbook.ActiveSheet.Name

Cancel = True
Application.EnableEvents = False

Sheet4.Visible = xlSheetHidden
Sheet1.Visible = xlSheetVisible

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Protect
wks.EnableSelection = xlNoSelection
Next wks

ThisWorkbook.Save
ThisWorkbook.Saved = True
Application.EnableEvents = True

Sheet1.Select
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If strname = "" Then
Exit Sub
ElseIf Worksheets(strname).Name = "Sheet1" Then
Exit Sub
Else
Worksheets(strname).Select
strname = ""
End If
End Su

 

[Otto Moehrbach]

I don't understand what you are doing. If a sheet is selected after the
file is saved, that sheet will not be the active sheet when the file is
again opened. I think I am missing what your intent is. No macro will run
if the file is opened with macros disabled. Are you trying to force the
user to open the file with macros enabled? Please post back and try your
explanation again. Provide a step-by-step of what you want to happen. Otto

 

[Otto Moehrbach]

What do mean by "I'm trying to write something that will save the workbook
in its initial state." What is the initial state? Otto

 

[kev_06]

Thanks for your patience.

Here's what I'm trying to do:

When the workbook opens, I want sheet1 to be visible and sheet4 to be
hidden. When the macros are enabled, I can use a workbook_open event.
The problem is when the macros are disabled. When the macros are
disabled, I want sheet1 to be visible because it is an information
sheet telling the users that the macros are disabled and to lock all
the other sheets so that no changes can be made to them (many
calculations on the sheets are done with macros). I also want sheet4 to
be hidden. I know there's a warning about macros being disabled when the
workbook opens, but many of the end users do not take the time to read
through and understand that the workbook will not function correctly.

Therefore, I could use some kind of workbook_beforeclose event, but I
would have to save the changes. What if the user does not want to save
changes on close?

So, I figure if I could use a beforesave event, then I could make
sheet1 visible and sheet4 hidden so that when another user opens the
workbook with macros disabled, the first thing they see is the
information sheet. But, if the user was doing something else on another
sheet at the time of save, then
they would have to go back and unhide sheet4. It would be a hassle for
the end user.

What I'm trying to do with workbook_beforesave is:

-save the current sheet name
-unhide sheet1
-hide sheet4
-save the workbook

After save (Return to the sheet the user was working on):

-return to the sheet the user was working on
-hide sheet1
-unhide sheet4

I'm hiding sheet4 because it's run entirely on macros. It manages all
the sheets. The user can set up new spreadsheets through different
templates and delete, print, export, or e-mail existing sheets. If
macros are disabled, then this sheet is useless and ultimately confuses
the end users.

 

[Ardus Petus]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Worksheets("Sheet1").Visible = True
Worksheets("Sheet4").Visible = False
Me.Save
Worksheets("Sheet1").Visible = False
Worksheets("Sheet4").Visible = True
Cancel = True
End Sub

HTH

 

[Ardus Petus]

Oooops! (I had not tested my code)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Worksheets("Sheet1").Visible = True
Worksheets("Sheet4").Visible = False
Application.EnableEvents = False
Me.Save
Application.EnableEvents = True
Worksheets("Sheet4").Visible = True
Worksheets("Sheet1").Visible = False
Cancel = True
End Sub

 

[Otto Moehrbach]

Did what Ardus gave you take care of it? Post back if you need more. Otto