Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to test sheet and workbook protect and unprotect status.
All,
Just wanted to share what finally came out of the guidance I received from this ng. Maybe this will help someone else. Feedback appreciated.: This macro can be run from personal.xls or your self-created MacroBook. For this macro, the user can select a workbook and the code generates a msgbox showing the sheetname and whether the sheet is protected or unprotected. Another msgbox is used to show the protected/ unprotected status of the target workbook. VR/ Lost Sub ProtectedStatus() Dim wks As Worksheet Dim myList As String NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If NewFN = False Then ' They pressed Cancel MsgBox "Stopping because you did not select a file" Exit Sub Else On Error GoTo Erro Workbooks.Open Filename:=NewFN End If Set oldbk = Workbooks.Open(Filename:=NewFN) result = "" For Each wks In ActiveWorkbook.Worksheets myList = myList + wks.Name myList = myList + " " + IIf(wks.ProtectContents, "is protected.", "is NOT PROTECTED!") + vbCr Next wks MsgBox myList X = False If ActiveWorkbook.ProtectWindows Then X = True If ActiveWorkbook.ProtectStructure Then X = True If X = False Then MsgBox "The workbook is NOT PROTECTED!" Else MsgBox "The workbook is protected." End If oldbk.Close savechanges:=False Erro: Select Case Err Case 0 MsgBox "Macro completed successfully (or was cancelled by user)." Case Else MsgBox "There is something wrong: " & Chr(10) & _ Err & ": " & Err.Description End Select Err.Clear End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to protect/unprotect a sheet using a macro with password | Excel Discussion (Misc queries) | |||
Can I use a Macro to unprotect a sheet in the workbook? | Excel Programming | |||
Unprotect and Protect sheet within macro | Excel Programming | |||
Unprotect and protect sheet in a macro | Excel Programming | |||
Protect/Unprotect Sheet from Macro | Excel Programming |