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