No Excel Queries for a while - Here's One !

jghal

Hi All, hope someone can help with this:

In my Excel 2000 spreadsheet I have a column of check boxes in cells A6 : A206 and these are linked to cells B6:B206 to produce a TRUE in cell B6 when check box in A6 is checked and so on down to A206/B206.

What I really want is a total of 25 columns of check boxes in columns A, C, E, G etc through to column AW, with correspopnding TRUEs in columns B, D, F, H through to column AX.

I can copy 'n' paste the check boxes alright, but the only way I can make the links to the 'linked' cells is 'longhand' by right clicking the checkbox and typing in the cell ref I want. I don't, however, feel enthusiastic about doing this 25 x 200 times.

Does any one know a way of copying 'n' pasting the checkboxes where the 'linked' cell is automatically updated to the new cell - like when I copy 'n paste a formula the cell references in that are updated automatically to be appropriate to the new location.

thanks in advance

:beer:

Chippy_Minton

Here's a macro I knocked up:

Sub CreateCheckboxes()
    'Creates checkboxes on the current sheet in A6 to A206 linked to adjacent cell, i.e. B6 to B206.
    'Repeats this for every other column, i.e. C, E, G to AW
    
    Application.ScreenUpdating = False
        
    For c = Columns("A").Column To Columns("AW").Column Step 2
        For r = 6 To 206
        
            ' Create the checkbox
            With Cells(r, c)
                ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height).Select
            End With
             
            Selection.Text = ""
            
            ' Set linked cell to adjacent cell
            Selection.LinkedCell = Cells(r, c + 1).Address
        Next
    Next
    
End Sub

Test it on an empty worksheet. Note that Excel will shoot up to 99% CPU and will say 'Not responding' while the macro is running. This is normal - give it a minute or so to finish, depending on how fast your computer is.

jghal

Absolutely fantastic

thank you very very much

:beer: