Adding buttons with macros

[mike]

This code 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...


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

 

[Trevor Shuttleworth]

Mike

one way:

For Each cell In Range("A2,C2,E2,G2,I2")
Set btn = ActiveSheet.Buttons.Add( _
:
:

Regards

Trevor

 

[Tom Ogilvy]

Application.Caller returns the name of the button that called the routine.
I just put msgbox application.Caller into the macros that are assigned to
each button so you would get some response - it was only a demo, not
required.

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:I2")
if cell.column mod 2 = 1 then
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
End if
Next

Application.ScreenUpdating = True
End Sub

Regards,
Tom Ogilvy

 

[Bob Phillips]

Mike,

Here is one way

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*2, _
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

Application.Caller returns the name of the button, that is why Tom used that
code, so you could see that which button had been pressed.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[mike]

Thanks. Just what the doctor ordered....

-----Original Message-----
Mike

one way:

For Each cell In Range("A2,C2,E2,G2,I2")
Set btn = ActiveSheet.Buttons.Add( _
:
:

Regards

Trevor





.

 

[Shatin]

Hi all,

I have successfully adapted the following code to insert buttons into
a new file from my macro in the personal.xls file. Two questions:

1. I actually only need two buttons at H2 and J2. While I have managed
to add the buttons by changing "For Each Cell In Range("A2:E2")" to
"For Each cell In Range("H2,J2"), how could I have done it without
using the "For Each" structure?

2. I have also successfully added two macros, "Macro1" and "Macro2"
into the new file programmatically from my macro in the personal.xls
file. However, when I click the buttons, Excel says it can't find
"Macro1" and "Macro2" in the personal file. I don't want Excel to look
for the macros in personal.xls but the file itself, especially since I
am going to share the file with my friends. How can I do this?

Any help will be much appreciated.