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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,729
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
 

mshaneweaver

New Member
Joined
Nov 25, 2014
Messages
12
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!!!
 

ElBombay

Board Regular
Joined
Aug 3, 2005
Messages
185
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,729
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.
 

mshaneweaver

New Member
Joined
Nov 25, 2014
Messages
12
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,729
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,147
Messages
5,442,650
Members
405,191
Latest member
wedloski

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top