Excel Command button - Lock so that is can\'t be moved Hi All, I have two Excel

Question

Excel Command button - Lock so that is can't be moved

Hi All,

I have two Excel command buttons (not ActiveX buttons). 1 of the 2 buttons is a toggle button where my code toggles the caption between ON and OFF so I must be able to change the caption on this button.

The column and row sizes don't change on the tab where the buttons reside.

The problem is that the user can right click to select either button and then move it.

How can I "Lock" these two buttons in the current position so the user can only CLICK the buttons to run the code?

Also, I want to be able to lock it using vba as all other securtiy is turned on and off by CODE.lock it using vba as all other securtiy is turned on and off by CODE.

Many Thanks

Explanation / Answer

No guarantee, I haven't tried all possible actions when the sheet is "semi-unprotected".

But instead of using a statement like

ActiveSheet.Unprotect Password:="thePassword"

try using this instead

ActiveSheet.Protect DrawingObjects:=True, Contents:=False, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True, password:="thePassword"

The Contents:=False pretty much opens it up for any changes, but the DrawingObjects:=True part of it keeps you from grabbing shapes on the sheet and moving them around.

You could set this up as a Function to be called and pass either the worksheet object or the worksheet name that is to be semi-unprotected like this (uses worksheet name passed). You'll probably need an equivalent ProtectSheet() function to specifically actually protect what you want protected properly again.

You may also need a standard

Worksheets(sheetName).Unprotect password:="thePassword"

statement just ahead of the .Protect statement in this.

Function SemiUnprotectSheet(sheetName As String) As Boolean
On Error Resume Next
SemiUnprotectSheet = False
Worksheets(sheetName).Protect DrawingObjects:=True, Contents:=False, Scenarios:= _
      False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
      AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
      :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
      AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
      AllowUsingPivotTables:=True, Password:="thePassword"
If Err = 0 Then
    SemiUnprotectSheet = True
Else
    Err.Clear
End If
On Error GoTo 0

End Function

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.