excel move row when checkbox selected

[ViviTheMage]

Anyone know if it is possible to make a row move to a new sheet once 3 checkboxed are x'd?

 

[quakeworld]

are the checkboxes in the same row that you wanna move? so if you have for example 5 rows you would have 3 boxes in each of the 5 rows?

 

[ViviTheMage]

yeah, same row. basically I would complete that row, and then check the box, or put an x, whatever into the column... have it move to the new sheet.

 

[PowerEngineer]

I think this is going to be hard.

The good news is that you can write macros that get run every time you change the entry in the checkboxes. The bad news is that inserts like the checkboxes and drop-down selections aren't tightly tied to the cell locations over which they are located. This suggests to me that the macro would have to dig deep into each checkbox's properties in order to identify which three checkboxes belong to each row of cells in the spreadsheet.

I'm hoping maybe someone else can educate us both on how this can be done easily!

Failing that, you might consider setting up columns of cells to be checkbox-like using data validation. You could still trigger a macro (run every time that an entry in those columns changed) to move the row, but you might also consider triggering that macro just once after all checkbox changes have been made.

Good luck.

 

[postmark]

OK, I worked up a quick macro for this to work. There are a few things to change to work with your setup.

Change the InitialSheetName and FinalSheetName to work with your workbook. Also, the variable CharacterToLookFor is what I check to see if it exists in the 3 columns (I populated it with X for now). Last thing to change is the KeyCells. I set it to look for X's on columns A, B, and C, but you can change that to wherever you check items off.

Also, make sure you have a header in Row 1 of the Final Sheet area otherwise the paste function gets funky.

And last thing... This needs to go into the Sheet code for the Initial Sheet as it run every time a cell is changed on that sheet (but only looks at the columns specified to search for the X's. To get to that code, right click on the Sheet name in Excel and click View code, then paste the macro there.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range
    Dim row_num As Integer
    Dim InitialSheetName As String

    Dim FinalSheetName As String
    
    InitialSheetName = "Initial"
    FinalSheetName = "Final"
    CharacterToLookFor = "X"
    
    

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A:C")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        row_num = Target.row
        If InStr(1, ActiveSheet.Range("A" & row_num).Value, CharacterToLookFor) > 0 Then
            If InStr(1, ActiveSheet.Range("B" & row_num).Value, CharacterToLookFor) > 0 Then
                If InStr(1, ActiveSheet.Range("C" & row_num).Value, CharacterToLookFor) > 0 Then
                    ActiveSheet.Rows(LTrim(Str(Target.row)) & ":" & LTrim(Str(Target.row))).Select
                    Selection.Copy
                    Sheets(FinalSheetName).Select
                    NumberOfRows = ActiveSheet.Range("A65536").End(xlUp).row
                    ActiveSheet.Range("A" & NumberOfRows + 1).Select
                    Selection.Insert Shift:=xlDown
                    Sheets(InitialSheetName).Select
                    ActiveSheet.Rows(LTrim(Str(Target.row)) & ":" & LTrim(Str(Target.row))).Select
                    Selection.Cells(1).EntireRow.Delete
                    
                    
                End If
            End If
        End If
       
    End If

End Sub

 

[ViviTheMage]

Thanks postmark, finally had a chance to play with it ... this is what I have now:

Private Sub SENDTOCOMPLETE(ByVal Target As Range)

    Dim KeyCells As Range
    Dim row_num As Integer
    Dim InitialSheetName As String

    Dim FinalSheetName As String
    
    InitialSheetName = "Sheet3"
    FinalSheetName = "Sheet4"
    CharacterToLookFor = "x"
    
    

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("G:I")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        row_num = Target.Row
        If InStr(1, ActiveSheet.Range("G" & row_num).Value, CharacterToLookFor) > 0 Then
            If InStr(1, ActiveSheet.Range("H" & row_num).Value, CharacterToLookFor) > 0 Then
                If InStr(1, ActiveSheet.Range("I" & row_num).Value, CharacterToLookFor) > 0 Then
                    ActiveSheet.Rows(LTrim(Str(Target.Row)) & ":" & LTrim(Str(Target.Row))).Select
                    Selection.Copy
                    Sheets(COMPLETE).Select
                    NumberOfRows = ActiveSheet.Range("A65536").End(xlUp).Row
                    ActiveSheet.Range("A" & NumberOfRows + 1).Select
                    Selection.Insert Shift:=xlDown
                    Sheets(InitialSheetName).Select
                    ActiveSheet.Rows(LTrim(Str(Target.Row)) & ":" & LTrim(Str(Target.Row))).Select
                    Selection.Cells(1).EntireRow.Delete
                    
                    
                End If
            End If
        End If
       
    End If

End Sub

Is there a way to test it? I closed it, and changed the the tabs to yes, and it doesn't do anything.

 

[postmark]

Thanks postmark, finally had a chance to play with it ... this is what I have now:

Private Sub SENDTOCOMPLETE(ByVal Target As Range)

    Dim KeyCells As Range
    Dim row_num As Integer
    Dim InitialSheetName As String

    Dim FinalSheetName As String
    
    InitialSheetName = "Sheet3"
    FinalSheetName = "Sheet4"
    CharacterToLookFor = "x"
    
    

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("G:I")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        row_num = Target.Row
        If InStr(1, ActiveSheet.Range("G" & row_num).Value, CharacterToLookFor) > 0 Then
            If InStr(1, ActiveSheet.Range("H" & row_num).Value, CharacterToLookFor) > 0 Then
                If InStr(1, ActiveSheet.Range("I" & row_num).Value, CharacterToLookFor) > 0 Then
                    ActiveSheet.Rows(LTrim(Str(Target.Row)) & ":" & LTrim(Str(Target.Row))).Select
                    Selection.Copy
                    Sheets(COMPLETE).Select
                    NumberOfRows = ActiveSheet.Range("A65536").End(xlUp).Row
                    ActiveSheet.Range("A" & NumberOfRows + 1).Select
                    Selection.Insert Shift:=xlDown
                    Sheets(InitialSheetName).Select
                    ActiveSheet.Rows(LTrim(Str(Target.Row)) & ":" & LTrim(Str(Target.Row))).Select
                    Selection.Cells(1).EntireRow.Delete
                    
                    
                End If
            End If
        End If
       
    End If

End Sub

Is there a way to test it? I closed it, and changed the the tabs to yes, and it doesn't do anything.

Did you put it in the sheet code and not the normal code section? I'll try to put it in a workbook that I can attach here so you can try it.

Edit:

Oh wait, I see you renamed the Sub. I don't think you can do that in this case as this is a special sub that runs on any changes in the sheet. You might want to rename it back to try.

 

[ViviTheMage]

sweet, that works thanks

 

[postmark]

sweet, that works thanks

Any time