I need to set a form checkbox in an Excel sheet row, to move with the row when the row heights in the sheet are reset.
I have tried various ideas I found, including .Placement = xlMoveAndSize
Sub AddCheckBoxesRange()
'https://www.contextures.com/excelcheckboxvba.html
'by Dave Peterson
'add Form checkboxes
Dim c As Range
Dim myCBX As CheckBox
Dim wks As Worksheet
Dim rngCB As Range
Dim strCap As String
Dim i As Integer
Dim CB_Number As Integer
Set wks = ActiveSheet
strCap = "" 'Caption
For i = 4 To 6
Set rngCB = wks.Range("C" & i)
CB_Number = i - 3
For Each c In rngCB
With c
Set myCBX = wks.CHECKBOXES.Add _
(Top:=.Top, Width:=.Width, _
Height:=.Height, Left:=.Left)
End With
With myCBX
.Name = "cbx_" & c.Address(0, 0)
.LinkedCell = c.Offset(0, 1) _
.Address(external:=True)
.Caption = strCap
.Placement = xlMoveAndSize
.OnAction = ThisWorkbook.Name & "!CheckBox" & CB_Number
End With
Next c
Next i
End Sub
I expect the checkboxes to move with the rows if the rows are resized. If the rows start very high, and then are made lower, the checkboxes get left behind. Thank you in advance.
Aucun commentaire:
Enregistrer un commentaire