Sheet selection on Autoclose
 

Hi all,

i was wondering whether if anyone can help me on this problem.

I have this macro that protects the spreadsheet at worksheet and workbook
level when i close spreadsheet, however, i discovered a problem when i have
mutiple spreadsheets opened together with this particular sheet and exiting
excel all together at once. The macro instead of targeting and running the
macro for the worksheet it opened with, will just work for top-most opened
spreadsheet which i am closing from. I do not want to hardcode the name of
the spreadsheet into the macro as this is a generic macros that i use for a
lot of my spreadsheets and my spreadsheet will be renamed from time to time.

Would really appreciate any help that can be given. Thanks

-----------------------------------------------------------------------------------------------
Sub Auto_close()

Application.DisplayAlerts = False

On Error Resume Next
lastSheet = ActiveWorkbook.Sheets.Count
For curSheet = 1 To lastSheet
Application.Sheets(curSheet).Activate
ActiveSheet.Protect ("12345")
Next
Sheets("Main").Select
Range("A1").Select
ActiveWorkbook.Protect ("12345")

End Sub

----------------------------------------------------------------------------------------------

Rgds
Ray

Sheet selection on Autoclose
 

I think I'd tweak it this way

Dim myWS as Excel.Worksheet

For each myWS in ThisWorkbook.Worksheets
myws.protect("12345")
next myWS
--
HTH,

Barb Reinhardt



"swiftcode" wrote:

Hi all,

i was wondering whether if anyone can help me on this problem.

I have this macro that protects the spreadsheet at worksheet and workbook
level when i close spreadsheet, however, i discovered a problem when i have
mutiple spreadsheets opened together with this particular sheet and exiting
excel all together at once. The macro instead of targeting and running the
macro for the worksheet it opened with, will just work for top-most opened
spreadsheet which i am closing from. I do not want to hardcode the name of
the spreadsheet into the macro as this is a generic macros that i use for a
lot of my spreadsheets and my spreadsheet will be renamed from time to time.

Would really appreciate any help that can be given. Thanks

-----------------------------------------------------------------------------------------------
Sub Auto_close()

Application.DisplayAlerts = False

On Error Resume Next
lastSheet = ActiveWorkbook.Sheets.Count
For curSheet = 1 To lastSheet
Application.Sheets(curSheet).Activate
ActiveSheet.Protect ("12345")
Next
Sheets("Main").Select
Range("A1").Select
ActiveWorkbook.Protect ("12345")

End Sub

----------------------------------------------------------------------------------------------

Rgds
Ray

Sheet selection on Autoclose
 

Hi Barb,

It works! Thanks

Rgds
Ray

"Barb Reinhardt" wrote:

I think I'd tweak it this way

Dim myWS as Excel.Worksheet

For each myWS in ThisWorkbook.Worksheets
myws.protect("12345")
next myWS
--
HTH,

Barb Reinhardt



"swiftcode" wrote:

Hi all,

i was wondering whether if anyone can help me on this problem.

I have this macro that protects the spreadsheet at worksheet and workbook
level when i close spreadsheet, however, i discovered a problem when i have
mutiple spreadsheets opened together with this particular sheet and exiting
excel all together at once. The macro instead of targeting and running the
macro for the worksheet it opened with, will just work for top-most opened
spreadsheet which i am closing from. I do not want to hardcode the name of
the spreadsheet into the macro as this is a generic macros that i use for a
lot of my spreadsheets and my spreadsheet will be renamed from time to time.

Would really appreciate any help that can be given. Thanks

-----------------------------------------------------------------------------------------------
Sub Auto_close()

Application.DisplayAlerts = False

On Error Resume Next
lastSheet = ActiveWorkbook.Sheets.Count
For curSheet = 1 To lastSheet
Application.Sheets(curSheet).Activate
ActiveSheet.Protect ("12345")
Next
Sheets("Main").Select
Range("A1").Select
ActiveWorkbook.Protect ("12345")

End Sub

----------------------------------------------------------------------------------------------

Rgds
Ray