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