PDA

View Full Version : Creating button by offsetting space



bull699
11-10-2015, 02:45 AM
Hi

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

nilem
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

bull699
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