populating data into cell when checkbox is checked

    Hello all,


    What I would like to do is that when a checkbox is checked, I want the data that is one row above the cell the checkbox is linked to, to populate in the next empty cell in another worksheet.


    Here is what I have so far:


    Code
    Sub Checkbox_Click()
Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Log")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
ws.Range("A" & LastRow).Value = ActiveCell.Offset(-1, 0).Value


End Sub


    This works ONLY if I first select the cell that the checkbox is linked to, to be my active cell. I want the code to be written such that active cell is always the cell that the checkbox I just clicked is linked to. ADDITIONALLY, if I uncheck the checkbox, I want it to erase the data I populated in the other sheet. This code needs to work for ANY text box. I have over 150 checkboxes in my sheet and once the code is working, I want to select all the checkboxes in the sheet and add this macro to it.


    Thanks!

    Re: populating data into cell when checkbox is checked


    not quite sure how your offsetting works but this should give you idea even if it isnt


    Code
    Sub Checkbox_Click()    Dim LastRow As Long, ws As Worksheet
    Set ws = Sheets("Log")
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
    ws.Range("A" & LastRow).Value = Cells(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row, -1).Value
     
End Sub


    application.caller returns the control that was clicked
    then topleftcell.row gives the row

    Re: populating data into cell when checkbox is checked


    I got an application-defined or object-defined error when clicking the checkbox. The error is on the last line of code.

    Re: populating data into cell when checkbox is checked


    did you assign the code to run when checkbox is clicked?
    it doesnt work if you just run it normally

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login