Excel click and drag check box formula

  pickle factory 16:28 19 Jun 2006
Locked

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

  pickle factory 16:34 19 Jun 2006

Sorry, forgot to mention, I already did that, and yes, it does put those in by default.

  pickle factory 17:49 19 Jun 2006

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

  pickle factory 06:47 20 Jun 2006

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

  pickle factory 09:04 23 Jun 2006

Did you really have to mention the Tenerife bit???? And yes, yet again, your wisdom is spot on, that works perfectly. Thank you.

  pickle factory 09:22 23 Jun 2006

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.

  pickle factory 09:50 23 Jun 2006

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.

  pickle factory 11:43 23 Jun 2006

Very strange! It seems it is not a cumulative error as I thought, even if I only use a range of 5 consecutive cells max (I really want around 80), and try and insert them to a range half way down the column (F45:F50), they do not sit within the cells, they again straddle the cell borders. When I insert the checkboxes to cells in F16:F20 with the above code, they look fine.
I am confused (Not that it takes much). Surely this must be something simple that I am missing.

  pickle factory 13:00 23 Jun 2006

Now getting even more confused. Depending on what % zoom I have the sheet displayed at, drastically affects the position that the checkboxes appear?????? If I run the above code on cells F46:F48 with sheet at 100% zoom, the boxes appear in the correct cells but slightly high, if I run with zoom at 95% they appear slightly low in the correct cells. Now HA HA, if I set the zoom to 97% they jump down one whole cell and sit in the cell below where they should, and at 98% one whole cell high. Good ol' Microsoft, where did I put my abacus?? Any clues anyone?

  pickle factory 13:50 23 Jun 2006

Anyone know if this code would need to be different for Office 97? (That's all we can manage at work, cheapskates) I'm at home now on Office XP and it all works perfectly, absolutely no problem.

  pickle factory 14:33 26 Jun 2006

Seems to be related to my existing spreadsheet, opened a fresh sheet and code ran OK. Hmmmmm! Bored with it now, think I'll let the mystery slide, start a fresh sheet, run code and then copy across all the rest.
Thank for the advice. Hope you had a good time in Tenerife.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Mi Mix 3 5G UK Release Date, Price & Specification

D&AD Awards 2019: Check out the Pencil winners from the year's biggest creative awards

WWDC 2019 dates and what to expect

iPhone 2019 : date de sortie, prix et autres rumeurs