PDA

View Full Version : VBA Code to link checkboxes to certain columns



fightothend1
06-29-2017, 06:39 AM
Hello fourms!

First id like to say my vba skills are very limited and new. Next my issue is this:I have three columns E(insufficient QTY) F(Too Slow) and G(Not Listed) They all have checkboxes in them. I need to link E to H F to I G to JThe following code works nicely if there was only 1 column of checkboxes but I don't know how to improve the code to run by checkboxes in a certain column. Right now it just searches the entire sheet for checkboxes and links them to the desired column.

Sub LinkChecks()
'Update 20150310
i = 2
For Each cb In ActiveSheet.CheckBoxes
cb.LinkedCell = Cells(i, G).Address
i = i + 1
Next cb
End Sub

mdmackillop
06-29-2017, 06:57 AM
Try

For Each cb In ActiveSheet.CheckBoxes
X = cb.TopLeftCell.Column
cb.LinkedCell = Cells(i, X).Offset(, 3).Address
i = i + 1
Next cb

fightothend1
06-29-2017, 07:05 AM
That kind of worked the code itself worked but where it linked then checkboxes to didn't work i only have 300 checkboxes in each column E,F,G and G2 is linked to J650 I have no idea how that happened.

mdmackillop
06-29-2017, 07:44 AM
Shape orders are not always apparent
Does this do what you're after?

For Each cb In Activesheet.CheckBoxes
cb.LinkedCell = cb.TopLeftCell.Offset(, 3).Address
Next cb

fightothend1
06-29-2017, 07:50 AM
As far as I can tell that did, in fact, work ill do some further testing.Thank you mdmackillop my boss was set of having this excel sheet have checkboxes haha.