Sheet Protection Identifier

[CLR]

Hi All.......

I regularly work on WorkBooks that require some of the sheets to be
Protected, and some not. Naturally while I'm editing them, I must turn the
Protection off. Once I get ready to save the WorkBook, I have to check each
sheet individually to see if it's protected or not..
Is it possible to somehow indicate on a Sheet Tab, whether or not that
sheet is Protected?. Maybe change tab color, or add an * to the Sheetname,
or something?

Any help would be appreciated.....

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

Only by running code - so if your going to run code anyway, it might be just
as easy to run a routine to do the checking (and correcting).

 

[JE McGimpsey]

One way:

Put this in you Personal.xls workbook and attach it to a toolbar button:

Public Sub ToggleProtectWithIndication()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet

With ActiveSheet
If .ProtectContents Then
.Unprotect Password:=PWORD
.Name = .Name & "##"
Else
.Protect Password:=PWORD
If .Name Like "*[##]" Then _
.Name = Left(.Name, Len(.Name) - 2)
End If
End With
End Sub

This will add "##" when you unprotect the sheet, and remove it when you
reprotect it.

 

[CLR]

Ok Tom, thanks..........

Only by running code - so if your going to run code anyway, it might be just
as easy to run a routine to do the checking (and correcting).

 

[CLR]

Thanks JE.........that's a great start.........I first mistakenly put it in
"ThisWorkbook" and it didn't seem to work, but then I moved it to a regular
module and it works "fine as frog hair".........I just need to modify it now
to accomodate my 8 sheets, and whether I want them on or off protection.

Thanks again
Vaya con Dios,
Chuck, CABGx3

One way:

Put this in you Personal.xls workbook and attach it to a toolbar button:

Public Sub ToggleProtectWithIndication()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet

With ActiveSheet
If .ProtectContents Then
.Unprotect Password:=PWORD
.Name = .Name & "##"
Else
.Protect Password:=PWORD
If .Name Like "*[##]" Then _
.Name = Left(.Name, Len(.Name) - 2)
End If
End With
End Sub

This will add "##" when you unprotect the sheet, and remove it when you
reprotect it.

Hi All.......

I regularly work on WorkBooks that require some of the sheets to be
Protected, and some not. Naturally while I'm editing them, I must turn the
Protection off. Once I get ready to save the WorkBook, I have to check each
sheet individually to see if it's protected or not..
Is it possible to somehow indicate on a Sheet Tab, whether or not that
sheet is Protected?. Maybe change tab color, or add an * to the Sheetname,
or something?

Any help would be appreciated.....

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

Missing the punch line here. you are going to handle each sheet
individually and each time it is changed?

--
Regards,
Tom Ogilvy

Thanks JE.........that's a great start.........I first mistakenly put it in
"ThisWorkbook" and it didn't seem to work, but then I moved it to a regular
module and it works "fine as frog hair".........I just need to modify it now
to accomodate my 8 sheets, and whether I want them on or off protection.

Thanks again
Vaya con Dios,
Chuck, CABGx3

One way:

Put this in you Personal.xls workbook and attach it to a toolbar button:

Public Sub ToggleProtectWithIndication()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet

With ActiveSheet
If .ProtectContents Then
.Unprotect Password:=PWORD
.Name = .Name & "##"
Else
.Protect Password:=PWORD
If .Name Like "*[##]" Then _
.Name = Left(.Name, Len(.Name) - 2)
End If
End With
End Sub

This will add "##" when you unprotect the sheet, and remove it when you
reprotect it.

Hi All.......

I regularly work on WorkBooks that require some of the sheets to be
Protected, and some not. Naturally while I'm editing them, I must

turn

the

 

[CLR]

Naw, that's not really what I'm looking for..............I started out
wanting to look at a workbook and see all the 8 tabs as they should normally
appear when all Protection and Non-Protection was properly in
place...........then, if during my editing I have to Un-protect a sheet by
hand (or accidently Protect one that should be un-protected), that sheets
tab would display some symbol or special color to alarm me that the setting
for that sheet was not proper for saving.............then I could fire a
macro and all protection would be properly in place, whether it already was,
or not, not just a toggle.........It still would be nice if that was a
feature that whenever a sheet was Protected, it's tab would have a border or
underlined name or something to so indicate, huh?

But, maybe after thinking about your statement........all that might really
be needed is just a Before-Save macro to set all 8 Protections to their
proper settings........

So, with yours and JE's help, I think I see what can be done and I think I
understand what I want now..........it's just a matter of getting out my ole
trusty Macro-Recorder and getting to work..............thanks again to both
of you.............

Vaya con Dios,
Chuck, CABGx3

Missing the punch line here. you are going to handle each sheet
individually and each time it is changed?

--
Regards,
Tom Ogilvy

Thanks JE.........that's a great start.........I first mistakenly put it in
"ThisWorkbook" and it didn't seem to work, but then I moved it to a regular
module and it works "fine as frog hair".........I just need to modify it now
to accomodate my 8 sheets, and whether I want them on or off protection.

Thanks again
Vaya con Dios,
Chuck, CABGx3

One way:

Put this in you Personal.xls workbook and attach it to a toolbar button:

Public Sub ToggleProtectWithIndication()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet

With ActiveSheet
If .ProtectContents Then
.Unprotect Password:=PWORD
.Name = .Name & "##"
Else
.Protect Password:=PWORD
If .Name Like "*[##]" Then _
.Name = Left(.Name, Len(.Name) - 2)
End If
End With
End Sub

This will add "##" when you unprotect the sheet, and remove it when you
reprotect it.



Hi All.......

I regularly work on WorkBooks that require some of the sheets to be
Protected, and some not. Naturally while I'm editing them, I must

turn

the
Protection off. Once I get ready to save the WorkBook, I have to

check
each

sheet individually to see if it's protected or not..
Is it possible to somehow indicate on a Sheet Tab, whether or not that
sheet is Protected?. Maybe change tab color, or add an * to the Sheetname,
or something?

Any help would be appreciated.....

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

all that might really

be needed is just a Before-Save [or beforeclose with a save included] macro

to set all 8 Protections to their

proper settings........

That would be my thought. No need for an alert, just makes sure it is done
whether needed or not. No argument that a visual cue that a sheet is
protected wouldn't be useful as a built in feature.

--
Regards,
Tom Ogilvy

Naw, that's not really what I'm looking for..............I started out
wanting to look at a workbook and see all the 8 tabs as they should normally
appear when all Protection and Non-Protection was properly in
place...........then, if during my editing I have to Un-protect a sheet by
hand (or accidently Protect one that should be un-protected), that sheets
tab would display some symbol or special color to alarm me that the setting
for that sheet was not proper for saving.............then I could fire a
macro and all protection would be properly in place, whether it already was,
or not, not just a toggle.........It still would be nice if that was a
feature that whenever a sheet was Protected, it's tab would have a border or
underlined name or something to so indicate, huh?

But, maybe after thinking about your statement........all that might really
be needed is just a Before-Save macro to set all 8 Protections to their
proper settings........

So, with yours and JE's help, I think I see what can be done and I think I
understand what I want now..........it's just a matter of getting out my ole
trusty Macro-Recorder and getting to work..............thanks again to both
of you.............

Vaya con Dios,
Chuck, CABGx3

Missing the punch line here. you are going to handle each sheet
individually and each time it is changed?

--
Regards,
Tom Ogilvy

Thanks JE.........that's a great start.........I first mistakenly put

it
in

"ThisWorkbook" and it didn't seem to work, but then I moved it to a regular
module and it works "fine as frog hair".........I just need to modify

it
now

to accomodate my 8 sheets, and whether I want them on or off protection.

Thanks again
Vaya con Dios,
Chuck, CABGx3



One way:

Put this in you Personal.xls workbook and attach it to a toolbar button:

Public Sub ToggleProtectWithIndication()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet

With ActiveSheet
If .ProtectContents Then
.Unprotect Password:=PWORD
.Name = .Name & "##"
Else
.Protect Password:=PWORD
If .Name Like "*[##]" Then _
.Name = Left(.Name, Len(.Name) - 2)
End If
End With
End Sub

This will add "##" when you unprotect the sheet, and remove it when you
reprotect it.



Hi All.......

I regularly work on WorkBooks that require some of the sheets to be
Protected, and some not. Naturally while I'm editing them, I must turn
the
Protection off. Once I get ready to save the WorkBook, I have to check
each
sheet individually to see if it's protected or not..
Is it possible to somehow indicate on a Sheet Tab, whether or not that
sheet is Protected?. Maybe change tab color, or add an * to the
Sheetname,
or something?

Any help would be appreciated.....

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks for the comeback Tom..........It's comforting for me to know that our
thinking coincides........have a great one.........

Vaya con Dios,
Chuck, CABGx3

all that might really
be needed is just a Before-Save [or beforeclose with a save included]

macro
to set all 8 Protections to their

proper settings........

That would be my thought. No need for an alert, just makes sure it is done
whether needed or not. No argument that a visual cue that a sheet is
protected wouldn't be useful as a built in feature.

--
Regards,
Tom Ogilvy

Naw, that's not really what I'm looking for..............I started out
wanting to look at a workbook and see all the 8 tabs as they should normally
appear when all Protection and Non-Protection was properly in
place...........then, if during my editing I have to Un-protect a sheet by
hand (or accidently Protect one that should be un-protected), that sheets
tab would display some symbol or special color to alarm me that the setting
for that sheet was not proper for saving.............then I could fire a
macro and all protection would be properly in place, whether it already was,
or not, not just a toggle.........It still would be nice if that was a
feature that whenever a sheet was Protected, it's tab would have a

border

or
underlined name or something to so indicate, huh?

But, maybe after thinking about your statement........all that might really
be needed is just a Before-Save macro to set all 8 Protections to their
proper settings........

So, with yours and JE's help, I think I see what can be done and I think I
understand what I want now..........it's just a matter of getting out my ole
trusty Macro-Recorder and getting to work..............thanks again to both
of you.............

Vaya con Dios,
Chuck, CABGx3

put

it

modify

it

now
to accomodate my 8 sheets, and whether I want them on or off protection.

Thanks again
Vaya con Dios,
Chuck, CABGx3



One way:

Put this in you Personal.xls workbook and attach it to a toolbar
button:

Public Sub ToggleProtectWithIndication()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet

With ActiveSheet
If .ProtectContents Then
.Unprotect Password:=PWORD
.Name = .Name & "##"
Else
.Protect Password:=PWORD
If .Name Like "*[##]" Then _
.Name = Left(.Name, Len(.Name) - 2)
End If
End With
End Sub

This will add "##" when you unprotect the sheet, and remove it

when
you

reprotect it.



Hi All.......

I regularly work on WorkBooks that require some of the sheets

to

be