View Full Version : Creating button by offsetting space

11-10-2015, 02:45 AM

I'm using this to create a button with an assigned macro

Sub CreateButton()
ActiveSheet.Buttons.Add(199.5, 20, 81, 36).Select
Selection.Name = "New Button"
Selection.OnAction = "MoveValue"
ActiveSheet.Shapes("New Button").Select
Selection.Characters.Text = "Check Totals"
End Sub

However, I want to create the button multiple times, each time offsetting from the previous button running down the Y axis. Essentially I want something like End(xlUp).Offset(1), something that recognises that the space is already taken by another button.

Many Thanks

11-10-2015, 03:29 AM
maybe something like this

Sub CreateButton()
Dim i&
With ActiveSheet
i = .Shapes.Count
With .Buttons.Add(199.5, 20 + 46 * i, 81, 36)
.Name = "New Button" & Format(i, "00")
.OnAction = "MoveValue"
.Characters.Text = "Check Totals " & Format(i, "00")
End With
End With
End Sub

11-10-2015, 04:02 AM
I'm going to have multiple buttons on the page already so that doesn't quite solve the problem. Different buttons are going to be generated in different areas of the sheet, so this will offset these buttons by too much