Excel VBA Button Anchor

Question

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

Warcupine · Accepted Answer · 2021-09-13 18:04:22Z

1

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)

edited Sep 13, 2021 at 18:04

answered Sep 13, 2021 at 13:12

Warcupine

4,7203 gold badges18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

SSerb1989 Over a year ago

Thank you for your help so far - I'm brand new to this, where exactly should i drop the above function sub into?

Warcupine Over a year ago

The function you can place in a module or the sheet's codebehind doesn't really matter where that is, the sub you can place the code after you insert the row(s). Since you already have a With statement you really just need the Dim and the two lines within the With

SSerb1989 Over a year ago

Thank You! I put the function into a seperate module, but now I'm getting a Runtime error saying the item with the specified name wasnt found and when i debug it takes me to Twips = line of the button sub. any additional help would be greatly appreciated

Warcupine Over a year ago

Ohh you may need to do Module1.GetRowColumnTwips Or whatever the Module name is, and then put a Public in front of the Function definition.

SSerb1989 Over a year ago

I think Im getting closer! my function is in Module 2 so I made it a public function and modified the twips statement: twips = Module2.GetRowColumnTwips(.Shapes(Application.Caller).TopLeftCell.Row, .Shapes(Application.Caller).TopLeftCell.Column) But im still getting the same error

|

Collectives™ on Stack Overflow

Excel VBA Button Anchor

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related