Check boxes

[Jam_Ica]

Im creating a macro that can be run to edit a file. The macro has a form that opens with 8 check boxes( named checkbox1, checkbox2 ect) The idea is to click the check boxes of the categories you want to keep. The categories are on the excel spread sheet in column C. If you do not select the checkbox the whole row should be erased. This is what I have for code. Could be way off.... =)

Private Sub CommandButton1_Click()
    Dim firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim cat As String
    
    With Application
       CalcMode = .Calculation
       .Calculation = xlCalculationManual
       .ScreenUpdating = False
     End With
    
    With ActiveSheet
        .Select
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        .DisplayPageBreaks = False
        firstrow = .UsedRange.Cells(2).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        cat = "Over All"
        For Lrow = Lastrow To firstrow Step -1
            With .Cells(Lrow, "C")
                If Not IsError(.Value) Then
                    If .Value = cat Then .EntireRow.Delete
                End If
            End With
        Next Lrow
    End With
    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

I know I will have to repeat the code for each checkbox but have just been trying the one for now. It is eraseing all rows.

Any help would be greatly appreciated!

 

[AlphaFrog]

Private Sub CommandButton1_Click()
    
    Dim Lastrow As Long
    Dim CalcMode As Long
    Dim cat As String
    Dim cb As Integer
    
    With Application
       CalcMode = .Calculation
       .Calculation = xlCalculationManual
       .ScreenUpdating = False
    End With
    
    With ActiveSheet
        For cb = 1 To 8
                             
            If Me.Controls("CheckBox" & cb).Value = False Then
                cat = Me.Controls("CheckBox" & cb).Caption
                Lastrow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
                If Not .Columns("C").Find(cat, , xlValues, xlWhole, xlByRows, xlNext, False) Is Nothing Then
                    .Range("C1:C" & Lastrow).AutoFilter Field:=1, Criteria1:=cat
                    .Range("C2:C" & Lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                End If
                .AutoFilterMode = False
            End If
            
        Next cb
    End With
    
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
    
    MsgBox "Categories deleted.", vbInformation, "Process Complete"
    
End Sub

 

[Jam_Ica]

Where do i put in the phrases. So if checkbox1 is not chexked erase the row if column 3 says "Over All". Each of the different checkboxes has a different prase to look for. Also when i run what u have helped with i get a type mismatch on the fastrow

 

[AlphaFrog]

The "phrases" are the checkbox captions

cat = Me.Controls("CheckBox" & cb).Caption

Lastrow will have an error if the active sheet is empty.