New here, having a hard time with checkbox code.

mshaneweaver

New Member
Joined
Nov 25, 2014
Messages
12
I am trying to write some code that will copy data from one cell, and then paste it to another when checked. It should also delete any data in the destination cells if the box is unchecked. The code I'm working on is below, is this something someone can help with? Also, the clear function isnt quite what I want because it clears the formatting with the data.



Thanks,

Shane



Private Sub CheckBox1_Click()
If ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.Value = 0 Then
Range("Q2").Select
Selection.Copy
Range("I6").Select
ActiveSheet.Paste
Range("R2").Select
Selection.Copy
Range("J6").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = True
Else
Range("J6").Clear
Range("I6").Clear


End If
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
No need to do all the selecting to copy and paste. Clear - clears everything, including formats. ClearContents - clears the contents, but not the formats.

Try this:
Code:
Private Sub CheckBox1_Click()
If CheckBox1 Then
    Range("Q2:R2").Copy Range("I6:J6")
    Application.CutCopyMode = False
Else
    Range("I6:J6").ClearContents
End If
End Sub
 
Upvote 0
Omg you are awesome, thanks so much!!! How would you change this if the "copy" cells are on a different sheet or if the source or destination cells are not consecutive?

Thanks again!!!
 
Upvote 0
I just received board-help with a similar problem using InputBoxes.

For the e format issue:

Code:
         '[Do stuff]
                  .
                  .
                  .
    cFormat = ActiveCell.NumberFormat
    ActiveCell.Value = dNew                    'Date format defaults to m/d/y h:m:s.  Messy
    ActiveCell.NumberFormat = cFormat
 
Upvote 0
Omg you are awesome, thanks so much!!! How would you change this if the "copy" cells are on a different sheet or if the source or destination cells are not consecutive?

Thanks again!!!
You are welcome.

If the copy cells are on a different sheet (let's say Sheet2 for example) and you are running the macro from the sheet where the paste will go then:

Sheets("Sheet2").Range("Q2:R2").Copy Range("I6:J6")

If the source or destination cells are not contiguous then you need to do a cell by cell copy/paste.
 
Upvote 0
Ok I have had to put this project on the back burner a couple of weeks and opened it today. The checkbox no longer functions (will not check/uncheck) and if I delete it and try and insert a new checkbox I get a "Cannot insert" error. What the heck could have happened?


Thanks,
shane
 
Upvote 0
Ok I have had to put this project on the back burner a couple of weeks and opened it today. The checkbox no longer functions (will not check/uncheck) and if I delete it and try and insert a new checkbox I get a "Cannot insert" error. What the heck could have happened?


Thanks,
shane

You can thank a recent Microsoft update for that. Search for all files with a .exd extension and remove them, then reboot your computer and your checkboxes should be working again. Look here for more details on what to do:
Office Update breaks ActiveX controls | Excel Matters
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top