CODEWITHSUNDEEP
excelvba
Dam Gal
Dam Gal

Reputation: 145

Excel renaming buttons after save

After ctrl+S excel renames buttons for unknown reasons. The buttons are created by vba code. When I right click on the button and then release mouse it will show "Button XX" name. However if I right click on the button and hold mouse button it will show the old name. Is this a common bug?

Sub ButtonAdd(ByRef RangeTarget As Range, Optional ByVal WidthOffset As Variant, Optional ByVal HeightOffset As Variant, Optional ByVal Width As Variant, Optional ByVal Height As Variant, Optional Name As Variant = "Button", Optional Caption As Variant = "Button", Optional Command As Variant)
    If IsMissing(WidthOffset) = True Then WidthOffset = 0
    If IsMissing(HeightOffset) = True Then HeightOffset = 0
    With ASheet.Buttons.Add(RangeTarget.Left + WidthOffset, RangeTarget.Top + HeightOffset, RangeTarget.Width, RangeTarget.Height)
        .Name = Name
        .Caption = Caption
        .Font.Bold = True
        .Font.Italic = True
        .AutoScaleFont = True
        If IsMissing(Width) = False Then .Width = Width Else .Width = .Width - 2 * WidthOffset
        If IsMissing(Height) = False Then .Height = Height Else .Height = .Height - 2 * HeightOffset
        .OnAction = Command
    End With
End Sub

Sample Screenshot below: http://shrani.si/f/3A/Az/4bKcrnk6/capture.jpg

Sample Screenshot

Ok I've also tried this:

Sub ch()
    Dim z As Shape
    For Each z In ActiveSheet.Shapes
            MsgBox z.Name
    Next
End Sub

And it shows bunch of buttons that aren't on the sheet (even ctrl+A +delete) apparenetly doesn't remove them.

Upvotes: 0

Views: 107

Answers (1)

TinMan
TinMan

Reputation: 7759

I suspect that you have multiple buttons stacked on each other and when you save and then reselect the button you are actually selecting the top button which could not be renamed because the name was taken.

Using RangeTarget.Parent instead of ASheet will make your code more versatile.

With RangeTarget.Parent.Buttons.Add(RangeTarget.Left + WidthOffset, RangeTarget.Top + HeightOffset, RangeTarget.Width, RangeTarget.Height)

Upvotes: 1

Related Questions