Macro stuffing up my checkboxes

Hi all,

Have a list of items with 3 form checkboxes on each line.
When I click on the worksheet to and copy to new workbook etc it all looks good in the new workbook. Perfect.
If I record a macro to do this process and then run the macro it will deselect all that were selected and display the work "Check" next to each of them and some of them will double up.

This will happen to the original content and also the new content pasted into the new workbook.

Any ideas as to why?

Edit - I do notice all the original data is there, but brand new checkboxes are added to each cell covering the original data. (In both the original workbook and the copy into the new workbook)

Please Login or Register  to view this content.

Hi there,

If you run that code while the active worksheet contains CheckBoxes created by a previous execution of the code, another set of CheckBoxes will be created on top of the original set.

If I have understood your requirements correctly (and I may well not have!) you need two separate routines - one to create the CheckBoxes on the initial worksheet, and one to copy that worksheet to a new workbook.

The word "Check" which you are seeing is the shortened version of "Check Box nnn" which Excel assigns as the default caption of a newly-added Checkbox - you are seeing the shortened version of this caption because the width (124) you assigned to the Checkbox isn't large enough to display the entire caption.

Just for information, a more flexible way to create your CheckBoxes is outlined below - using an approach like this means that it is far less painful if you ever need to change the positions and/or dimensions of the CheckBoxes.

Please Login or Register  to view this content.

The above code will create 24 of the CheckBoxes created by your original code - to add more rows of Checkboxes, just change the iNO_OF_ROWS value to suit, and add an extra vaTops(i) = nnn statement for each new row of CheckBoxes.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Originally Posted by Greg M

... The above code will create 24 of the CheckBoxes created by your original code ...

Originally Posted by koska

...
1. Have a list of items with 3 form checkboxes on each line ...
2. ...macro ... deselect all that were selected ...
3. ...and some of them will double up ...
4. ... but brand new checkboxes are added to each cell covering the original data (In both the original workbook and the copy into the new workbook)

koska,
1. How many do you really have these x3 lines ?
(for now you have 4 blocks of CheckBoxes for 7x3, 5x3, 10x3 and 14x3 items)
2. Would you like the macro to select all of these check boxes ?
3. Which ones are "duble up" ?
4. ???