Password Protect Indicators
 

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.

Password Protect Indicators
 

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.

Password Protect Indicators
 

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.

Password Protect Indicators
 

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.

Password Protect Indicators
 

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.

Password Protect Indicators
 

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.

Password Protect Indicators
 

yes it works with the standard "Protection" toolbar
(since xlXP?) and yes it must be visible. my code assumes
it's a standard non modified "protection" toolbar, where the
"worksheet/workbook icons are #3 and #4..)

else include a line cbr.visible=true in the updateTB macro.


normally the xla will load, the workbook_open event is autom. fired
and you're fine.

however: during testing/ building you must be sure that the xlApp
variable is set. this is easiest done by running the workbook_open
procedure from the VBE (with f5 while cursor is inside the procedure)

When the alApp is set ( Not nothing) it will monitor specified events
in the running instance of excel. This means it can react to
any workbook being activated of any selection change in all worksheets
in any open workbook.

it will call updateTB.

Update TB toggles the icons for the worksheet/workbook icons on the
toolbar.



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


BEEJAY wrote :

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.

Password Protect Indicators
 

Thanks, BUT, I'm still Stuck
I added the toolbar as required.
Currently, when I open a workbook and click on the Icon that the macro is
currently attached to, I get a message that it is password protected, which
it isn't.
When all is working correctly, obviously the VBA should be "automatically"
working whenever a workbook is open.
I don't know what is meant by, or how to "must be sure that the xlApp
variable is set."
Would you kindly take it back to pre-kindergarten level for me?
Thanks


"keepITcool" wrote:



yes it works with the standard "Protection" toolbar
(since xlXP?) and yes it must be visible. my code assumes
it's a standard non modified "protection" toolbar, where the
"worksheet/workbook icons are #3 and #4..)

else include a line cbr.visible=true in the updateTB macro.


normally the xla will load, the workbook_open event is autom. fired
and you're fine.

however: during testing/ building you must be sure that the xlApp
variable is set. this is easiest done by running the workbook_open
procedure from the VBE (with f5 while cursor is inside the procedure)

When the alApp is set ( Not nothing) it will monitor specified events
in the running instance of excel. This means it can react to
any workbook being activated of any selection change in all worksheets
in any open workbook.

it will call updateTB.

Update TB toggles the icons for the worksheet/workbook icons on the
toolbar.



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


BEEJAY wrote :

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.

Password Protect Indicators
 

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.

Password Protect Indicators
 

Greetings / Goede Morgen
I'm at your mercy. I've checked and rechecked the spelling, etc.
and still cannot get the sheets protection part to work.
Would you kindly help me out?
Thanks

"keepITcool" wrote:



yes it works with the standard "Protection" toolbar
(since xlXP?) and yes it must be visible. my code assumes
it's a standard non modified "protection" toolbar, where the
"worksheet/workbook icons are #3 and #4..)

else include a line cbr.visible=true in the updateTB macro.


normally the xla will load, the workbook_open event is autom. fired
and you're fine.

however: during testing/ building you must be sure that the xlApp
variable is set. this is easiest done by running the workbook_open
procedure from the VBE (with f5 while cursor is inside the procedure)

When the alApp is set ( Not nothing) it will monitor specified events
in the running instance of excel. This means it can react to
any workbook being activated of any selection change in all worksheets
in any open workbook.

it will call updateTB.

Update TB toggles the icons for the worksheet/workbook icons on the
toolbar.



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


BEEJAY wrote :

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.

Password Protect Indicators
 

Sorry my messages are out of sending order

When I click on VBA Project (Protection-Visual.xla, the code shows up on the
right side panel.
When I click on Microsoft Excel Objects, Nothing Changes
When I click on Sheet 1 (Sheet 1), the right panel reads ony "Option Explicit"
When I click on ThisWorkbook, the full code shows up again.
This is where I put the code originally.
I can't understand why the WorkBook Protection part of the code DOES work
for me, but the WorkSheet part does NOT.
Since I obviously don't know what I'm doing, I tried cut and paste the code
into Module 1. This didn't work at all.
Then I cut and paste it back into ThisWorkbook, Hit F5 and Run. Now the
whole thing doesn't work anymore.

Help!!!!!

"keepITcool" wrote:




Where did you put the code?

the code is intended for the objectcode module behind
thisworkbook in your workbook. (can be saved as addin)

For testing you can manually run the workbook_open event procedure by
placing your cursor in it and pressing f5
(normally the procedure is run when the workbook is opened and you dont
need to do anything.)

(this sets (instantiates) the xlapp variable so it can begin to monitor
events in all workbooks/worksheets in the application.


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


BEEJAY wrote :

Greetings / Goede Morgen
I'm at your mercy. I've checked and rechecked the spelling, etc.
and still cannot get the sheets protection part to work.
Would you kindly help me out?
Thanks

"keepITcool" wrote:



yes it works with the standard "Protection" toolbar
(since xlXP?) and yes it must be visible. my code assumes
it's a standard non modified "protection" toolbar, where the
"worksheet/workbook icons are #3 and #4..)

else include a line cbr.visible=true in the updateTB macro.


normally the xla will load, the workbook_open event is autom. fired
and you're fine.

however: during testing/ building you must be sure that the xlApp
variable is set. this is easiest done by running the workbook_open
procedure from the VBE (with f5 while cursor is inside the
procedure)

When the alApp is set ( Not nothing) it will monitor specified
events in the running instance of excel. This means it can react to
any workbook being activated of any selection change in all
worksheets in any open workbook.

it will call updateTB.

Update TB toggles the icons for the worksheet/workbook icons on the
toolbar.



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


BEEJAY wrote :

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.

Password Protect Indicators
 

I already told you TWICE.
all the code should go in THISWORKBOOK
But you must THEN start the code.

i'm giving up.
send me the book in a zip.
my email is below...






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


BEEJAY wrote :

Sorry my messages are out of sending order

When I click on VBA Project (Protection-Visual.xla, the code shows up
on the right side panel.
When I click on Microsoft Excel Objects, Nothing Changes
When I click on Sheet 1 (Sheet 1), the right panel reads ony "Option
Explicit" When I click on ThisWorkbook, the full code shows up again.
This is where I put the code originally.
I can't understand why the WorkBook Protection part of the code DOES
work for me, but the WorkSheet part does NOT.
Since I obviously don't know what I'm doing, I tried cut and paste
the code into Module 1. This didn't work at all.
Then I cut and paste it back into ThisWorkbook, Hit F5 and Run. Now
the whole thing doesn't work anymore.

Help!!!!!

"keepITcool" wrote:




Where did you put the code?

the code is intended for the objectcode module behind
thisworkbook in your workbook. (can be saved as addin)

For testing you can manually run the workbook_open event procedure
by placing your cursor in it and pressing f5
(normally the procedure is run when the workbook is opened and you
dont need to do anything.)

(this sets (instantiates) the xlapp variable so it can begin to
monitor events in all workbooks/worksheets in the application.


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


BEEJAY wrote :

Greetings / Goede Morgen
I'm at your mercy. I've checked and rechecked the spelling, etc.
and still cannot get the sheets protection part to work.
Would you kindly help me out?
Thanks

"keepITcool" wrote:



yes it works with the standard "Protection" toolbar
(since xlXP?) and yes it must be visible. my code assumes
it's a standard non modified "protection" toolbar, where the
"worksheet/workbook icons are #3 and #4..)

else include a line cbr.visible=true in the updateTB macro.


normally the xla will load, the workbook_open event is autom.
fired and you're fine.

however: during testing/ building you must be sure that the
xlApp variable is set. this is easiest done by running the
workbook_open procedure from the VBE (with f5 while cursor is
inside the procedure)

When the alApp is set ( Not nothing) it will monitor specified
events in the running instance of excel. This means it can
react to any workbook being activated of any selection change
in all worksheets in any open workbook.

it will call updateTB.

Update TB toggles the icons for the worksheet/workbook icons on
the toolbar.



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


BEEJAY wrote :

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.

Password Protect Indicators
 

Thanks for the personal review of the file.
Also the additional tweaks you did.
It works just as I envisioned it - GREAT!!


"keepITcool" wrote:


I already told you TWICE.
all the code should go in THISWORKBOOK
But you must THEN start the code.

i'm giving up.
send me the book in a zip.
my email is below...






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


BEEJAY wrote :

Sorry my messages are out of sending order

When I click on VBA Project (Protection-Visual.xla, the code shows up
on the right side panel.
When I click on Microsoft Excel Objects, Nothing Changes
When I click on Sheet 1 (Sheet 1), the right panel reads ony "Option
Explicit" When I click on ThisWorkbook, the full code shows up again.
This is where I put the code originally.
I can't understand why the WorkBook Protection part of the code DOES
work for me, but the WorkSheet part does NOT.
Since I obviously don't know what I'm doing, I tried cut and paste
the code into Module 1. This didn't work at all.
Then I cut and paste it back into ThisWorkbook, Hit F5 and Run. Now
the whole thing doesn't work anymore.

Help!!!!!

"keepITcool" wrote:




Where did you put the code?

the code is intended for the objectcode module behind
thisworkbook in your workbook. (can be saved as addin)

For testing you can manually run the workbook_open event procedure
by placing your cursor in it and pressing f5
(normally the procedure is run when the workbook is opened and you
dont need to do anything.)

(this sets (instantiates) the xlapp variable so it can begin to
monitor events in all workbooks/worksheets in the application.


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


BEEJAY wrote :

Greetings / Goede Morgen
I'm at your mercy. I've checked and rechecked the spelling, etc.
and still cannot get the sheets protection part to work.
Would you kindly help me out?
Thanks

"keepITcool" wrote:



yes it works with the standard "Protection" toolbar
(since xlXP?) and yes it must be visible. my code assumes
it's a standard non modified "protection" toolbar, where the
"worksheet/workbook icons are #3 and #4..)

else include a line cbr.visible=true in the updateTB macro.


normally the xla will load, the workbook_open event is autom.
fired and you're fine.

however: during testing/ building you must be sure that the
xlApp variable is set. this is easiest done by running the
workbook_open procedure from the VBE (with f5 while cursor is
inside the procedure)

When the alApp is set ( Not nothing) it will monitor specified
events in the running instance of excel. This means it can
react to any workbook being activated of any selection change
in all worksheets in any open workbook.

it will call updateTB.

Update TB toggles the icons for the worksheet/workbook icons on
the toolbar.



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


BEEJAY wrote :

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.