CODEWITHSUNDEEP
excelvbaconditional-statementsselection
PocketLoan
PocketLoan

Reputation: 532

Deleting the contents of cells if a checkbox is clicked when a command button is pressed

I am building an order configuration spreadsheet. The various product categories have options which are incompatible, so I created some checkboxes in column A. I want to make a control button select the contents of each row from column H:L that has a selected checkbox and delete it and then unselect the checkbox. I don't really know how to write such code. Your help is really appreciated.

Sub EliminateCheckBoxes() 
    Dim CB As CheckBox, n As Long, x As Long 
    n = ActiveSheet.CheckBoxes.Count 
    For x = n To 1 Step -1 
        Set CB = ActiveSheet.CheckBoxes(x) 
        If CB.Name <> "Check Box 1" Then 
    Next x 
End Sub

Upvotes: 4

Views: 3507

Answers (1)

Cor_Blimey
Cor_Blimey

Reputation: 3310

You need to use a property of the checkbox that links to your relevant row. Either (if linked) use .LinkedCell (string then get the range object) or else if positioned on the relevant row then .TopLeftCell (range)

E.g.:

'Using LinkedCell
Range(cb.LinkedCell).EntireRow.Range("H1:L1").Delete

'or using TopLeftCell
cb.TopLeftCell.EntireRow.Range("H1:L1").Delete

cb.Value = -4146 'uncheck the checkbox

Example of your code with the above and an added check for whether the checkbox is checked:

Sub EliminateCheckBoxes() 
    Dim CB As CheckBox, n As Long, x As Long 
    n = ActiveSheet.CheckBoxes.Count 
    For x = n To 1 Step -1 
        Set CB = ActiveSheet.CheckBoxes(x)
        If CB.Name <> "Check Box 1" Then 'are you intentionally excluding Check Box 1?
            If CB.Value = 1 then
                CB.TopLeftCell.EntireRow.Range("H1:L1").ClearContents
                CB.Value = -4146 'uncheck the checkbox
            End If
        End If
    Next x 
End Sub

Upvotes: 3

Related Questions