Excel click and drag check box formula

  pickle factory 16:28 19 Jun 06
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

  fitshase 16:31 19 Jun 06

Look in the formula for the original cell. If there are $ signs, remove them as this tells Excel it is an absolute reference and will not replicate for cells in order.

Regards

Fitshase

  pickle factory 16:34 19 Jun 06

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

  pickle factory 17:49 19 Jun 06

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 06

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

  VoG II 16:16 22 Jun 06

c.Left, c.Top are setting the position.

Try

c.left+10, c.top-6

  VoG II 16:17 22 Jun 06

Gretings from Tenerife by the way :o))

  pickle factory 09:04 23 Jun 06

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

  Granddad Ray 09:19 23 Jun 06

Very interested in adopting this as its application would be useful to me. Unfortunately I have no idea where to enter the formula.how do I do it

  pickle factory 09:22 23 Jun 06

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 06

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.

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

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…