|
after save event/procedure
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 -- kev_0 ----------------------------------------------------------------------- kev_06's Profile: http://www.excelforum.com/member.php...fo&userid=3504 View this thread: http://www.excelforum.com/showthread.php?threadid=55007 |
after save event/procedure
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" wrote in message ... Is there any kind of method/event/procedure I can use so that I can make a sheet appear directly AFTER saving the book? I've trieed manipulating the beforesave event but didn't get the results I wanted. -- kev_06 ------------------------------------------------------------------------ kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046 View this thread: http://www.excelforum.com/showthread...hreadid=550078 |
after save event/procedure
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 -- kev_0 ----------------------------------------------------------------------- kev_06's Profile: http://www.excelforum.com/member.php...fo&userid=3504 View this thread: http://www.excelforum.com/showthread.php?threadid=55007 |
after save event/procedure
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 "kev_06" wrote in message ... Since most of the people that view this workbook may not enable their 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 want 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 are disabled. So, I'm trying to write something that will save the workbook in its initial state and then return to the sheet after the save has been completed. The code: Public strname As String Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As 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 Sub -- kev_06 ------------------------------------------------------------------------ kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046 View this thread: http://www.excelforum.com/showthread...hreadid=550078 |
after save event/procedure
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" wrote in message ... Since most of the people that view this workbook may not enable their 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 want 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 are disabled. So, I'm trying to write something that will save the workbook in its initial state and then return to the sheet after the save has been completed. The code: Public strname As String Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As 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 Sub -- kev_06 ------------------------------------------------------------------------ kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046 View this thread: http://www.excelforum.com/showthread...hreadid=550078 |
after save event/procedure
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. -- kev_06 ------------------------------------------------------------------------ kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046 View this thread: http://www.excelforum.com/showthread...hreadid=550078 |
after save event/procedure
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 "kev_06" a écrit dans le message de news: ... 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. -- kev_06 ------------------------------------------------------------------------ kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046 View this thread: http://www.excelforum.com/showthread...hreadid=550078 |
after save event/procedure
Did what Ardus gave you take care of it? Post back if you need more. Otto
"kev_06" wrote in message ... 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. -- kev_06 ------------------------------------------------------------------------ kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046 View this thread: http://www.excelforum.com/showthread...hreadid=550078 |
| All times are GMT +1. The time now is 06:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com