PDA

View Full Version : Link last Checkbox in Column to Cell below



Jenst
09-16-2016, 01:06 PM
Hey Community!
I struggeled today and spend to much time trying to find the solution:banghead:. I have this code:
___________________________________________
Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim lCol As Long
lCol = 0 'number of columns to the right for link

For Each chk In ActiveSheet.CheckBoxes
With chk
.LinkedCell = _
.TopLeftCell.Offset(0, lCol).Address
End With
Next chk

End Sub
__________________________________________

This Code is linking all the Checkboxes in the ActiveSheet to the cells below. But now I want to search in the last checkbox in Column A and only apply this operation to this Checkbox. I'm able to find the last cell in Column A but I don't know how to refer to the checkbox above.
Thanks for your Help, this Forum is so great!! :yes

Jens

mana
09-16-2016, 05:26 PM
Option Explicit

Sub test()
Dim srtl As Object
Dim chk As CheckBox

Set srtl = CreateObject("System.Collections.Sortedlist")

For Each chk In ActiveSheet.CheckBoxes
srtl(chk.TopLeftCell.Row) = chk.Index
Next

With ActiveSheet.CheckBoxes(srtl.GetByIndex(srtl.Count - 1))
.LinkedCell = .TopLeftCell.Address
End With

End Sub

mana
09-16-2016, 05:34 PM
Sub test2()
Dim srtl As Object
Dim chk As CheckBox

Set srtl = CreateObject("System.Collections.Sortedlist")

For Each chk In ActiveSheet.CheckBoxes
srtl(chk.TopLeftCell.Row) = chk
Next

With srtl.GetByIndex(srtl.Count - 1)
.LinkedCell = .TopLeftCell.Address
End With

End Sub

mikerickson
09-18-2016, 02:27 PM
You could use that same code to rename the checkboxes


For Each chk In ActiveSheet.CheckBoxes
With chk
.LinkedCell = .TopLeftCell.Offset(0, 1).Address
.Name = .LinkedCell
End With
Next chk

So to refer to the check box linked to the last cell in column A, just use the address of that cell as its name.



CheckBoxes(Range("A65536").End(xlup).Address)