Draw Buttons

[junx13]

Hi...
I was wondering if there was any way I could programmaticall
draw a button (command or form), setting its size and position in
macro

 

[ste mac]

Hi junx13, l can help you a bit.. the code below will draw a button and
then assign a macro to it, Add(484.5, 34.5, 111, 25.5)is the button sizes
have a mess round with it...

Option Explicit

Sub makebutton()
ActiveSheet.Buttons.Add(484.5, 34.5, 111, 25.5).Select
Selection.OnAction = "assignmacro"
End Sub
Sub assignmacro()
MsgBox "Button Made..."
End Sub

hth

seeya ste

 

[Dave Peterson]

You could even loop through a range of cells:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim myBTN As Button

Set wks = ActiveSheet
With wks
.Buttons.Delete 'remove existing buttons???
Set myRng = .Range("a1:a10")
For Each myCell In myRng.Cells
With myCell
Set myBTN = .Parent.Buttons.Add(0, 0, 0, 0)
myBTN.Height = .Height
myBTN.Width = .Width
myBTN.Left = .Left
myBTN.Top = .Top
myBTN.Name = "BTN_" & .Address(0, 0)
myBTN.OnAction = ThisWorkbook.Name & "!myBTNmacro"
myBTN.Caption = "Click Me"
End With
Next myCell
End With
End Sub
Sub myBTNMacro()
Dim myBTN As Button

Set myBTN = ActiveSheet.Buttons(Application.Caller)
With myBTN
MsgBox .TopLeftCell.Address(0, 0) & vbLf & .Name & vbLf & .Caption
End With

End Sub