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.