Adding a command button

[Roy Thompson]

I am trying to add a command button to the face of my
spreadsheet then insert some code behind it.

This is the code I am using:

' add the button
Worksheets(1).OLEObjects.Add, _
ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=6, Top:=4, _
Width:=50, Height:=20

Worksheets(1).OLEObjects.BringToFront
' and add the code for emailing
strQuote = Chr$(34)
strEmail = "(e-mail address removed)"
strSubject = "Outstanding Purchase Orders"
Code = "Sub CommandButton1_Click()" & vbCrLf
Code = Code & "On error resume next" & vbCrLf
Code = Code & "ThisWorkbook.SendMail Recipients:=" &
strQuote & strEmail & strQuote & ", Subject:=" & strQuote
& strSubject & strQuote & vbCrLf
Code = Code & "End Sub"
With Application.Workbooks(1).VBProject.VBComponents
(1).CodeModule
NextLine = .CountOfLines + 1
'.InsertLines NextLine, Code
End With

I have commented out the 'Insert Lines' line since this
seems to screw up some virus checkers.

The point is that it doesn't get this far. When I run the
code I get an error saying 'Unable to enter break mode at
this time' followed by 'Object does not support this
method' at the point that I am trying to add the button.

The code to add the button was taken from the VBA Help!!

What am I doing wrong??

Thanks in Advance

Roy Thompson

 

[Guest]

This might be a stupid suggestion depending on how you intend to use this
button but why create it dynamically. Why not just toggle the visible
property of a button that you have already created. Its a lot easier if you
can get away with it. I use it all the time

I don't know if this helps, but it might be worth a try...

 

[Roy Thompson]

Jim

Thanks for that but unfortunately I am creating the entire
spreadsheet dynamically each time the sheet is opened -
hence the need to add the button also dynamically.

Roy