VBA - I want to delete buttons which are on top of cells - Minesweeper game

as the title says, I want to delete buttons which are on top of cells given the cell location.

Here's what I tried so far to no success.

Dim r As Range    'range is already given from another function call
Dim butns As button
With Sheet1.Buttons
    butns.Top = r.Top
    butns.Left = r.Left
    End With
Sheet1.Buttons(butns).Delete

my attempt would be getting the cells "top" and "left" value and finding the button associated to that value and then deleting it.

Any help would be much appreciated. Thanks!

Answers


I think the only way you can really find which button is on top of a cell is to see if the center of the button is contained within the cell's dimensions.

Dim r As Range
Dim s As Shape
Dim i As Long
Dim cx As Double, cy As Double

For i = 1 To Sheet1.Shapes.Count
    Set s = Sheet1.Shapes(i)
    cx = s.Left + s.Width / 2
    cy = s.Top + s.Height / 2
    If cx >= r.Left And cx <= r.Left + r.Width And _
        cy >= r.Top And cy <= r.Top + r.Height _
    Then
        s.Delete
        Exit For
    End If
Next

It is form control buttons – user3221162 13 mins ago

Is this what you are trying?

Note when looping through shapes, it is important that you identify the .Type and .FormControlType to ensure that you are only deleting Form Button control else it will delete any shape which meets the criteria.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim shp As Shape

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    '~~> Loop through shapes
    For Each shp In ws.Shapes
        '~~> Check if it is a form control
        If shp.Type = 8 Then
            '~~> Check if it is a button
            If shp.FormControlType = xlButtonControl Then
                '~~> Check the cell. Taking B2 as example
                If shp.TopLeftCell.Address = "$B$2" Then shp.Delete
            End If
        End If
    Next
End Sub

I suggest that you put some info in the shapes' names (Button_1_1, Button_2_1 etc.) and then split out the coords when needed. With that you can keep track of what button is put where on creation.

Coordinate conversion is often more tricky than expected. You need to take care of zooming, DPIs and other stuff.


Need Your Help

Ignore Some % Parameters in Format String

string printf string-formatting

Since format strings show up so often, I sometimes run into the situation where I need to format a string, and then send that string into a second function for formatting again. For instance, I nee...