(Solved) – Delete a Shape (Spin Button) based on Active Cell.Offset variable – Excel VBA

  • by
(solved)-–-delete-a-shape-(spin-button)-based-on-active-cell.offset-variable-–-excel-vba

VBA beginner here and I’m having trouble getting the below code to work correctly. I want it to delete the spin box relative to the active cell (Column A, same row).I think the issue is in using .Value in the IF statement, .Address did not work at all .Value doesn’t delete the active Check Boxes but it goes nuts deleting all the other Check Boxes & Spin Buttons. I just can’t figure out which option I should be using.

Spreadsheet Intended Use

In Column A there are check boxes with IF statements that when checked(true), add the date in the previous row 1 to current row Column B, a spin button in Column D (linked to column C) and a Spin Button in column F (linked to Column E). This coding is working perfectly.

When the check boxes are unchecked (False), I have code to clear the contents of the cells in that row using ActiveCell.Offset (the A column cell) and I want to delete the spin buttons so they can’t be accidentally used to add values to cleared cells.

Another Submit button will simply copy the data entered by users and paste in another sheet for analysis. This button will also reset the sheet.

Below code is only trying to delete one Spin Button, I was going to just copy, paste and update the offset for the second Spin Button when it works. If working as intended the Spin Button with the red arrow should be the only one deleted.

Original code was found in the below post. Tried to adapt it to use a variable.
Any help or guidance is appreciated. 🙂

VBA-delete shapes

Options & Date

Sub RemoveSpinBoxes()

    Dim sh As Shape
    Dim OptionOneSpin As Range

    Set OptionOneSpin = ActiveCell.Offset(0, 3)

    For Each sh In ActiveSheet.Shapes
        Debug.Print sh.Name
        Debug.Print sh.TopLeftCell.Address
        Debug.Print sh.BottomRightCell.Address

        If sh.TopLeftCell.Value = OptionOneSpin And sh.BottomRightCell.Value = OptionOneSpin Then
            Debug.Print sh.Name; " is deleted!"
            sh.Delete
        Else
        End If

    Next

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *