Greetings:
I am constantly protecting and unprotecting worksheets and workbooks.
This was driving me totally nuts, so I made up (with major input from user
group) protect/unprotect toolbar icons and put them in a custom tool bar.

Now I'm looking for a quick, easy to read visual aid to indicate:
A: password protect status of current (active) worksheet
B: Password protect status of current (active) Workbook

What I'm thinking is perhaps toolbar icons that changes colour, or changes
from blank to an "L" to indicate locked, or ..................... ??

The indicators should work (be active) on whichever workbook/worksheet is
currently active.

I hope my question is clear.
Hopefully someone can help me out, before I'm totally bald.

tried the protection toolbar?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


BEEJAY wrote :

Greetings:
I am constantly protecting and unprotecting worksheets and workbooks.
This was driving me totally nuts, so I made up (with major input
from user group) protect/unprotect toolbar icons and put them in a
custom tool bar.

Now I'm looking for a quick, easy to read visual aid to indicate:
A: password protect status of current (active) worksheet
B: Password protect status of current (active) Workbook

What I'm thinking is perhaps toolbar icons that changes colour, or
changes from blank to an "L" to indicate locked, or
..................... ??

The indicators should work (be active) on whichever
workbook/worksheet is currently active.

I hope my question is clear.
Hopefully someone can help me out, before I'm totally bald.

Not so good if he still has 2000 or earlier.

--
HTH

Bob Phillips

"keepITcool" wrote in message
ft.com...
tried the protection toolbar?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


BEEJAY wrote :

Greetings:
I am constantly protecting and unprotecting worksheets and workbooks.
This was driving me totally nuts, so I made up (with major input
from user group) protect/unprotect toolbar icons and put them in a
custom tool bar.

Now I'm looking for a quick, easy to read visual aid to indicate:
A: password protect status of current (active) worksheet
B: Password protect status of current (active) Workbook

What I'm thinking is perhaps toolbar icons that changes colour, or
changes from blank to an "L" to indicate locked, or
..................... ??

The indicators should work (be active) on whichever
workbook/worksheet is currently active.

I hope my question is clear.
Hopefully someone can help me out, before I'm totally bald.

Sorry, I should have specified.
I use Excel 2003.
I have the appropriate tool-bar icons open all the time.
The only way to "read" them is to put the cursor on them.
I'm looking for a "visual" read of the worksheet and workbook status.
I'm sure it must be possible.
Being a beginner, I just don't know where even to start.
thanks


"Bob Phillips" wrote:

Not so good if he still has 2000 or earlier.

--
HTH

Bob Phillips

"keepITcool" wrote in message
ft.com...
tried the protection toolbar?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


BEEJAY wrote :

Greetings:
I am constantly protecting and unprotecting worksheets and workbooks.
This was driving me totally nuts, so I made up (with major input
from user group) protect/unprotect toolbar icons and put them in a
custom tool bar.

Now I'm looking for a quick, easy to read visual aid to indicate:
A: password protect status of current (active) worksheet
B: Password protect status of current (active) Workbook

What I'm thinking is perhaps toolbar icons that changes colour, or
changes from blank to an "L" to indicate locked, or
..................... ??

The indicators should work (be active) on whichever
workbook/worksheet is currently active.

I hope my question is clear.
Hopefully someone can help me out, before I'm totally bald.

in xl2003 you have a STANDARD protection bar.
use it..

put this in Thisworkbook module of an addin
(or any workbook you autoload)
it has an application level evetn handler.

to test make sure you fire up the workbook_open procedure
to instantiate the xlApp variable (and thus it's evetns)


Option Explicit

Dim WithEvents xlApp As Application

Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Private Sub xlApp_SheetActivate(ByVal Sh As Object)
UpdateTB
End Sub

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
UpdateTB
End Sub

Sub UpdateTB()
Static cbr As CommandBar, ctlWKS As CommandBarButton, ctlWKB As
CommandBarButton
If cbr Is Nothing Then
Set cbr = Application.CommandBars("Protection")
Set ctlWKS = cbr.Controls(3)
Set ctlWKB = cbr.Controls(4)
End If
On Error Resume Next
ctlWKS.FaceId = IIf(ActiveSheet.ProtectContents, 351, 893)
ctlWKB.FaceId = IIf(ActiveWorkbook.ProtectStructure Or
ActiveWorkbook.ProtectWindows, 352, 894)

End Sub






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


BEEJAY wrote :

Sorry, I should have specified.
I use Excel 2003.
I have the appropriate tool-bar icons open all the time.
The only way to "read" them is to put the cursor on them.
I'm looking for a "visual" read of the worksheet and workbook status.
I'm sure it must be possible.
Being a beginner, I just don't know where even to start.
thanks


"Bob Phillips" wrote:

Not so good if he still has 2000 or earlier.

--
HTH

Bob Phillips

"keepITcool" wrote in message
ft.com...
tried the protection toolbar?

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


BEEJAY wrote :

Greetings:
I am constantly protecting and unprotecting worksheets and
workbooks. This was driving me totally nuts, so I made up
(with major input from user group) protect/unprotect toolbar
icons and put them in a custom tool bar.

Now I'm looking for a quick, easy to read visual aid to
indicate: A: password protect status of current (active)
worksheet B: Password protect status of current (active)
Workbook

What I'm thinking is perhaps toolbar icons that changes colour,
or changes from blank to an "L" to indicate locked, or
..................... ??

The indicators should work (be active) on whichever
workbook/worksheet is currently active.

I hope my question is clear.
Hopefully someone can help me out, before I'm totally bald.

Thank You, keepITcool
Copied into Thisworkbook module.
Saved as .XLA
"selected" the add-in.

1: I have no idea what the following means:
Would you be so kind as to step me thru it?

to test make sure you fire up the workbook_open procedure
to instantiate the xlApp variable (and thus it's evetns)

2: Does the macro call up the Standard Protection Bar?
Or does it have to be open already?
Or does it create its own toolbar?

Sorry for the bother. My application ideas far out-stretch my capabilites,
although I am learning (slowly).



"keepITcool" wrote:


in xl2003 you have a STANDARD protection bar.
use it..

put this in Thisworkbook module of an addin
(or any workbook you autoload)
it has an application level evetn handler.

to test make sure you fire up the workbook_open procedure
to instantiate the xlApp variable (and thus it's evetns)


Option Explicit

Dim WithEvents xlApp As Application

Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Private Sub xlApp_SheetActivate(ByVal Sh As Object)
UpdateTB
End Sub

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
UpdateTB
End Sub

Sub UpdateTB()
Static cbr As CommandBar, ctlWKS As CommandBarButton, ctlWKB As
CommandBarButton
If cbr Is Nothing Then
Set cbr = Application.CommandBars("Protection")
Set ctlWKS = cbr.Controls(3)
Set ctlWKB = cbr.Controls(4)
End If
On Error Resume Next
ctlWKS.FaceId = IIf(ActiveSheet.ProtectContents, 351, 893)
ctlWKB.FaceId = IIf(ActiveWorkbook.ProtectStructure Or
ActiveWorkbook.ProtectWindows, 352, 894)

End Sub






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


BEEJAY wrote :

Sorry, I should have specified.
I use Excel 2003.
I have the appropriate tool-bar icons open all the time.
The only way to "read" them is to put the cursor on them.
I'm looking for a "visual" read of the worksheet and workbook status.
I'm sure it must be possible.
Being a beginner, I just don't know where even to start.
thanks


"Bob Phillips" wrote:

Not so good if he still has 2000 or earlier.

--
HTH

Bob Phillips

"keepITcool" wrote in message
ft.com...
tried the protection toolbar?

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


BEEJAY wrote :

Greetings:
I am constantly protecting and unprotecting worksheets and
workbooks. This was driving me totally nuts, so I made up
(with major input from user group) protect/unprotect toolbar
icons and put them in a custom tool bar.

Now I'm looking for a quick, easy to read visual aid to
indicate: A: password protect status of current (active)
worksheet B: Password protect status of current (active)
Workbook

What I'm thinking is perhaps toolbar icons that changes colour,
or changes from blank to an "L" to indicate locked, or
..................... ??

The indicators should work (be active) on whichever
workbook/worksheet is currently active.

I hope my question is clear.
Hopefully someone can help me out, before I'm totally bald.

Greetings: Holidays are over and we're back at it, again.
I actually got part of your add-in to work - I don't know what my problem
was, before the holidays.

However:
1: The WorkSHEET part does not work - ie: does not change depending on
protected or not protected. ( The Work-Book portion works great)
2: Is it possible to slightly modify the add-in somehow, so that it will
actually "read" the work-sheet and work-book status, when first opened? At
present, I have to select a different sheet, in order for it to show the
status.

Thanks for now. Looking forward to your response.



"keepITcool" wrote:


in xl2003 you have a STANDARD protection bar.
use it..

put this in Thisworkbook module of an addin
(or any workbook you autoload)
it has an application level evetn handler.

to test make sure you fire up the workbook_open procedure
to instantiate the xlApp variable (and thus it's evetns)


Option Explicit

Dim WithEvents xlApp As Application

Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Private Sub xlApp_SheetActivate(ByVal Sh As Object)
UpdateTB
End Sub

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
UpdateTB
End Sub

Sub UpdateTB()
Static cbr As CommandBar, ctlWKS As CommandBarButton, ctlWKB As
CommandBarButton
If cbr Is Nothing Then
Set cbr = Application.CommandBars("Protection")
Set ctlWKS = cbr.Controls(3)
Set ctlWKB = cbr.Controls(4)
End If
On Error Resume Next
ctlWKS.FaceId = IIf(ActiveSheet.ProtectContents, 351, 893)
ctlWKB.FaceId = IIf(ActiveWorkbook.ProtectStructure Or
ActiveWorkbook.ProtectWindows, 352, 894)

End Sub






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


BEEJAY wrote :

Sorry, I should have specified.
I use Excel 2003.
I have the appropriate tool-bar icons open all the time.
The only way to "read" them is to put the cursor on them.
I'm looking for a "visual" read of the worksheet and workbook status.
I'm sure it must be possible.
Being a beginner, I just don't know where even to start.
thanks


"Bob Phillips" wrote:

Not so good if he still has 2000 or earlier.

--
HTH

Bob Phillips

"keepITcool" wrote in message
ft.com...
tried the protection toolbar?

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


BEEJAY wrote :

Greetings:
I am constantly protecting and unprotecting worksheets and
workbooks. This was driving me totally nuts, so I made up
(with major input from user group) protect/unprotect toolbar
icons and put them in a custom tool bar.

Now I'm looking for a quick, easy to read visual aid to
indicate: A: password protect status of current (active)
worksheet B: Password protect status of current (active)
Workbook

What I'm thinking is perhaps toolbar icons that changes colour,
or changes from blank to an "L" to indicate locked, or
..................... ??

The indicators should work (be active) on whichever
workbook/worksheet is currently active.

I hope my question is clear.
Hopefully someone can help me out, before I'm totally bald.