How to mark all Marlett checkboxes

[sofiachr]

Hi,

I want to mark all the marlett checkboxes on the sheet. The macro will be linked to a button. The code allowing marlett checkboxing is the same as always:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub


If Not Intersect(Target, Range("A3:A102")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub

Now, how do I write a code that marks all the checkboxes in this area, A3:A102? And also, how to make it unmark all the checkboxes by pressing another button...

Help?

 

[Jerry Sullivan]

Hi sofiachr,

Place this code in a Standard Code Module (not a Sheet Code Module).

Sub CheckAll()
 With ActiveSheet.Range("A3:A102")
   '--optional: in case font not already set
   .Font.Name = "Marlett"
   .Value = "a"
 End With
End Sub

Sub UnCheckAll()
 With ActiveSheet.Range("A3:A102")
   .Font.Name = "Marlett"
   .Value = vbNullString
 End With
End Sub

 

[sofiachr]

Thank you Jerry!

Do you happen to know one more thing?

In another code I want to copy B3:C102 but only the rows that is checked in column A...

Thank you!

 

[Jerry Sullivan]

Thank you Jerry!

Do you happen to know one more thing?

In another code I want to copy B3:C102 but only the rows that is checked in column A...

Thank you!

To what destination range are you copying the rows?

Do you need to copy the values only or formulas and formats?

 

[sofiachr]

I´m copying them to another sheet, we can call it "sheet 2" and pasting from A1. It shouldn´t be any blank rows after pasting and there are just values.

Thank you!

 

[Jerry Sullivan]

Here's some code for you to try. Modify the names of both the source and destination sheets to match yours.

Sub CopyCheckedItems()
'--copies rows within a specified range that have the value "a"
'  in Column A indicating a checked Marlett Checkbox.
 
 Dim rVisible As Range
 
 With Sheets("Sheet1")
   .AutoFilterMode = False
   With .Range("A2:C102")
      '--autofilter to show only rows with checks in column A
      .AutoFilter Field:=1, Criteria1:="=a", Operator:=xlAnd
      On Error Resume Next
      Set rVisible = .Range("B3:C102").SpecialCells(xlCellTypeVisible)
      On Error GoTo 0
   End With
   .AutoFilterMode = False
   If rVisible Is Nothing Then
      MsgBox "No items are checked"
      Exit Sub
   Else
      rVisible.Copy
   End If
   .AutoFilterMode = False
 End With
 
 With Sheets("Sheet2")
   '--paste to first row with empty cell in column A.
   If .Range("A1").Value = vbNullString Then
      .Range("A1").PasteSpecial (xlPasteValues)
   Else
      .Cells(.Rows.Count, "A").End(xlUp)(2).PasteSpecial (xlPasteValues)
   End If
 End With
 
 Application.CutCopyMode = False
End Sub