Help with checkbox macros

[itsMelly]

Hi, I am trying to have all information in a given row (from columns A:F) automatically copied over to a separate worksheet if the checkbox is clicked.

I have tried scouring the internet for about 3 hours with no luck, so now I’m here. Can anyone help out with this?

I have attached a screenshot, as well as a OneDrive link to a copy of the sheet if you’d prefer to work in there. Thanks in advance : )

Plz Help Me Reddit.xlsx

 

[MikeVol]

Hello @itsMelly. You are using Search poorly... Look at this topic "Checkbox - True - Moves Whole Row To Another Sheet" similar to your question. I think you can adapt the code to your needs. Good luck.

 

[itsMelly]

Hello @itsMelly. You are using Search poorly... Look at this topic "Checkbox - True - Moves Whole Row To Another Sheet" similar to your question. I think you can adapt the code to your needs. Good luck.

Thanks for the reply. I went back and looked over the thread, but it looks like the example files are restricted, I cannot view them. And I’m pretty new to this, so it’s not the most intuitive to just look at code meant for someone else’s sheet and then fix it to work on mine, I’m sorry.

 

[NoSparks]

Firstly, you need to assign a macro to run to the .OnAction property of each check box.
Then you need that assigned macro to deal with the check box just clicked.

Copy this into a standard module.

Option Explicit

Sub ApplyOnAction()
    Dim rng As Range
    Dim cbx As CheckBox
    
Set rng = Sheets("Sheet1").Range("A3:A17")

For Each cbx In ActiveSheet.CheckBoxes
    If Not Intersect(cbx.TopLeftCell, rng) Is Nothing Then
        cbx.OnAction = "CopyTheRange"      'runs each time clicked
    End If
Next cbx

End Sub


Sub CopyTheRange()
    
    Dim whoCalled As String, copyRng As Range
    Dim lr As Long
    
    whoCalled = Application.Caller
    
    If ActiveSheet.CheckBoxes(whoCalled).Value = xlOn Then
        Set copyRng = Range(ActiveSheet.CheckBoxes(whoCalled).TopLeftCell.Address(0, 0)).Resize(, 6)
        With Sheets("Sheet2")
            lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
            .Range("A" & lr + 1).Resize(, 6).Value = copyRng.Value
        End With
    End If

End Sub

Run the ApplyOnAction sub once to assign the CopyTheRange macro to the OnAction property of the check boxes.
Now, clicking a check box runs the CopyTheRange macro.

Hope this is of some use.

 

[itsMelly]

Firstly, you need to assign a macro to run to the .OnAction property of each check box.
Then you need that assigned macro to deal with the check box just clicked.

Copy this into a standard module.

Option Explicit

Sub ApplyOnAction()
    Dim rng As Range
    Dim cbx As CheckBox
   
Set rng = Sheets("Sheet1").Range("A3:A17")

For Each cbx In ActiveSheet.CheckBoxes
    If Not Intersect(cbx.TopLeftCell, rng) Is Nothing Then
        cbx.OnAction = "CopyTheRange"      'runs each time clicked
    End If
Next cbx

End Sub


Sub CopyTheRange()
   
    Dim whoCalled As String, copyRng As Range
    Dim lr As Long
   
    whoCalled = Application.Caller
   
    If ActiveSheet.CheckBoxes(whoCalled).Value = xlOn Then
        Set copyRng = Range(ActiveSheet.CheckBoxes(whoCalled).TopLeftCell.Address(0, 0)).Resize(, 6)
        With Sheets("Sheet2")
            lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
            .Range("A" & lr + 1).Resize(, 6).Value = copyRng.Value
        End With
    End If

End Sub

Run the ApplyOnAction sub once to assign the CopyTheRange macro to the OnAction property of the check boxes.
Now, clicking a check box runs the CopyTheRange macro.

Hope this is of some use.

Will try this today, thank you!

 

[MikeVol]

Already tested and working.