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...

Private Sub Workbook_Open()
Sheets("Sheet1").CommandButton1.Visible = UCase(Environ("username")) = "YOURNTUSERNAME"
End Sub

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)

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

Private Sub Workbook_Open()
With Sheet3
    .ScrollArea = "A1:AB23"
    .Shapes("ButtonSave").Visible = UCase(Environ("username")) = "MYUSERNAME"
End With
End Sub

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

Public Sub Demo()
Dim str1 As String, str2 As String
str1 = "Donkey"
str2 = "donKey"
MsgBox str1 = str2
End Sub

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

Public Sub DemoTwo()
Dim str1 As String, str2 As String
str1 = "Donkey"
str2 = "donKey"
MsgBox UCase(str1) = UCase(str2)
End Sub

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:

MsgBox UCase(Environ("username")) = "DONKEY"

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