spacing buttons evenly

  • Thread starter Thread starter Mitch
  • Start date Start date
M

Mitch

The code below adds buttons to a worksheet. Data is
sorted depending on which button is selected.

This works great but it doesn't look very good for the
user because all of the columns the buttons are attached
to are different widths.

Any suggestions on how to space the buttons evenly across
the page would be appreciated. Thanks for the help....

***********************************************************

Sub Add_Buttons()

Dim btn As Button, varr, varr1
Dim i As Long
Dim cell As Range

Range("A1:A11").EntireRow.Select
Selection.Insert Shift:=xlDown
Range("A1").Select

ActiveSheet.Buttons.Delete

varr = Array("Sort1", "Sort2", "Sort3")
varr1 = Array("Customer", "Branch", "Amount")

i = 0

For Each cell In Range("A3, C3, G3")

Set btn = ActiveSheet.Buttons.Add( _
Left:=cell.Left, _
Top:=cell.Top, _
Width:=125, _
Height:=30)

btn.OnAction = varr(i)
btn.Caption = varr1(i)
btn.Name = varr1(i)

i = i + 1
Next

End Sub
 
The layout of the buttons is entirely up to you.

In your code you are locating them so that they are aligned with
certain cells. Instead, you could choose to position them according to
your own design. The untested code below offers one possibility.

Sub Add_Buttons()

Dim btn As Button, varr, varr1
Dim i As Long, BtnLeft as long, BtnTop as long
Dim cell As Range

Range("A1:A11").EntireRow.Insert Shift:=xlDown
Range("A1").Select 'don't know if this is really needed

ActiveSheet.Buttons.Delete

varr = Array("Sort1", "Sort2", "Sort3")
varr1 = Array("Customer", "Branch", "Amount")

BtnLeft=range("A3").left
BtnTop=Range("A3").Top

For i=lbound(varr) to ubound(varr)
'The buttons are spaced 130 units apart, which equals _
5 more than the width of each button
Set btn = ActiveSheet.Buttons.Add( _
Left:=BtnLeft + i * 130 , _
Top:=BtnTop, _
Width:=125, _
Height:=30)

btn.OnAction = varr(i)
btn.Caption = varr1(i)
btn.Name = varr1(i)
Next i

End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top