CODEWITHSUNDEEP
excelshapesvba
Daniel Avram
Daniel Avram

Reputation: 3

Move a shape to the last row

I have a macro button assigned to a shape at the end of an Excel table. It opens a user form for adding data to the table.

The problem is that the shape does not move down with every new entry and it eventually comes to be on top of the table.

Shape is set to move with cells, but there are no actual rows inserted, just data added to the end of the table..

How can I make it move with the first empty row with Excel-VBA?

Upvotes: 0

Views: 1181

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Place this code on a Standard Module and after writing the data onto the sheet, call this code which will place the button (shape) to the first empty row. Change it as per your requirement.

Sub MoveButton()
Dim ws As Worksheet
Dim lr As Long
Dim buttonCell As Range
Dim shp As Shape
Set ws = Sheets("Sheet1")   'Sheet where shape is inserted
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1  'first empty row in column A
Set buttonCell = ws.Cells(lr, "A")  'setting the cell where button will be placed
Set shp = ws.Shapes("myShape")  'name of the shape is "myShape", change it as per your requirement
'set the shape dimensions
With shp
    .Left = buttonCell.Left
    .Top = buttonCell.Top
End With
End Sub

Upvotes: 1

Related Questions