Excel Move Cell Content When checkbox is clicked

Question

I would like to move the contents of excel cells when a checkbox is clicked, to another worksheet, I would also like to clear the contents from the original, I have searched on google and cannot find a solution, I may not be understanding how to do it, please advise the best way to write some code

thank you

Answer 1

In Excel 2010 & Newer you can turn on the developer menu by going to File -> Options, then click on 'Customize Ribbon' in the left column. In the far right column check the box that says 'Developer' and hit 'ok'.

Under the developer tab, that is now visible on the top bar, select 'Visual basic'.

Right click the 'Microsoft Excel Objects' line in the left column and 'insert' -> 'module'

Now in the main window on the right (empty) copy the following code.

Sub MoveData()
  if ThisWorkbook.Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value = 1 then
     Range("A1:A5").Select
     Selection.Cut
     Sheets("Sheet2").Select
     Range("A1").Select
     ActiveSheet.Paste
     Range("A1").Select
  End If
End Sub

Now go back to your excel worksheet (assuming sheet1) and under the developer tab click 'insert' and select the checkbox. Now drag a rectangle on the worksheet where you want the checkbox to be located.

Right click the check box and 'assign macro'. Select 'MoveData' and hit 'ok'.

Now enter data into sheet1 in cells A1 thru A5 and check the box. It will delete it from sheet1 and paste it into sheet2.

Nothing will happen when you uncheck the box.

If you want to change the name of the checkbox you can right click it and select 'format control' and change the 'Alt Text'. Make sure you change the macro code to match it.