Results 1 to 8 of 8
Like Tree2Likes
  • 1 Post By JoeMo
  • 1 Post By JoeMo

New here, having a hard time with checkbox code.

This is a discussion on New here, having a hard time with checkbox code. within the Excel Questions forums, part of the Question Forums category; I am trying to write some code that will copy data from one cell, and then paste it to another ...

  1. #1
    New Member
    Join Date
    Nov 2014
    Posts
    12

    Default New here, having a hard time with checkbox code.

    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

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    14,068

    Default Re: New here, having a hard time with checkbox code.

    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 likes this.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Nov 2014
    Posts
    12

    Default Re: New here, having a hard time with checkbox code.

    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!!!

  4. #4
    Board Regular
    Join Date
    Aug 2005
    Location
    New York City
    Posts
    175

    Default Re: New here, having a hard time with checkbox code.

    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

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    14,068

    Default Re: New here, having a hard time with checkbox code.

    Quote Originally Posted by mshaneweaver View Post
    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 likes this.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  6. #6
    New Member
    Join Date
    Nov 2014
    Posts
    12

    Default Re: New here, having a hard time with checkbox code.

    Great, thanks again!

  7. #7
    New Member
    Join Date
    Nov 2014
    Posts
    12

    Default Re: New here, having a hard time with checkbox code.

    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

  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    14,068

    Default Re: New here, having a hard time with checkbox code.

    Quote Originally Posted by mshaneweaver View Post
    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com