Hello folks, I have a number of sheets where I want to have a check box linked to a cell, in fact I want a column of these. When I link the box to the cell and click and drag that cell down the column, it creates the check boxes but the cell link remains constant at the original cell, it does not drag the formula and increment to the next cell. Is there a way of doing this? Cheers
Found this on Mr Excel, I'll try it out and see what happens.
Sub Test() Dim Rng As Range Dim c As Range Dim Ctrl As Object Set Rng = Range("A1:A10") For Each c In Rng Set Ctrl = ActiveSheet.CheckBoxes.Add(c.Left, c.Top, 48, 17.25) With Ctrl .LinkedCell = c.Offset(0, 1).Address .Caption = "Completed" End With Next c End Sub
Code works fine except it sits each of the checkboxes smack on the border line of each cell instead of inside the cell. Anyone know how I can change the positions. My cell height is only 12.75 high, which I'd rather not change as this sheet also gets printed and settings are fixed for format reasons. Cheers
Aah! Not as resolved as I first thought. I ran it over a few cells and looked OK, but when I ran it down 85 cells in a column, by the time it had finished the checkboxes were one full cell out of sync. IE it doesn't quite centre the checkbox vertically in the centre of the cell, so there is a cumulative error as it progresses down the column. My code is currently
Sub Test() Dim Rng As Range Dim c As Range Dim Ctrl As Object Set Rng = Range("F15:F86") For Each c In Rng Set Ctrl = ActiveSheet.CheckBoxes.Add(c.Left, c.Top + 7, 20, 10#) With Ctrl .LinkedCell = c.Offset(1, 0).Address .Caption = " " End With Next c End Sub
I have tried resizing the cells to see if that helps, but it does the same thing. Any ideas.
Granddad Ray Copy the code from above. On your spreadsheet, right click on the Excel symbol top left corner, click on 'View Code' which will open a VBA window. Left hand pane should show a list of project areas, double click the sheet you want the checkboxes in and a new window opens. Paste the code into that window. Edit the range you need and click the 'Run' button (Top menu, looks like video play button). There are other ways of doing it and I'm sure people who know much more than I can tell you a better way, but thats the way I did it.