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