CODEWITHSUNDEEP
excelvba
Jejh
Jejh

Reputation: 85

VBA excel macro - add rows above button

in my Sheet I'm trying to create VBA code which will allow me to: 1)add specific (constant) number of rows above clicked button 2)format them in a proper way

What I know is every object (shape) stores value of TopLeftCell which from where I can get row number. I can simply add rows(s) above or belowe specific row in my Sheet

ActiveSheet.Rows(21).Insert shift:=xlShiftDown, CopyOrigin:=xlFormatFromLeftOrAbove

I would like to create where function addNewRows2 is unviersal for any button in the ActiveSheet - it gets the position of button(the row number) and adds row(s) above it

Private Sub CommandButton2_Click()
addNewRows2
End Sub

Function addNewRows2()
   Application.ActiveSheet.Buttons(Application.Caller).TopLeftCell.EntireRow.Insert 
End Function

Upvotes: 0

Views: 714

Answers (1)

YasserKhalil
YasserKhalil

Reputation: 9538

In Class module cButton put the following lines

Public WithEvents MyButton As MSForms.CommandButton

Private Sub MyButton_Click()
    ActiveSheet.Rows(MyButton.TopLeftCell.Row).Insert
End Sub

Then in standard module put the code

Dim TheCommandButtons() As New cButton

Sub Activate_ActiveX_CommandButtons()
    Dim shp As Shape, iButtonCount As Long
    ReDim TheCommandButtons(1 To 1)
    iButtonCount = 0
    For Each shp In ActiveSheet.Shapes
        If shp.OLEFormat.Object.OLEType = xlOLEControl Then
            iButtonCount = iButtonCount + 1
            ReDim Preserve TheCommandButtons(1 To iButtonCount)
            Set TheCommandButtons(iButtonCount).MyButton = shp.OLEFormat.Object.Object
        End If
    Next shp
End Sub

Now run the code Activate_ActiveX_CommandButtons, after that you can use any command button to do the task of inserting a row above the button.

Upvotes: 2

Related Questions