[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Display rows even when sum is 0.

aiu4840

3/27/2007 10:01:00 PM

Hello SQL gurus,

I have a query as follows:

SELECT DISTINCT
P.PlantDescription,
'SETUP' AS TransactionType,
SUM(MT.ActualMinutes * MT.Crewsize)/60 AS [SETUP run labor
hours]
FROM dbo.GLB_Plant_MASTER P
LEFT OUTER JOIN GLB_BusinessUnit_MASTER B ON
P.DepartmentNumber_ID =B.DepartmentNumber_ID
LEFT OUTER JOIN GLB_MESTransaction MT ON B.BusinessUnit_ID =
MT.BusinessUnit
WHERE (MT.TransactionType = "SETUP")
GROUP BY
P.PlantDescription

that generates the following result set:
PlantDescription TransactionType SETUP run labor hours
Food Bar SETUP 2686.850000
Natural Products SETUP 4284.283333
Packaging SETUP 29388.383333
Powder Mix SETUP 2541.866666
Powder Packaging SETUP 488.183333
Tablet Manufacturing SETUP 48876.983333

Then I modified this query to include the date range in the second
LEFT OUTER JOIN as follows:

SELECT DISTINCT
P.PlantDescription,
'SETUP' AS TransactionType,
SUM(MT.ActualMinutes * MT.Crewsize)/60 AS [SETUP run labor
hours]
FROM dbo.GLB_Plant_MASTER P
LEFT OUTER JOIN GLB_BusinessUnit_MASTER B ON
P.DepartmentNumber_ID = B.DepartmentNumber_ID
LEFT OUTER JOIN GLB_MESTransaction MT ON B.BusinessUnit_ID =
MT.BusinessUnit AND (convert(varchar,MT.TransactionDateTime,101) >=
'03/28/2007') AND (convert(varchar,MT.TransactionDateTime,101) <=
'03/28/2007')
WHERE (MT.TransactionType = "SETUP")
GROUP BY
P.PlantDescription

The result set for this query is blank since there isn't any data for
this date of '03/28/2007'. What I'd like to have the query do is to
display the following result set when there's no data for a specified
date range:

PlantDescription TransactionType SETUP run labor hours
Food Bar SETUP 0.00
Natural Products SETUP 0.00
Packaging SETUP 0.00
Powder Mix SETUP 0.00
Powder Packaging SETUP 0.00
Tablet Manufacturing SETUP 0.00

How can I approach solving this problem?

Thanks for your help/suggestion.

Dien
5 Answers

Mr Tea

3/27/2007 10:12:00 PM

0

Hi There

your LEFT OUTER JOIN is correct, the only thing you missed is the 'WHERE
(MT.TransactionType = "SETUP")' which is filtering out the records that
would have otherwise been preserved.

if you shift that to the ON clause for your join, that should do the trick:

SELECT DISTINCT
P.PlantDescription,
'SETUP' AS TransactionType,
SUM(MT.ActualMinutes * MT.Crewsize)/60 AS [SETUP run labor
hours]
FROM dbo.GLB_Plant_MASTER P
LEFT OUTER JOIN GLB_BusinessUnit_MASTER B ON
P.DepartmentNumber_ID = B.DepartmentNumber_ID
LEFT OUTER JOIN GLB_MESTransaction MT ON B.BusinessUnit_ID =
MT.BusinessUnit AND (convert(varchar,MT.TransactionDateTime,101) >=
'03/28/2007') AND (convert(varchar,MT.TransactionDateTime,101) <=
'03/28/2007') AND (MT.TransactionType = "SETUP")
GROUP BY
P.PlantDescription

one final comment on your query would be; dont convert your columns in
comparisons as this renders them unsargable, instead maniplate the
constants/parameters so that the query optimiser can have more options to
work its magic:

SELECT DISTINCT
P.PlantDescription,
'SETUP' AS TransactionType,
SUM(MT.ActualMinutes * MT.Crewsize)/60 AS [SETUP run labor
hours]
FROM dbo.GLB_Plant_MASTER P
LEFT OUTER JOIN GLB_BusinessUnit_MASTER B ON
P.DepartmentNumber_ID = B.DepartmentNumber_ID
LEFT OUTER JOIN GLB_MESTransaction MT ON B.BusinessUnit_ID =
MT.BusinessUnit AND MT.TransactionDateTime >= cast('03/28/2007' as
datetime) AND MT.TransactionDateTime <
cast('04/29/2007' as datetime) AND (MT.TransactionType = "SETUP")
GROUP BY
P.PlantDescription

Regards
Lee


"aiu4840" <khongdoc@earthlink.net> wrote in message
news:1175032838.409635.110180@y80g2000hsf.googlegroups.com...
> Hello SQL gurus,
>
> I have a query as follows:
>
> SELECT DISTINCT
> P.PlantDescription,
> 'SETUP' AS TransactionType,
> SUM(MT.ActualMinutes * MT.Crewsize)/60 AS [SETUP run labor
> hours]
> FROM dbo.GLB_Plant_MASTER P
> LEFT OUTER JOIN GLB_BusinessUnit_MASTER B ON
> P.DepartmentNumber_ID =B.DepartmentNumber_ID
> LEFT OUTER JOIN GLB_MESTransaction MT ON B.BusinessUnit_ID =
> MT.BusinessUnit
> WHERE (MT.TransactionType = "SETUP")
> GROUP BY
> P.PlantDescription
>
> that generates the following result set:
> PlantDescription TransactionType SETUP run labor hours
> Food Bar SETUP 2686.850000
> Natural Products SETUP 4284.283333
> Packaging SETUP 29388.383333
> Powder Mix SETUP 2541.866666
> Powder Packaging SETUP 488.183333
> Tablet Manufacturing SETUP 48876.983333
>
> Then I modified this query to include the date range in the second
> LEFT OUTER JOIN as follows:
>
> SELECT DISTINCT
> P.PlantDescription,
> 'SETUP' AS TransactionType,
> SUM(MT.ActualMinutes * MT.Crewsize)/60 AS [SETUP run labor
> hours]
> FROM dbo.GLB_Plant_MASTER P
> LEFT OUTER JOIN GLB_BusinessUnit_MASTER B ON
> P.DepartmentNumber_ID = B.DepartmentNumber_ID
> LEFT OUTER JOIN GLB_MESTransaction MT ON B.BusinessUnit_ID =
> MT.BusinessUnit AND (convert(varchar,MT.TransactionDateTime,101) >=
> '03/28/2007') AND (convert(varchar,MT.TransactionDateTime,101) <=
> '03/28/2007')
> WHERE (MT.TransactionType = "SETUP")
> GROUP BY
> P.PlantDescription
>
> The result set for this query is blank since there isn't any data for
> this date of '03/28/2007'. What I'd like to have the query do is to
> display the following result set when there's no data for a specified
> date range:
>
> PlantDescription TransactionType SETUP run labor hours
> Food Bar SETUP 0.00
> Natural Products SETUP 0.00
> Packaging SETUP 0.00
> Powder Mix SETUP 0.00
> Powder Packaging SETUP 0.00
> Tablet Manufacturing SETUP 0.00
>
> How can I approach solving this problem?
>
> Thanks for your help/suggestion.
>
> Dien
>


sarndt

4/2/2010 6:53:00 PM

0

Thanks Chip - I went out and read your Event articles on your website. But
didn't see any of this covered.

Some more information and a couple of questions.

1. I have control toolbar frames on my worksheet that I've added events for
in a sheet module. Each frame consists of a textbox and spinbutton control.
I've added the events for both controls in the frame to the collection which
then gets used in a class module. All works fine.
2. In the worksheet_change event, I dynamically create a frame in a cell
offset based on the value of the currently selected cell. The frame also
consists of a textbox and spinbutton control. I can access the controls but
can't use the events in the class module to edit/control the control values.
3. Originally I thougth I could just re-initalize the collection and the
events for the newly added control would be included in the collection. But
this doesn't appear to work.
4. Will the events added using the Extensibility Library be included in the
collection so that I can use the normal class events to control both the
original and dynamic controls? If not, is there another way to do this?
5. Do I need to use coding similar to the CreateEventProc in your code for
every event I want to setup?

Thanks

Steve

P.S. After I add the dynamic control, the control will work using the class
events if I stop and restart the macro. This would make sense, since the
control is not part of the collection at runtime. Not sure if this helps...


"Chip Pearson" wrote:

> I forgot the declaration for VBP. Add
>
> Dim VBP As VBIDE.VBProject
>
> to the existing declarations.
>
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional,
> Excel, 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> On Fri, 02 Apr 2010 11:35:30 -0500, Chip Pearson <chip@cpearson.com>
> wrote:
>
> >Steve,
> >
> >First add a reference to the VBA Extensibility Library. In VBA, go to
> >the Tools menu, choose References, and scroll down to and check the
> >item "Microsoft Visual Basic For Applications Extensibility Library
> >5.3". Then, use code like the following:
> >
> >Sub AAA()
> > Dim VBComp As VBIDE.VBComponent
> > Dim CodeMod As VBIDE.CodeModule
> > Dim L As Long
> > Dim Ctrl As OLEObject
> >
> > Set Ctrl = Worksheets("Sheet1").OLEObjects("CommandButton1")
> > Set VBP = ThisWorkbook.VBProject
> > Set VBComp = VBP.VBComponents(Ctrl.TopLeftCell.Worksheet.CodeName)
> > Set CodeMod = VBComp.CodeModule
> > L = CodeMod.CreateEventProc("Click", Ctrl.Name)
> > CodeMod.InsertLines L + 1, " Msgbox ""Hello World"""
> >End Sub
> >
> >This adds the following to the code module for Sheet1
> >
> >Private Sub CommandButton1_Click()
> > MsgBox "Hello World"
> >End Sub
> >
> >
> >For lots more information about creating and working with VBA objects
> >via code, see www.cpearson.com/Excel/VBE.aspx .
> >
> >Cordially,
> >Chip Pearson
> >Microsoft Most Valuable Professional,
> > Excel, 1998 - 2010
> >Pearson Software Consulting, LLC
> >www.cpearson.com
> >
> >
> >
> >
> >
> >
> >On Fri, 2 Apr 2010 09:00:01 -0700, sarndt
> ><sarndt@discussions.microsoft.com> wrote:
> >
> >>When I dynamically add controls to a worksheet that already has controls on
> >>it, how do I modify the collection to include the new events?
> >>
> >>Thanks
> >>
> >>Steve
> .
>

sarndt

4/2/2010 7:38:00 PM

0

One more question - If I still need to create each event in the collection
individually, I assume this code will only be needed/used when the object is
created. And that the next time, the macro is run on worksheet
startup/initialization, the object will be referenced as a design-time object
- not a run-time object.

Thanks

Steve

"sarndt" wrote:

> Thanks Chip - I went out and read your Event articles on your website. But
> didn't see any of this covered.
>
> Some more information and a couple of questions.
>
> 1. I have control toolbar frames on my worksheet that I've added events for
> in a sheet module. Each frame consists of a textbox and spinbutton control.
> I've added the events for both controls in the frame to the collection which
> then gets used in a class module. All works fine.
> 2. In the worksheet_change event, I dynamically create a frame in a cell
> offset based on the value of the currently selected cell. The frame also
> consists of a textbox and spinbutton control. I can access the controls but
> can't use the events in the class module to edit/control the control values.
> 3. Originally I thougth I could just re-initalize the collection and the
> events for the newly added control would be included in the collection. But
> this doesn't appear to work.
> 4. Will the events added using the Extensibility Library be included in the
> collection so that I can use the normal class events to control both the
> original and dynamic controls? If not, is there another way to do this?
> 5. Do I need to use coding similar to the CreateEventProc in your code for
> every event I want to setup?
>
> Thanks
>
> Steve
>
> P.S. After I add the dynamic control, the control will work using the class
> events if I stop and restart the macro. This would make sense, since the
> control is not part of the collection at runtime. Not sure if this helps...
>
>
> "Chip Pearson" wrote:
>
> > I forgot the declaration for VBP. Add
> >
> > Dim VBP As VBIDE.VBProject
> >
> > to the existing declarations.
> >
> >
> > Cordially,
> > Chip Pearson
> > Microsoft Most Valuable Professional,
> > Excel, 1998 - 2010
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> >
> >
> > On Fri, 02 Apr 2010 11:35:30 -0500, Chip Pearson <chip@cpearson.com>
> > wrote:
> >
> > >Steve,
> > >
> > >First add a reference to the VBA Extensibility Library. In VBA, go to
> > >the Tools menu, choose References, and scroll down to and check the
> > >item "Microsoft Visual Basic For Applications Extensibility Library
> > >5.3". Then, use code like the following:
> > >
> > >Sub AAA()
> > > Dim VBComp As VBIDE.VBComponent
> > > Dim CodeMod As VBIDE.CodeModule
> > > Dim L As Long
> > > Dim Ctrl As OLEObject
> > >
> > > Set Ctrl = Worksheets("Sheet1").OLEObjects("CommandButton1")
> > > Set VBP = ThisWorkbook.VBProject
> > > Set VBComp = VBP.VBComponents(Ctrl.TopLeftCell.Worksheet.CodeName)
> > > Set CodeMod = VBComp.CodeModule
> > > L = CodeMod.CreateEventProc("Click", Ctrl.Name)
> > > CodeMod.InsertLines L + 1, " Msgbox ""Hello World"""
> > >End Sub
> > >
> > >This adds the following to the code module for Sheet1
> > >
> > >Private Sub CommandButton1_Click()
> > > MsgBox "Hello World"
> > >End Sub
> > >
> > >
> > >For lots more information about creating and working with VBA objects
> > >via code, see www.cpearson.com/Excel/VBE.aspx .
> > >
> > >Cordially,
> > >Chip Pearson
> > >Microsoft Most Valuable Professional,
> > > Excel, 1998 - 2010
> > >Pearson Software Consulting, LLC
> > >www.cpearson.com
> > >
> > >
> > >
> > >
> > >
> > >
> > >On Fri, 2 Apr 2010 09:00:01 -0700, sarndt
> > ><sarndt@discussions.microsoft.com> wrote:
> > >
> > >>When I dynamically add controls to a worksheet that already has controls on
> > >>it, how do I modify the collection to include the new events?
> > >>
> > >>Thanks
> > >>
> > >>Steve
> > .
> >

Chip Pearson

4/2/2010 8:30:00 PM

0

You are probably raising some confusion by thinking of events as
belonging to some sort of collection to and from which events are
added and removed, or that can be somehow reset or refreshed. Events
don't really make up any kind of collection. If a procedure with the
correct name exists in one of several places that Excel looks for
event code, Excel executes the code.

You can't get all the events from a TextBox when it is referenced
tangentially in another object module. A TextBox is a bit of an
oddball in this respect. Almost all the other controls expose all
their events when instantiated in another object module. TextBox does,
and I have no idea why that is the case.

Creating code via the Extensibility library is the same as if you
typed in the code manually. When you save the workbook, the newly
created event procedures will be saved. Therefore, you should
probably check whether the event proc already exists before creating a
new one. Otherwise, you'll get compiler errors.

Broadly speaking, I recommend against using code to modify its own
VBProject, especially if that code is to modify its own VBComponent.
Sometime, the new code will not be available and recognized until VBA
decides to recompile the code. Sometimes you can force it with

Application.VBE.Command Bars.FindControl(ID:=578)
If .Enabled = True Then
.Execute
.Execute ' yes, call it twice
End If
End With

but this is not a foolproof method.

You might be better off manually creating the event procedures before
hand and then adding controls dynamically at run time. It causes no
error and no problems if you have event code for an object that
doesn't yet exist.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Fri, 2 Apr 2010 11:53:01 -0700, sarndt
<sarndt@discussions.microsoft.com> wrote:

>Thanks Chip - I went out and read your Event articles on your website. But
>didn't see any of this covered.
>
>Some more information and a couple of questions.
>
>1. I have control toolbar frames on my worksheet that I've added events for
>in a sheet module. Each frame consists of a textbox and spinbutton control.
>I've added the events for both controls in the frame to the collection which
>then gets used in a class module. All works fine.
>2. In the worksheet_change event, I dynamically create a frame in a cell
>offset based on the value of the currently selected cell. The frame also
>consists of a textbox and spinbutton control. I can access the controls but
>can't use the events in the class module to edit/control the control values.
>3. Originally I thougth I could just re-initalize the collection and the
>events for the newly added control would be included in the collection. But
>this doesn't appear to work.
>4. Will the events added using the Extensibility Library be included in the
>collection so that I can use the normal class events to control both the
>original and dynamic controls? If not, is there another way to do this?
>5. Do I need to use coding similar to the CreateEventProc in your code for
>every event I want to setup?
>
>Thanks
>
>Steve
>
>P.S. After I add the dynamic control, the control will work using the class
>events if I stop and restart the macro. This would make sense, since the
>control is not part of the collection at runtime. Not sure if this helps...
>
>
>"Chip Pearson" wrote:
>
>> I forgot the declaration for VBP. Add
>>
>> Dim VBP As VBIDE.VBProject
>>
>> to the existing declarations.
>>
>>
>> Cordially,
>> Chip Pearson
>> Microsoft Most Valuable Professional,
>> Excel, 1998 - 2010
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> On Fri, 02 Apr 2010 11:35:30 -0500, Chip Pearson <chip@cpearson.com>
>> wrote:
>>
>> >Steve,
>> >
>> >First add a reference to the VBA Extensibility Library. In VBA, go to
>> >the Tools menu, choose References, and scroll down to and check the
>> >item "Microsoft Visual Basic For Applications Extensibility Library
>> >5.3". Then, use code like the following:
>> >
>> >Sub AAA()
>> > Dim VBComp As VBIDE.VBComponent
>> > Dim CodeMod As VBIDE.CodeModule
>> > Dim L As Long
>> > Dim Ctrl As OLEObject
>> >
>> > Set Ctrl = Worksheets("Sheet1").OLEObjects("CommandButton1")
>> > Set VBP = ThisWorkbook.VBProject
>> > Set VBComp = VBP.VBComponents(Ctrl.TopLeftCell.Worksheet.CodeName)
>> > Set CodeMod = VBComp.CodeModule
>> > L = CodeMod.CreateEventProc("Click", Ctrl.Name)
>> > CodeMod.InsertLines L + 1, " Msgbox ""Hello World"""
>> >End Sub
>> >
>> >This adds the following to the code module for Sheet1
>> >
>> >Private Sub CommandButton1_Click()
>> > MsgBox "Hello World"
>> >End Sub
>> >
>> >
>> >For lots more information about creating and working with VBA objects
>> >via code, see www.cpearson.com/Excel/VBE.aspx .
>> >
>> >Cordially,
>> >Chip Pearson
>> >Microsoft Most Valuable Professional,
>> > Excel, 1998 - 2010
>> >Pearson Software Consulting, LLC
>> >www.cpearson.com
>> >
>> >
>> >
>> >
>> >
>> >
>> >On Fri, 2 Apr 2010 09:00:01 -0700, sarndt
>> ><sarndt@discussions.microsoft.com> wrote:
>> >
>> >>When I dynamically add controls to a worksheet that already has controls on
>> >>it, how do I modify the collection to include the new events?
>> >>
>> >>Thanks
>> >>
>> >>Steve
>> .
>>

sarndt

4/2/2010 9:21:00 PM

0

You're right - what I was thinking is wrong. Maybe, I'm trying to overkill
this since I can't get it to work...Here's what I got so far:

1. I have a worksheet that has control toolbox frames on it - each with a
textbox and spinbutton control in the frame. When the worksheet is
activated, it calls the InitalizeEvents sub of the sheet module. For example:

For Each objcontrol In ActiveSheet.OLEObjects

If TypeName(objcontrol.Object) = "Frame" Then
Set clsEvents = New CTimeControl
Set clsEvents.TBControl = objcontrol.Object.Controls(0) - textbox
Set clsEvents.SBControl = objcontrol.Object.Controls(1) -
spinbutton
mcolEvents.Add clsEvents
End If
Next

In the class module, I have:
Private WithEvents TB As MSForms.TextBox
Private WithEvents SB As MSForms.SpinButton

Public Property Set TBControl(objNewTB As MSForms.TextBox)
Set TB = objNewTB
End Property
Public Property Set SBControl(objNewSB As MSForms.SpinButton)
Set SB = objNewSB
End Property

This all works fine and I'm able to work with all of the events of all of
the controls that are designed on the worksheet using TB and SB appropriately.

In the Worksheet_Change event, when the user picks a paticular value from
the list, I want to dynamically add a frame with the same controls and then
ue the TB and SB events to do the same type of updating and editing. For
example:

Set objframe =
ActiveSheet.OLEObjects.Add(classtype:="Forms.Frame.1").Object
Set objTextBox = objframe.Controls.Add("Forms.Textbox.1")
Set objSpinButton = objframe.Controls.Add("Forms.Spinbutton.1")
objframe.Activate
With objframe

This adds the frame and controls and I can access them. But the events
don't work. What I'm struggling to figure out is how to get the TB and SB
events to work with the dynamic control.

Steve

"Chip Pearson" wrote:

> You are probably raising some confusion by thinking of events as
> belonging to some sort of collection to and from which events are
> added and removed, or that can be somehow reset or refreshed. Events
> don't really make up any kind of collection. If a procedure with the
> correct name exists in one of several places that Excel looks for
> event code, Excel executes the code.
>
> You can't get all the events from a TextBox when it is referenced
> tangentially in another object module. A TextBox is a bit of an
> oddball in this respect. Almost all the other controls expose all
> their events when instantiated in another object module. TextBox does,
> and I have no idea why that is the case.
>
> Creating code via the Extensibility library is the same as if you
> typed in the code manually. When you save the workbook, the newly
> created event procedures will be saved. Therefore, you should
> probably check whether the event proc already exists before creating a
> new one. Otherwise, you'll get compiler errors.
>
> Broadly speaking, I recommend against using code to modify its own
> VBProject, especially if that code is to modify its own VBComponent.
> Sometime, the new code will not be available and recognized until VBA
> decides to recompile the code. Sometimes you can force it with
>
> Application.VBE.Command Bars.FindControl(ID:=578)
> If .Enabled = True Then
> .Execute
> .Execute ' yes, call it twice
> End If
> End With
>
> but this is not a foolproof method.
>
> You might be better off manually creating the event procedures before
> hand and then adding controls dynamically at run time. It causes no
> error and no problems if you have event code for an object that
> doesn't yet exist.
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional,
> Excel, 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> On Fri, 2 Apr 2010 11:53:01 -0700, sarndt
> <sarndt@discussions.microsoft.com> wrote:
>
> >Thanks Chip - I went out and read your Event articles on your website. But
> >didn't see any of this covered.
> >
> >Some more information and a couple of questions.
> >
> >1. I have control toolbar frames on my worksheet that I've added events for
> >in a sheet module. Each frame consists of a textbox and spinbutton control.
> >I've added the events for both controls in the frame to the collection which
> >then gets used in a class module. All works fine.
> >2. In the worksheet_change event, I dynamically create a frame in a cell
> >offset based on the value of the currently selected cell. The frame also
> >consists of a textbox and spinbutton control. I can access the controls but
> >can't use the events in the class module to edit/control the control values.
> >3. Originally I thougth I could just re-initalize the collection and the
> >events for the newly added control would be included in the collection. But
> >this doesn't appear to work.
> >4. Will the events added using the Extensibility Library be included in the
> >collection so that I can use the normal class events to control both the
> >original and dynamic controls? If not, is there another way to do this?
> >5. Do I need to use coding similar to the CreateEventProc in your code for
> >every event I want to setup?
> >
> >Thanks
> >
> >Steve
> >
> >P.S. After I add the dynamic control, the control will work using the class
> >events if I stop and restart the macro. This would make sense, since the
> >control is not part of the collection at runtime. Not sure if this helps...
> >
> >
> >"Chip Pearson" wrote:
> >
> >> I forgot the declaration for VBP. Add
> >>
> >> Dim VBP As VBIDE.VBProject
> >>
> >> to the existing declarations.
> >>
> >>
> >> Cordially,
> >> Chip Pearson
> >> Microsoft Most Valuable Professional,
> >> Excel, 1998 - 2010
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >>
> >> On Fri, 02 Apr 2010 11:35:30 -0500, Chip Pearson <chip@cpearson.com>
> >> wrote:
> >>
> >> >Steve,
> >> >
> >> >First add a reference to the VBA Extensibility Library. In VBA, go to
> >> >the Tools menu, choose References, and scroll down to and check the
> >> >item "Microsoft Visual Basic For Applications Extensibility Library
> >> >5.3". Then, use code like the following:
> >> >
> >> >Sub AAA()
> >> > Dim VBComp As VBIDE.VBComponent
> >> > Dim CodeMod As VBIDE.CodeModule
> >> > Dim L As Long
> >> > Dim Ctrl As OLEObject
> >> >
> >> > Set Ctrl = Worksheets("Sheet1").OLEObjects("CommandButton1")
> >> > Set VBP = ThisWorkbook.VBProject
> >> > Set VBComp = VBP.VBComponents(Ctrl.TopLeftCell.Worksheet.CodeName)
> >> > Set CodeMod = VBComp.CodeModule
> >> > L = CodeMod.CreateEventProc("Click", Ctrl.Name)
> >> > CodeMod.InsertLines L + 1, " Msgbox ""Hello World"""
> >> >End Sub
> >> >
> >> >This adds the following to the code module for Sheet1
> >> >
> >> >Private Sub CommandButton1_Click()
> >> > MsgBox "Hello World"
> >> >End Sub
> >> >
> >> >
> >> >For lots more information about creating and working with VBA objects
> >> >via code, see www.cpearson.com/Excel/VBE.aspx .
> >> >
> >> >Cordially,
> >> >Chip Pearson
> >> >Microsoft Most Valuable Professional,
> >> > Excel, 1998 - 2010
> >> >Pearson Software Consulting, LLC
> >> >www.cpearson.com
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >On Fri, 2 Apr 2010 09:00:01 -0700, sarndt
> >> ><sarndt@discussions.microsoft.com> wrote:
> >> >
> >> >>When I dynamically add controls to a worksheet that already has controls on
> >> >>it, how do I modify the collection to include the new events?
> >> >>
> >> >>Thanks
> >> >>
> >> >>Steve
> >> .
> >>
> .
>