Draw Buttons

[junx13]

Hi Dave,
Thanks for all the help.( Same to you, ste mac )
What I would like is to write code that, like the buttons code :

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.Name = "BTN_" & .Address(0, 0)
myBTN.OnAction = ThisWorkbook.Name & "!myBTNmacro"
myBTN.Caption = "Click Me"
End With
Next myCell
End With
End Sub

Except that I would like the button to look like an image file. I
there a property like myBtn.Image or something like that? Or do I hav
to add an image that is clickable? If so, how do I do it

 

[Guest]

hi,
yes there is a property. the Picture property.
but i am not sure about the code syntax.

 

[Guest]

hi,

try something like this:
CommandButton1.Picture = _
LoadPicture("c:\windows\argyle.bmp")

 

[Dave Peterson]

I'd just use the picture. All of the cells get the same picture?

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim myPict As Picture
Dim myPictName As String

myPictName = "C:\My Documents\My Pictures\clouds.jpg"

Set wks = ActiveSheet
With wks
.Pictures.Delete 'remove existing pictures???
Set myRng = .Range("a1:a10")
For Each myCell In myRng.Cells
With myCell
Set myPict = .Parent.Pictures.Insert(myPictName)
myPict.Height = .Height
myPict.Width = .Width
myPict.Left = .Left
myPict.Top = .Top
myPict.Name = "Pict_" & .Address(0, 0)
myPict.OnAction = ThisWorkbook.Name & "!myPictmacro"
End With
Next myCell
End With
End Sub
Sub myPictMacro()
Dim myPict As Picture

Set myPict = ActiveSheet.Pictures(Application.Caller)
With myPict
MsgBox .TopLeftCell.Address(0, 0) & vbLf & .Name
End With

End Sub


You could even set up an array of picture names and cycle through them.