Sleeper: Linking protect to enable=false

[tonyicu]

Sorry for the name that's the best I could do for one line.

I need away to link the protect function to the "Enabled = True" (or "Enabled = False") properties of a command button.

I browsed through 2 books and can't find anything.

When the worksheet is protected, then "Enable = False".

and when unprotected "Enabled = True"

[geekgirlau]

I'm assuming that you're talking about a command button on a user form, although you could adapt this for a control on the sheet:



frmTest.cmdRun.Enabled = Not ActiveSheet.ProtectContents

[tonyicu]

I'm a newb to this where (under what section in vb editor) would I enter it in I understand the script but how do I link it to a command button i don't get asked like I would if it was a forms button

[geekgirlau]

Could you clarify what object you want to attach the code to? Is it a user form in the VB Editor? A button on the toolbar? A button on a worksheet? If attached to a button on the worksheet, which toolbar did you use to create the button?

[tonyicu]

okay this is exactly what I need to do ... so from the top...:

this is a stock sheet to link the stock in our tokyo office.

the sheet has sets of 3 buttons

1 forms button called "take one" [-1]
1 forms dutton called "Put one back"[+1]
1 command button called "restock"[+20]

All those play with the sum in a cell (for example "B6")

the 2 forms button can be accessed by any one

I want the command button ("restock") to only be accessable when the sheet is unprotected... therefore enable=true when sheet is unprotected (enable=false when sheet is protected) what script would I need to do that and where would I put it in...?

[geekgirlau]

Open your workbook and press [Alt-F11] to go to the VB Editor. If you can't see the Project Explorer on the left (this will be headed "Project - VBAProject") then select View | Project Explorer.

In this window at the top left you'll see the project for your workbook (for example, "VBAProject (Test.xls)". Double-click on "Microsoft Excel Objects", then on "ThisWorkbook".

At the top of the VBE window are two drop-down boxes - change the one in the middle to "Workbook" and the one on the right to "Open". Then paste your code in. It should end up looking like the following (I've assumed the name of the sheet is "stock", so change this if necessary).



Private Sub Workbook_Open()
Sheets("stock").restock.Enabled = Not Sheets("stock").ProtectContents
End Sub


When finished, press [Alt-F4] to close the VBE window and then save your workbook.

This macro will run automatically each time you open the workbook.

[tonyicu]

I tried it there still no luck, (with it in my "this workbook") I had exactly what you had. in the above post.

This is what I'd like it to do

This is what I have. The exact button is called "restock6" this is what I see in my "sheet1 (forming)".



Private Sub Restock6_Click()
With ActiveSheet.Range("B6")
.Value = .Value + 20
End With
frmTest.cmdRun.Enabled = Not ActiveSheet.ProtectContents
End Sub


If possible I'd like to have the code in the button script it's self I used your code from the beggining to show you.