Copy Cell Value to Another Sheet if Checkbox is Checked

[Jennable]

Hello! I am hoping one of you Excel geniuses might be able to help me with this! I have a spreadsheet with a number of columns representing different areas of business (Columns B, D, F, H, J, L & N). Each topic is separated by a column where each row contains ActiveX checkboxes (Columns C, E, G, I, K, & M). What I am wanting to do is write the code so that if a checkbox is checked, the contents of the cell adjacent to it will be copied to a new sheet (creating a list of the most pertinent action items). For example, IF C9 is checked, B9 is copied to the new sheet. I know how to code this simply using the IF function however the problem I am having is that it's not a 1:1 transfer-- ie I want it to function so if any of the checkboxes are checked, that cell is copied to the new sheet one after the other. I'm hoping this makes sense and you can help! Thank you in advance.

 

[Micron]

You're asking for vba code or a formula?
AFAIK, when dealing with controls on a sheet you have to get their top and left properties to figure out what row/column you're in. Insert a column to the left of the control and everything breaks as a result. Wouldn't it be easier to just double click the cell you want to copy?

 

[Skyybot]

Are you using FormControl or ActiveX checkboxes?

 

[Jennable]

Are you using FormControl or ActiveX checkboxes?

ActiveX checkboxes but could certainly change them if needed!

 

[Jennable]

You're asking for vba code or a formula?
AFAIK, when dealing with controls on a sheet you have to get their top and left properties to figure out what row/column you're in. Insert a column to the left of the control and everything breaks as a result. Wouldn't it be easier to just double click the cell you want to copy?

I’m sorry I’m not following what you are saying about top/left properties…but there shouldn’t be any changes made to the sheet (ie no columns added) to break anything.

And I’m open to whatever is needed to make the functionality work! Formula or VBA whichever is best.

 

[Micron]

I’m not following what you are saying about top/left properties

In order to know what cell/range to copy (based on which control was clicked) you have to know where the checkbox is located. If it's in B9 then its left property is related to column 2 (B) and its top property is related to row 9. That's how you know the control in B9 was the one that was clicked. That's how you know where to copy from. I still think just double clicking on a cell is the way to go, especially if it's the cell you want to copy. If the double clicked cell isn't in the row/column you wish to limit this to, that can be ignored.

there shouldn’t be any changes made to the sheet (ie no columns added)

Until it happens, long after someone said it might.

 

[NoSparks]

Below this thread MrExcel has "Similar threads", you can or should have looked at.

As per this one , here's my solution altered to suit your request, except that I don't know the ranges you will actually be using.
Put these in a standard module and give it a try.
To insert the check boxes

Sub Insert_Form_Checkboxes()
     Dim myCell As Range, myRng As Range
     Dim CBX As CheckBox

With ActiveSheet
    'delete ALL existing checkboxes from sheet, links are NOT cleared.
    '.CheckBoxes.Delete  '<~~~~~ comment out to NOT delete existing checkboxes
    'Exit Sub           '<~~~~~ uncomment when deleting checkboxes only.
    ' The range to have check boxes
    Set myRng = Range("C2:C10,E2:E10,G2:G10,I2:I10,K2:K10,M2:M10,O2:O10")
End With

Application.ScreenUpdating = False
    For Each myCell In myRng.Cells
        With myCell
            Set CBX = .Parent.CheckBoxes.Add _
                        (Top:=.Top, Left:=.Left, _
                         Width:=.Width, Height:=.Height) 'click area same size as cell
          
            CBX.Name = "CBX_" & .Address(0, 0)      'name includes address of checkbox
            CBX.Caption = ""                        'whatever you want, "" for none
            CBX.Value = xlOff                       'initial value unchecked
            CBX.OnAction = "RunForAllChkBoxes"      'run this each time clicked
            'CBX.LinkedCell = .Offset(0, 0).Address  'linked to the cell its in
            'format the cell
            '.NumberFormat = ";;;"                   'make cell contents not show
        End With
    Next myCell
Application.ScreenUpdating = True

End Sub

And the macro that runs every time a check box gets clicked

Sub RunForAllChkBoxes()
    Dim fromWhere As String
  
With ActiveSheet.CheckBoxes(Application.Caller)
    If .Value = xlOn Then
        fromWhere = .TopLeftCell.Address(0, 0)
        Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) = Range(fromWhere).Offset(, -1)
    End If
End With

End Sub

TopLeftCell is used to accommodate Micron's concern of a column being added.

Hope that is of some assistance.

 

[Micron]

TopLeftCell is used to accommodate Micron's concern of a column being added.

Excel vba novice here - hope you don't mind me asking how is that true. Let's say the top left of a checkbox is in E5 and the copied value comes from D5 because of
Range(fromWhere).Offset(, -1) correct? Someone inserts a column between B and C. Now the checkbox shifts over to column F and the -1 will pull the value from E5 instead of D5?

 

[Skyybot]

@Micron, When I use CheckBoxes, I'll link to a cell in the "Reference" row (helper column) and use Offset to get the Value of the Cell that I need. Not positioning. Helper Column is adjacent to the needed Cell (usually before so adding columns won't affect it) and Hidden.

 

[Micron]

@skybot by "link to a cell" are you referring to using the Linked cell property? If so, that will make the linked cell show True or False depending on the clicked state. Do you then hide this column (because that info is of no value)?