CODEWITHSUNDEEP
excelvbabutton
Billy J
Billy J

Reputation: 121

Disallow Resizing of Buttons in Excel Worksheet (VBA)

I have created a macro that adds two buttons to every worksheet in my workbook. I am trying to figure out how "lock" the buttons (via VBA) so that the user cannot resize, change text, etc. This seems like it should be a simple task but for some reason, I cannot find a concise answer. The only code I found is: .Placement = xlMove, however, this does not work.

Here is the current button code:

Dim t1 As Range
Set t1 = ActiveSheet.Range(Cells(TwoDown, 6), Cells(TwoDown, 6))
Set btn1 = ActiveSheet.Buttons.Add(t1.Left, t1.Top, t1.Width, t1.Height)

With btn1
    .Placement = xlMove
    .OnAction = "Module32.btnF"
    .Caption = "Sort By Field Order"
     .Name = "Sort By Field Order"
End With

t1.Select
Application.ScreenUpdating = True

Thanks in advance & Merry Christmas!

Upvotes: 1

Views: 948

Answers (1)

BigBen
BigBen

Reputation: 49998

AFAIK, the only way to do this is to protect the sheet, with DrawingObjects:=True and all other options with False or True as desired.

ws.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

Note that you'll have to Unprotect the sheet before trying to add buttons, if it's already protected.

Side note: A Button also has a Locked and LockedText property, but these are only in effect if the worksheet is protected. Setting them to False allows the button to be edited when the worksheet is in Design mode (but still protected). As mentioned earlier, I think the only way to accomplish what you're looking for is by protecting the sheet.

Upvotes: 2

Related Questions