VBA excel macro - add rows above button

Question

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

What is your question / what is the issue?

BigBen
– BigBen

2020-07-27 17:34:24 +00:00
Commented Jul 27, 2020 at 17:34 — BigBen
– BigBen, Commented Jul 27, 2020 at 17:34

YasserKhalil · Accepted Answer · 2020-07-27 17:59:01Z

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.

Collectives™ on Stack Overflow

VBA excel macro - add rows above button

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related