Hide command button if worksheet is protected

Questions

1 - Command Button is Active X or Forms based control ?

2 - How is the sheet protection being toggled ?

...the sheet is being protected using just the protect sheet facility within excel.

this is a hard "event" to trap - ie the event (change to sheet protection) does not exist by default.

You can use a Sub Routine (via Command Button etc) but again this does not prevent the user from protecting / unprotecting the sheet by "normal" means and toggling sheet protection via normal means is not a Volatile action.

Maybe if yu explai why you need to do this we might be able to sugget a better way.

i want this only to be available to me

You could then perhaps use the Workbook_Open event to toggle visibility based on NT username...

Please Login or Register  to view this content.

the above utilises an ActiveX Command Button but get the gist I hope ?

Yes, correct.

It's a much safer way of ascertaining current user than Application.Username givne the latter can be easily modified via Excel Options... the former is pretty bullet proof unless someone has logged on to a machine using someone else's credentials at which point you have bigger fish to fry.

Remember of course

a) you will want to password protect your VBE Project

b) no security in XL is 100% watertight (those in the know will always find a way to circumvent XL protection)

Originally Posted by DonkeyOte

...you will want to password protect your VBE Project

DonkeyOte how do I password protect the VBA project so that it is secure, is it just the excel feature within the VBAProjectProperties?, i know the protect worksheet/workbook is useless as it is easily overidden.

...how do I password protect the VBA project so that it is secure, is it just the excel feature within the VBAProjectProperties?

Yes.

(on an aside please don't quote entire posts unless strictly necessary as it clutters the board, thanks)

Here is a class and code to capture the Protect/unprotect usage.

Class CEvt

Please Login or Register  to view this content.

Module

Please Login or Register  to view this content.

Thisworkbook module

Please Login or Register  to view this content.

Currently the click event only displays msgboxes. So stick your code in there to do what you want.

Thanks Andy, that's brilliant as usual!

Please post a sample file that reflects your exact setup (sheet name, button etc...) with code in place.

Sample workbook:

OK a few points...

1 - workbook level events (like Workbook_Open) should reside in the Workbook Object which is called ThisWorkbook (ie not in Sheet Object nor standard Module)

2 - you refer to Sheets("Sheet3") ... the button is seemingly on sheet called "Template" which happens to have Sheet3 codename so to refer to this sheet use either

a) Sheet3

or

b) Sheets("Template")

3 - the code I gave originally was for an ActiveX Command Button rather than a Forms Command Button... you're using the latter so you will need to revise the code accordingly

4 - given neither Sheet1 nor Sheet2 (codenames) exist in your sample file you need to remove those lines


So putting all of the above together the below should be added to ThisWorkbook Object in your sample file

Please Login or Register  to view this content.

I couldnt same to get the username to work, i was aware the username had to be in caps to be recognized by the code.

Again, not sure I follow but just to elaborate in terms of the use of UCase etc...

By default VBA is case-sensitive unlike native XL, eg

Please Login or Register  to view this content.

generates FALSE (whereas in native XL: ="Donkey"="donKey" would generate TRUE)

on that basis I tend to convert both strings to a common case before comparing so as to remove issue of case sensitivity

Please Login or Register  to view this content.

Generates TRUE as DONKEY = DONKEY

In your case the str1 is the Environ("username") and str2 is your own NT login, so given I am going to coerce str1 to Upper Case I must ensure that the test value is also in Upper Case, ie even if my real NT ID is donKey I would use:

Please Login or Register  to view this content.

Given my NT login will be forced to Upper Case in the first part of the test.

You can also use Option Compare Text at the head of a Module to indicate all comparisons should be case insensitive, however, in some cases you may want case sensitivity so I prefer the above method myself as a general rule