Make a cell into a Macro button

[Al]

How do I turn a specfic cell into a button that when
pressed runs a specific macro?


Thx in Advance,
Al

 

[Al]

I'm trying to make it idiot ... errr ... Manager proof.

 

[Arvi Laanemets]

Hi

Insert the button from Forms toolbar (it's easier to use than one from
Control Toolbox) into worksheet, assign the macro to it, place the over cell
and adjust, and set it to 'Move and Size with cells'

 

[AA]

Bad idea, I think, because
1. it is not obvious what needs to be done to trigger the
macro; the cell looks like any other cell.
2. if rows or columns are inserted, the position of
the 'button' cell changes; its address needs to be trapped-
-see below.

BUT, if you must,

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As
Range, Cancel As Boolean)
If Target.Address = "$C$3" Then abc
Cancel = True
End Sub

Sub abc()
MsgBox "Macro can run here"
End Sub

If C3 is double clicked then the macro abc will be run.

 

[Al]

Sweetness!

Thx MUCH!!

-----Original Message-----
Hi

Insert the button from Forms toolbar (it's easier to use than one from
Control Toolbox) into worksheet, assign the macro to it, place the over cell
and adjust, and set it to 'Move and Size with cells'


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets





.

 

[Paul Robinson]

Hi
This will do it on cell A1 in the active sheet

Sub MakeButton()
Dim myButton as Button

ActiveWindow.Zoom = 100 'if not, there may be an alignment problem
when date buttons are clicked
With Activesheet.Range("A1")
Set myButton = ActiveSheet.Buttons.Add (.Left, .Top, .Width,
..Height)

With myButton 'change properties as required
.Placement = xlMove
.Caption = "mytext"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = xlUpward
.OnAction = "myMacro"
End With 'myButton
End With ' 'Range "a1"
'Scroll to the button
Application.GoTo Reference:=Activesheet.Range("A1") _
, Scroll:=True
Set myButton = Nothing
end sub

regards
Paul