Adding a Control programatically

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.CommandButton.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
__________________________

This works for me

Sub AddButton()
Dim oWs As Worksheet
Dim oOLE As OLEObject

Set oWs = ActiveSheet

Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=237.75, Top:=21, Width:=93, Height:=22.5)

With oOLE
.Object.Caption = "Run myMacro"
.Name = "myMacro"
End With

With ThisWorkbook.VBProject.VBComponents(oWs.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", oOLE.Name) + 1, _
vbTab & "If Range(""A1"").Value > 0 Then " & vbCrLf & _
vbTab & vbTab & "Msgbox ""Hi""" & vbCrLf & _
vbTab & "End If"

End With

End Sub


--
HTH

Bob Phillips

"Richard Buttrey" <[email protected]> wrote in
message news:[email protected]...
> 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.CommandButton.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
> __________________________

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.CommandButton.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
> __________________________
>

This may be irrelevant, but I have run in to the same error message and
found I needed to write the code in VB Editor, but test it from another
location (Excel). I never bothered to figure out why, just dealt with it.

"Richard Buttrey" <[email protected]> wrote in
message news:[email protected]...
> 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.CommandButton.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
> __________________________

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"
<[email protected]> 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.CommandButton.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
__________________________

Are yhou using Excel 97?

XL97: "Can't Enter Break Mode" Stepping Through a Macro
http://support.microsoft.com/s*uppor...Q155/0/51.a*sp


--
HTH

Bob Phillips

"William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> This may be irrelevant, but I have run in to the same error message and
> found I needed to write the code in VB Editor, but test it from another
> location (Excel). I never bothered to figure out why, just dealt with it.
>
> "Richard Buttrey" <[email protected]> wrote in
> message news:[email protected]...
> > 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.CommandButton.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
> > __________________________
>
>

On Mon, 4 Jul 2005 15:57:50 +0100, "Bob Phillips"
<[email protected]> wrote:

>Are yhou using Excel 97?
>
>XL97: "Can't Enter Break Mode" Stepping Through a Macro
>http://support.microsoft.com/s*uppor...Q155/0/51.a*sp


No - Excel 2002 - SP3

Regards
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

I think that MS hasn't updated the page to include newer versions.

Richard Buttrey wrote:
>
> On Mon, 4 Jul 2005 15:57:50 +0100, "Bob Phillips"
> <[email protected]> wrote:
>
> >Are yhou using Excel 97?
> >
> >XL97: "Can't Enter Break Mode" Stepping Through a Macro
> >http://support.microsoft.com/s*uppor...Q155/0/51.a*sp
>
> No - Excel 2002 - SP3
>
> Regards
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

--

Dave Peterson

the page link does not work for me Bob, can you test it once more? maybe a
problem with my IE.

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Are yhou using Excel 97?
>
> XL97: "Can't Enter Break Mode" Stepping Through a Macro
> http://support.microsoft.com/s*uppor...Q155/0/51.a*sp
>
>
> --
> HTH
>
> Bob Phillips
>
> "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:[email protected]...
>> This may be irrelevant, but I have run in to the same error message and
>> found I needed to write the code in VB Editor, but test it from another
>> location (Excel). I never bothered to figure out why, just dealt with it.
>>
>> "Richard Buttrey" <[email protected]> wrote in
>> message news:[email protected]...
>> > 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.CommandButton.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
>> > __________________________
>>
>>
>
>

No it is the perennial Google problem.

Try this version http://support.microsoft.com/kb/q155051/

--
HTH

Bob Phillips

"William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> the page link does not work for me Bob, can you test it once more? maybe a
> problem with my IE.
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Are yhou using Excel 97?
> >
> > XL97: "Can't Enter Break Mode" Stepping Through a Macro
> > http://support.microsoft.com/s*uppor...Q155/0/51.a*sp
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> > news:[email protected]...
> >> This may be irrelevant, but I have run in to the same error message and
> >> found I needed to write the code in VB Editor, but test it from another
> >> location (Excel). I never bothered to figure out why, just dealt with
it.
> >>
> >> "Richard Buttrey" <[email protected]> wrote in
> >> message news:[email protected]...
> >> > 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.CommandButton.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
> >> > __________________________
> >>
> >>
> >
> >
>
>