Excel VBA Button Anchor-CodePudding

I have multiple buttons that are to the right of different tables, all stacked vertically. when I press the button it adds a new row to the top of the table and shifts the other rows down - exactly what i want. However it seems that the buttons are not moving with the first row of the tables so after a few clicks of each, everything is misaligned. how can i keep my buttons anchored to the first rows of their respective tables.

this is my code:

Private Sub CommandButton1_Click()
Dim mySheets
    Dim i As Long
    
    mySheets = Array("Highland")
    
    For i = LBound(mySheets) To UBound(mySheets)
        With Sheets(mySheets(i))
            .Range("OP_DATE").EntireRow.Insert Shift:=xlDown
            .Range("OP_DATE:lineOP").Borders.Weight = xlThin
        End With
    Next i
End Sub

Private Sub CommandButton2_Click()
Dim mySheets
    Dim i As Long
    
    mySheets = Array("Highland")
    
    For i = LBound(mySheets) To UBound(mySheets)
        With Sheets(mySheets(i))
            .Range("P_DATE").EntireRow.Insert Shift:=xlDown
            .Range("P_DATE:lineP").Borders.Weight = xlThin
        End With
    Next i
End Sub

Private Sub CommandButton3_Click()
Dim mySheets
    Dim i As Long
    
    mySheets = Array("Highland")
    
    For i = LBound(mySheets) To UBound(mySheets)
        With Sheets(mySheets(i))
            .Range("S_DATE").EntireRow.Insert Shift:=xlDown
            .Range("S_DATE:lineS").Borders.Weight = xlThin
        End With
    Next i
End Sub

CodePudding user response：

You may need to play around with this but it should be pretty close. If you are adding multiple rows you will need to multiple the return of the function by that number.

This function gets the row height and column width in twips.

Public Function GetRowColumnTwips(r As Long, c As Long) As Variant
    Dim twips(1) As Long
    
    twips(0) = Rows(r).Height
    twips(1) = Columns(c).Width 'I put this in if you need it but I'm not actually using it here
    
    GetRowColumnTwips = twips
End Function

This sub uses the row height value to keep its place on the sheet.

Sub Button2_Click()
    Dim twips As Variant
    With Sheet1
        twips = GetRowColumnTwips(.Shapes(Application.Caller).TopLeftCell.Row, .Shapes(Application.Caller).TopLeftCell.Column)
        .Shapes(Application.Caller).Top = .Shapes(Application.Caller).Top - twips(0)
    End With
End Sub

You can drop the button event sub code into any of your button codes, the only thing that needs to change is the sheet in the With statement.

In your first button's code:

Private Sub CommandButton1_Click()
Dim mySheets
    Dim i As Long
    Dim twips As Variant
    mySheets = Array("Highland")
    
    For i = LBound(mySheets) To UBound(mySheets)
        With Sheets(mySheets(i))
            .Range("OP_DATE").EntireRow.Insert Shift:=xlDown
            .Range("OP_DATE:lineOP").Borders.Weight = xlThin
            twips = Module1.GetRowColumnTwips(.Shapes(Application.Caller).TopLeftCell.Row, .Shapes(Application.Caller).TopLeftCell.Column)
            .Shapes(Application.Caller).Top = .Shapes(Application.Caller).Top - twips(0)
        End With
    Next i
End Sub

For ActiveX Controls you'll need to refer to each button by name instead of relying upon the calling shape.

twips = Module1.GetRowColumnTwips(.OLEObjects("CommandButton1").TopLeftCell.Row, .OLEObjects("CommandButton1").TopLeftCell.Column)
.OLEObjects("CommandButton1").Top = .OLEObjects("CommandButton1").Top - twips(0)