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)

Sub Checklist()
'
' Checklist Macro
'

'
    Sheets("Checklist").Select
    ActiveSheet.CheckBoxes.Add(247.5, 231, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 231, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 231, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 246, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 246, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 246, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 261, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 261, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 261, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 276, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 276, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 276, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 336, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 336, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 336, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 351, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 351, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 351, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 366, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 366, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 366, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 381, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 381, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 381, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 396, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 396, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 396, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 411, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 411, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 411, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 426, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 426, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 426, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 441, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 441, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 441, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 456, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 456, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 456, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 471, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 471, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 471, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 516, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 516, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 516, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 531, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 531, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 531, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 546, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 546, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 546, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 561.75, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 561.75, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 561.75, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 606, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 606, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 606, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 621, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 621, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 621, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 636, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 636, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 636, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 651, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 651, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 651, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 666, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 666, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 666, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 681, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 681, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 681, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 696, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 696, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 696, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 291, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 291, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 291, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 591, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 591, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 591, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 711, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 711, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 711, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 576, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 576, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 576, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 96, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 96, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 96, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 111, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 111, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 111, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 126, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 126, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 126, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 141, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 141, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 141, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 156, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 156, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 156, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 171, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 171, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 171, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(247.5, 186, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(303.75, 186, 24, 17.25).Select
    ActiveSheet.CheckBoxes.Add(360, 186, 24, 17.25).Select
    Sheets("Checklist").Copy
    Range("E2").Select
End Sub

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.

Sub CreateCheckBoxes()

    Const sSHEET_NAME   As String = "Checklist"
    Const iNO_OF_ROWS   As Integer = 8
    Const dCOLUMN_1     As Double = 247.5
    Const dCOLUMN_2     As Double = 303.75
    Const dCOLUMN_3     As Double = 360
    Const dHEIGHT       As Double = 17.25
    Const dWIDTH        As Double = 124

    Dim wksChecklist    As Worksheet
    Dim vaTops          As Variant
    Dim iRowNo          As Integer

    Set wksChecklist = ThisWorkbook.Worksheets(sSHEET_NAME)

    ReDim vaTops(1 To iNO_OF_ROWS)

    vaTops(1) = 231
    vaTops(2) = 246
    vaTops(3) = 261
    vaTops(4) = 276
    vaTops(5) = 336
    vaTops(6) = 351
    vaTops(7) = 366
    vaTops(8) = 381

    For iRowNo = 1 To iNO_OF_ROWS

        wksChecklist.CheckBoxes.Add dCOLUMN_1, vaTops(iRowNo), dWIDTH, dHEIGHT
        wksChecklist.CheckBoxes.Add dCOLUMN_2, vaTops(iRowNo), dWIDTH, dHEIGHT
        wksChecklist.CheckBoxes.Add dCOLUMN_3, vaTops(iRowNo), dWIDTH, dHEIGHT

    Next iRowNo

End Sub

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. ???