Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Events to Existing Collection of Events
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Events to Existing Collection of Events
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.CodeNa me) 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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Events to Existing Collection of Events
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 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.CodeNa me) 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Events to Existing Collection of Events
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 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.CodeNa me) 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 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Events to Existing Collection of Events
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 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.CodeNa me) 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 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Events to Existing Collection of Events
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 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 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.CodeNa me) 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 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add New Events to Existing Collection of Events
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 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 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.CodeNa me) 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 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Class Collection - trapped events keep repeating! | Excel Programming | |||
do events? background events | Excel Programming | |||
On Events | Excel Programming | |||
events | Excel Programming | |||
Need some events... | Excel Programming |