Macro Help Regarding Check Boxes

[ExcelLost88]

Hello all!

I am currently creating a spreadsheet (excel 2010) where I have 2 recorded macros that are assigned to 1 checkbox1 in M6. The checkbox when clicked moves C6:L6 from "Warehouse to Rig" tab to "Rig to Well" tab and deletes when unclicked. How can I create multiple check boxes like the one mentioned and that moves with the row? For example, the next checkbox in M7 will move information from C7:L7 to the other spreadsheet. I would potentially need over 500+ checkboxes.

Any help is appreciated!

Thank you.

Here is my recorded macros:
Sub IFNO()
'
' IFNO Macro
'
'
Sheets("Rig to Well").Select
Range("B6:L6").Select
Selection.ClearContents
Sheets("Warehouse to Rig").Select
Range("L6").Select
End Sub
Sub IFYES()
'
' IFYES Macro
'
'
Range("B6:L6").Select
Selection.Copy
Sheets("Rig to Well").Select
Range("B6").Select
ActiveSheet.Paste
End Sub


Here is how I am running them with the checkbox:

Private Sub CheckBox1_Click()
Static AlreadyClicked As Boolean
If Not AlreadyClicked Then
IFYES
AlreadyClicked = True
Else
IFNO
AlreadyClicked = False
End If
End Sub

 

[JoeMo]

Might be easier to set the font to Wingdings in column M then use an "x" to represent the check and convert your CheckBox1_Click macro to a Worksheet_change event macro.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Columns("M")) Is Nothing Then
    For Each c In Intersect(Target, Columns("M"))
        If c.Value = "x" Then
            IFYES
        ElseIf c.Value = "" Then
            IFNO
        End If
    Next c
End If
End Sub

 

[AlphaFrog]

Use the Form-type checkbox and not the ActiveX-type checkbox from the Control Toolbox toolbar.
Overview of forms, form controls, and ActiveX controls on a worksheet

Assign this one macro to all your Form-type checkboxes in column M

The code below code goes in a standard code module e.g.; Module1

[COLOR=darkblue]Sub[/COLOR] CheckBoxes_Click()
    [COLOR=darkblue]With[/COLOR] ActiveSheet.CheckBoxes(Application.Caller) [COLOR=green]'Checkbox that called this macro[/COLOR]
        [COLOR=darkblue]If[/COLOR] .Value = xlOn [COLOR=darkblue]Then[/COLOR]
            [COLOR=green]'Checkbox is checked[/COLOR]
            .TopLeftCell.EntireRow.Range("B1:L1").Copy _
                Destination:=Sheets("Rig to Well").Range("B" & .TopLeftCell.Row)
        [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'Checkbox is unchecked[/COLOR]
            Sheets("Rig to Well").Rows(.TopLeftCell.Row).Range("B1:L1").ClearContents
            Application.Goto Sheets("Warehouse to Rig").Range("L" & .TopLeftCell.Row)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]