creating buttons with a macro
 

Once a user starts macro1, it should create 5 buttons
during the macro and then assign a different macro to each
button.

For example, once macro1 is finished, there is a
spreadsheet with Buttons 1 - 5 across the top.

Button 1 has a macro to sort by date
Button 2 has a macro to sort by amount
Button 3 has a macro to sort by Customer Number.

I'm having trouble creating the buttons with a macro and
assigning the macro to each button. I also need help in
placing the buttons evenly across the spreadheet.

Any help you can provide to get me started would be
greatly appreciated. Thanks.

creating buttons with a macro
 

Option Base 0

Sub AddButtons()
Dim btn As Button, varr, varr1
Dim i as Long
Dim cell as Range
Application.ScreenUpdating = False
' to remove previously added buttons -
ActiveSheet.Buttons.Delete
varr = Array("Macro1", "Macro2", "Macro3", _
"Macro4", "Macro5")
varr1 = Array("Date", "Amount", "Cus Num", _
"Other1", "Other2")

i = 0
For Each Cell In Range("A2:E2")
Set btn = ActiveSheet.Buttons.Add( _
Left:=Cell.Left, _
Top:=Cell.Top, _
Width:=Cell.Width, _
Height:=Cell.Height)
btn.OnAction = varr(i)
btn.Caption = varr1(i)
btn.Name = varr1(i)
i = i + 1
Next

Application.ScreenUpdating = True
End Sub

Sub Macro1()
MsgBox Application.Caller
End Sub
Sub Macro2()
MsgBox Application.Caller
End Sub
Sub Macro3()
MsgBox Application.Caller
End Sub
Sub Macro4()
MsgBox Application.Caller
End Sub
Sub Macro5()
MsgBox Application.Caller
End Sub

This uses buttons from the forms toolbar, but these are a lot easier to work
with for what you are trying to do.

--
Regards,
Tom Ogilvy

mike wrote in message
...
Once a user starts macro1, it should create 5 buttons
during the macro and then assign a different macro to each
button.

For example, once macro1 is finished, there is a
spreadsheet with Buttons 1 - 5 across the top.

Button 1 has a macro to sort by date
Button 2 has a macro to sort by amount
Button 3 has a macro to sort by Customer Number.

I'm having trouble creating the buttons with a macro and
assigning the macro to each button. I also need help in
placing the buttons evenly across the spreadheet.

Any help you can provide to get me started would be
greatly appreciated. Thanks.

creating buttons with a macro
 

Hi Mike

Try this macro to create a button (Sheet1 C3) and code in the click event

Sub test()
Dim WS As Worksheet
Dim Btn As OLEObject
Set WS = ThisWorkbook.Worksheets("Sheet1")
With WS
Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1" , _
Left:=.Range("C3").Left, Top:=.Range("C3").Top, _
Width:=100, Height:=30)
End With
Btn.Object.Caption = "Click Me"
Btn.Name = "TheButton"
With ThisWorkbook.VBProject.VBComponents(WS.CodeName).C odeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
"Msgbox ""Hi there"" "
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"mike" wrote in message ...
Once a user starts macro1, it should create 5 buttons
during the macro and then assign a different macro to each
button.

For example, once macro1 is finished, there is a
spreadsheet with Buttons 1 - 5 across the top.

Button 1 has a macro to sort by date
Button 2 has a macro to sort by amount
Button 3 has a macro to sort by Customer Number.

I'm having trouble creating the buttons with a macro and
assigning the macro to each button. I also need help in
placing the buttons evenly across the spreadheet.

Any help you can provide to get me started would be
greatly appreciated. Thanks.

creating buttons with a macro
 

This works great but I have one question. This puts a
button in each cell in the range. Can I put a button in
every other cell and increase the range.

Also can you explain the Msgbox Application.Caller code.
I'm not sure I understand what this is trying to do.
Thanks for all of the help...

-----Original Message-----
Option Base 0

Sub AddButtons()
Dim btn As Button, varr, varr1
Dim i as Long
Dim cell as Range
Application.ScreenUpdating = False
' to remove previously added buttons -
ActiveSheet.Buttons.Delete
varr = Array("Macro1", "Macro2", "Macro3", _
"Macro4", "Macro5")
varr1 = Array("Date", "Amount", "Cus Num", _
"Other1", "Other2")

i = 0
For Each Cell In Range("A2:E2")
Set btn = ActiveSheet.Buttons.Add( _
Left:=Cell.Left, _
Top:=Cell.Top, _
Width:=Cell.Width, _
Height:=Cell.Height)
btn.OnAction = varr(i)
btn.Caption = varr1(i)
btn.Name = varr1(i)
i = i + 1
Next

Application.ScreenUpdating = True
End Sub

Sub Macro1()
MsgBox Application.Caller
End Sub
Sub Macro2()
MsgBox Application.Caller
End Sub
Sub Macro3()
MsgBox Application.Caller
End Sub
Sub Macro4()
MsgBox Application.Caller
End Sub
Sub Macro5()
MsgBox Application.Caller
End Sub

This uses buttons from the forms toolbar, but these are a
lot easier to work
with for what you are trying to do.

--
Regards,
Tom Ogilvy

mike wrote in
message
...
Once a user starts macro1, it should create 5 buttons
during the macro and then assign a different macro to
each
button.

For example, once macro1 is finished, there is a
spreadsheet with Buttons 1 - 5 across the top.

Button 1 has a macro to sort by date
Button 2 has a macro to sort by amount
Button 3 has a macro to sort by Customer Number.

I'm having trouble creating the buttons with a macro and
assigning the macro to each button. I also need help in
placing the buttons evenly across the spreadheet.

Any help you can provide to get me started would be
greatly appreciated. Thanks.


.