mardi 16 juillet 2019

How to set Form Checkbox to move with cell when row is resized?

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