Thanks Patrick,

If I wanted say a ListBox (with pre-determined values), instead of a
Button, how would the code alter?



Regards



On Mon, 4 Jul 2005 05:30:02 -0700, "Patrick Molloy"
wrote:

use a "Forms" command button instead of teh ActiveX version
This code adds a button, gives it a caption and assigns th ecode to run


Sub AddFormsButton()
' AddFormsButton
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Buttons.Add(94.5, 11.25, 109.5, 30.75).Select
With Selection
.OnAction = "MyProcedure"
.Characters.Text = "Show a message"
End With
End Sub
Private Sub MyProcedure()
msbox "ok"
End Sub


"Richard Buttrey" wrote:

Hi,

I have some VBA code which opens up a .txt file, hacks the formatting
around a bit deleting superfluous stuff, and leaves the user with a
nice neat database.

I recorded the following code to add a command button to the
worksheet, with the intention of adding some further code behind the
button.

Sub AddButton

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=237.75, Top:=21, Width:=93, _
Height:=22.5).Select
Range("a1").Select

End Sub

When I test this by playing it back in the VBA window, it halts with
the error message "Can't enter break mode at this time", Continue End
Help and the usual Debug button is greyed out.
If I select 'End', the button appears on the worksheet in Edit Mode,
but I can't procede with any more code in the subroutine

First of all can someone tell me what's wrong with the code, and then
secondly, assuming I can succesfully place a button on the worksheet,
how can I add some code programatically to the button object - or
indeed any other control?

Usual TIA




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________