Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting part of a Spinner
I want to protect everthing about a Spinner except the ability to change the
"Incremental Change". Everybody would be able to change the Incremental Change with the Protection Locked In without knowing the password. Is this possible? If it is, how would I do it? Thankyou Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting part of a Spinner
Ed
You could do this with a macro, but I can't think of a way to do it with the normal Format Control dialog. The first macro adds a button to change the increment to the menu that you get when you right click a cell. You can't get to the "Excel Control" right click menu when the sheet is protected, so it won't do you any good. The second macro changes the SmallChange property Sub CreateMenuItem() With Application.CommandBars("Cell") With .Controls.Add(msoControlButton) .Caption = "Change Increment" .OnAction = "ChangeSpinner" End With End With End Sub Sub ChangeSpinner() Dim lNew As Long lNew = Application.InputBox("Enter Increment", , , , , , , 1) If lNew 0 Then Sheet1.Spinners(1).SmallChange = lNew End If End Sub Another thing that you could do is leave the spinner unprotected and change the behavior of the Format Control menu item. Sub ChangeFormatCb() Application.CommandBars.FindControl(, 3061).OnAction = "ChangeSpinner" Application.CommandBars.FindControl(, 3060).OnAction = "ChangeSpinner" End Sub That will take care of the Format Control menu item and the Format Control item on the right click menu. If you use either of these methods, be sure to set them back the way they were when the workbook is Deactivated. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Ed" wrote in message ... I want to protect everthing about a Spinner except the ability to change the "Incremental Change". Everybody would be able to change the Incremental Change with the Protection Locked In without knowing the password. Is this possible? If it is, how would I do it? Thankyou Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protecting formulas without protecting sheet so grouping still wor | Excel Discussion (Misc queries) | |||
Can you hide a spinner??? | Excel Discussion (Misc queries) | |||
Spinner | Excel Discussion (Misc queries) | |||
Using Protection on a Spinner | Excel Programming | |||
Protecting buttons with VB code rather than protecting sheets/books? | Excel Programming |