Hello All,
I found this small function that checks if a worksheet is protected or not ...
... and it works OK, but there is one issue.
If say in cell B6 I enter this statement ... =IsSheetProtected("Financial Data")
then the value of B6 is either TRUE (sheet is protected) or FALSE (sheet is not protected).
However, if I right click on the sheet tab to change the protection of the sheet, the value cell B6 does not update unless I click on another cell in the sheet.
My objective is to have a cell on each sheet in the workbook to indicate the protection status of that sheet. Ideally, I would like a way to do this without referencing a
specific sheet name. For example, a function that would take the current sheet name or sheet number and test the protection status. Then update the cell with an
indicator of the protection status. And if the protection status is changed, to update the cell without having to activate another sheet and then come back or select a
different cell in the worksheet.
Thanks for any help,
Steve
I found this small function that checks if a worksheet is protected or not ...
VBA Code:
Function IsSheetProtected(s As String) As Boolean
' The value s is passed as the worksheet name
Application.Volatile
IsSheetProtected = Sheets(s).ProtectContents
End Function
... and it works OK, but there is one issue.
If say in cell B6 I enter this statement ... =IsSheetProtected("Financial Data")
then the value of B6 is either TRUE (sheet is protected) or FALSE (sheet is not protected).
However, if I right click on the sheet tab to change the protection of the sheet, the value cell B6 does not update unless I click on another cell in the sheet.
My objective is to have a cell on each sheet in the workbook to indicate the protection status of that sheet. Ideally, I would like a way to do this without referencing a
specific sheet name. For example, a function that would take the current sheet name or sheet number and test the protection status. Then update the cell with an
indicator of the protection status. And if the protection status is changed, to update the cell without having to activate another sheet and then come back or select a
different cell in the worksheet.
Thanks for any help,
Steve