Determine if a cell has a button over/in it

user_3916966 0
Viewed 70

How can I use VBA in excel to determine if a particular cell has one or more buttons in it?

I have a spreadsheet with 50 rows. Each row has a series of data entry fields, and then in column S, there are two buttons to do some calculations based on that data. The buttons all run the same macro, and check which row they are in to determine which row to act on.

Periodically, a user will delete one or more rows, once that data is no longer relevant. When this happens, I want to insert the same number of new rows onto the bottom, to ensure I always have 50 data rows. I can copy and paste e.g. S49 to S50, and the buttons will appear in S50, and work as intended, but I can't tell if one row or n rows have been deleted, so I don't know whether to start copying from S49, S48, or Sn.

If I write a for n = 1 to 50 loop to cycle through each row of column S, how can I check to see if there is a button present in cell Sn?

Edit: with @Michael's answer below I realised I needed to loop through the buttons, not the cells. While Michael's solution correctly answers my question as asked, in my particular application it is a little simpler:

Dim lastBtnRow as Integer
Dim b As Button
lastBtnRow = 0

For Each b In ActiveSheet.Buttons
    If b.TopLeftCell.Row > lastBtnRow Then lastBtnRow = b.TopLeftCell.Row
Next

Then it's just a matter of copying and pasting S:lastBtnRow to S:lastBtnRow+1 through S:50

vba excel
1 Answers

Cells don't have a property that identifies objects sitting in them, but objects have properties about where they're located.

You need to instead build a loop through all buttons on the sheet and test where they're located. If you're dealing with Form Buttons (and not ActiveX Buttons), then this is a simple loop that will count the number of buttons located in the cell you currently have selected:

Sub ButtonLoop()

    Dim b As Button, i As Long
    
    For Each b In ActiveSheet.Buttons
        If b.TopLeftCell.Address = Selection.Address Then i = i + 1
    Next
    
    MsgBox i

End Sub

If you want to check the number of buttons in each cell, you can either write an outer loop that loops through each cell, and then use the loop above as the inner loop to check for buttons in the current cell in the outer loop; or more efficiently use a data structure such as a dictionary to increment a counter for each cell address encountered.

user_74445070
Related