Option to flag a cell in a column so it can be used to copy certain row data-CodePudding

I have been looking in to adding buttons to a column so I can add specific rows of data to another sheet. It seems like you can't add buttons to cells so I have made my column cells look like buttons through formatting and then added a click function to determine if the row is clicked. This works but I'm starting to think it'll be better if the user selects which rows they want first clicks a single button to the right of the table to take all the rows out and add them to the other sheet.

Looking at radio buttons it seems like they too can't be embedded into a column but linked to a particular cell such that when they are clicked it can trigger data to a cell. They also seem like they can't be de-clicked either.

Is there something that can sit in a cell that when clicked will set it to say 1 ("Add to Sheet") and clicked again will set it to 0 ("Don't add")? Which I can then read in to a function to determine which rows should be added to the other sheet.

CodePudding user response：

The easiest way is to add a column where the user puts in an X (or a blank). You can put an validation rule to that column so that only X is allowed if you want.

Unfortunately there is no Click-Event for a cell so that you can react on a click to toggle the X, best bet is the double-click (Worksheet_BeforeDoubleClick), so instead of entering the X by keyboard, the user can also double click on the cell:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 Then   ' Assuming your selection column is [A]
        Target.Value = IIf(Target.Value = "", "X", "")
    End If
End Sub

If you think that this is too complicated for the user, you can put a control (like a checkbox) in every cell. However, you will either have to create those controls manually one by one or by code. You need to understand that all objects like controls, shapes, images, charts (and so on) are separate objects and they are not linked to a cell. You cannot access a shape with a property like Range("A1").shape, the shapes are only linked to the worksheet.
To force Excel to move a shape when a cell is resized, you need to set the property "Move with cells" so that when rows or columns are resized, the shape stays within the cell. But again, the control is not linked to the cell, Excel just moves its position (left and top property).

I am not a big fan of controls on a sheet, and I will not start to discuss the pros and cons of form controls vs ActiveX controls. If I need something like a button, I usually "misuse" a simple shape for that - every shape has a OnClick-method that is called when the shape is clicked.
The following piece of code places a rectangle shape inside a cell that bacically serves to catch the mouse click. I named those shapes "XShape" because they are used to set an X into a cell. The complete shape name contains also the row and column. The routine first checks if a shape with that name already exists - if yes, no new shape is created.

Sub AddShape(c As Range)
    Const XShapeName_Prefix = "XShape_"
    Const DeltaX = 2
    Const DeltaY = 1
    
    Dim ws As Worksheet, sh As Shape
    Set ws = c.Worksheet
    Dim shapeName As String
    shapeName = XShapeName_Prefix & c.row & "_" & c.Column
    
    ' Check if shape is already there
    On Error Resume Next
    Set sh = ws.Shapes(shapeName)
    On Error GoTo 0
    If Not sh Is Nothing Then Exit Sub      ' Shape already there
    
    Set sh = ws.Shapes.AddShape(msoShapeRectangle, c.Left   DeltaX, c.Top   DeltaY, c.Width - 2 * DeltaX, c.Height - 2 * DeltaY)
   
    sh.Fill.Visible = msoFalse
    sh.Line.Visible = msoTrue
    sh.Line.Weight = 0.75
    sh.Line.ForeColor.RGB = vbBlack
    sh.Name = shapeName
    sh.OnAction = "XShape_Clicked"
    
End Sub

(Hint: If you set the ForeColor to vbWhite, the shape is invisible but still catches the mouse click)

As you can see, the OnAction-method is set to a subroutine XShape_Clicked. You can use the same routine for any number of shapes, the routine figures out which shape was clicked by using Application.Caller. It writes an "X" (or blank) into the underlying cell. Just format the cell so that the X is displayed inside the shape.

Sub XShape_Clicked()
    
    Dim sh As Shape
    On Error Resume Next
    Set sh = ActiveSheet.Shapes(Application.Caller)
    On Error GoTo 0
    If sh Is Nothing Then Exit Sub

    Dim cell As Range
    Set cell = sh.TopLeftCell
    If cell Is Nothing Then Exit Sub
    
    cell.Value = IIf(UCase(cell.Value) = "X", "", "X")
End Sub

Now all you need is a small routine that puts a shape in every row of your data:

Sub AddShapesToColumn()
    Dim row As Long, lastRow As Long
    With ActiveSheet  ' Replace this with the sheet you want to use
    
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).row
        For row = 2 To lastRow
            AddShape .Cells(row, "A")
        Next row
    End With
End Sub

You can call this routine whenever needed - eg if a new row is created, it will create new shapes only for the new rows.