View original (French)

Checkbox: Copy-Paste = Offset of the Pasted Box

Solved
Bertellus -  
Froulik Posted messages 4292 Status Member -
Hello,

I am using Excel 2010 and I have encountered a problem:

I have a checkbox in a merged cell. The checkbox is completely included within the cell. The option: "Move and size with cells" is enabled.

I copy this checkbox, change sheets, and paste it into a cell of the same size (merged, same number of cells, same column and row widths).

The result: a checkbox appears but not IN the cell: it appears below, straddling the target cell and the cell below.

Wanting this function in a macro, I tested the same macro 4 times and got a different position each time (cyclic: it shifts down, then positions correctly, then shifts up, then shifts down...)

How can I ensure that the checkbox stays perfectly in place?

Thank you for your help :)

4 answers

  1.
    Froulik Posted messages 4292 Status Member 954
     
    I'm not a specialist, but have you tried selecting your "checkbox" object
    right-clicking
    control format
    properties
    object positioning?
    0
  2. Bertellus
     
    I would like to clarify that the issue seems to occur because my macro includes row insertions before the checkbox copy-paste. On a test sheet, the checkbox positions correctly every time.

    The macro copies a "storage" area to paste it into a sheet for printing. So, I insert the cells without the checkboxes first, then the checkboxes into the target cells.

    With each iteration of the macro, it's at this point that the checkbox gets misaligned.
    0
    1. Froulik Posted messages 4292 Status Member 954
       
      yeah. so what I suggested probably won't work
      sorry
      0
    2. Bertellus
       
      It's me who's at fault. I didn't specify everything from the beginning. Sorry.
      I've already searched everywhere in the VBA, in the object's properties, in the control format, and nothing works; I still have this offset happening.
      0
  3. Bertellus
     
    Some parts of my VBA code might help to solve my problem: here is the relevant section (the rest works fine):

    Sheets("XXXX").Select
    Rows("19:22").Copy
    Sheets("YYYY").Select
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown

    Sheets("XXXX").Select
    ActiveSheet.Shapes.Range(Array("Checkbox1")).Select
    Selection.Copy
    Sheets("YYYY").Select
    Range("A20:D21").Select
    ActiveSheet.Paste
    0
  4. Bertellus Posted messages 6 Status Member
     
    Well, for those who might be in the same situation as me, here is an alternative solution:

    Instead of using an ActiveX control, I used a form checkbox. I had tried just about everything except that. It now works correctly.

    Activating an ActiveX in macros is generally preferable, but in this case, there's no need to copy-paste the checkbox; it happens automatically (without design mode) along with the cell it’s in.

    90% of computer problems are found between the chair and the screen... and I'm ashamed to admit, not having thought of the simplest alternative solution... :'(
    0
    1. Froulik Posted messages 4292 Status Member 954
       
      Shame? Maybe, but this lack of (relatively) reflection has allowed you to progress in your knowledge, hasn't it? So it's all good :o))
      0