Video: How Do I: Add Controls to a Document Surface at Runtime?

Presenter:Steve Hansen

Steve Hansen explains how to add controls to a document surface at runtime and then attach event handlers to them.


The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
By my name is Steve Hanson with Visio tips as videos how to edit control to a document surface at runtime when the today s greater new Excel project in Visual Basic for Excel 2007 and I want to demonstrate today how you can add controls to the surface of a worksheet at runtime one of forcing some to do before I get into the actual code to do the just add a ribbon to the project and then just add a button
Minute 1
Riven also used assignment office image ID will make the button a larger size extra minute move over into sheet one and view the code there and want to do is when you click on the button in the riven dissent dynamically add a daytime picker to the active cell and she won
Minute 2
Smith s greatest love for the procedure to do that go back over to the riven and finished the code over here syndrome although the revenue will go into actual code to do this or that is assuming you some objects from a couple of different namespace is something ago had an import does when he is devious to go namespace and assign it to the alias tools and then use these system collections namespace
Minute 3
Is going to use a list object to what I could do in the ad control procedure is first of all new need to come up with a name for the control so I can reference it later in one minute do is just use a simplistic indexing scheme and to do that or go out and create a class level very well for my index and initialize it to the value of one so my name and to speed the daytime picker and then whatever the value of the index is
Minute 4
Solicits a real simplistic implementation is not my grandmother checking for the existence of that name already but if you re in a different legacy going ahead and do that some the other night and at a big time ticker to the document surface someone declare a variable to hold a reference to the daytime picker so noticed that this daytime picker comes from tools Excel controls rather than the daytime victory would use it on a Windows form so the one that is in the tools namespace is got a few extra bits to allow you to embedded on the surface of a document or a spreadsheet than I will go ahead and at the daytime picker
Minute 5
To do this I can mean here is keep in mind the sheet one link and use controls and then there s a numerous methods here to set the controls so one freeze type of control that you can add so the reason that he uses method which this comes from VB SCO namespace minister runtime is that when you embed a one form control on say a spreadsheet actually happens as there is an ActiveX wrapper and invisible ActiveX wrapper or container few willingness embedded on the spreadsheet and then he went form control goes and said that ActiveX wrapper so these methods provided by the establishment of a lot easier to get these controls onto the worksheet Thursday at a time picker
Minute 6
And you pass in the active cell as deranged as should contain the control and then provide my name Saddam does make sure that the mean in fact sticks so many to add an event handler which I will come back to us whether to do is to demonstrate how you d actually use the daytime could run a worksheet cells one thing and that it on the worksheet but if you can actually refer to the value from within calculations or to drive other things on your worksheet is not of much value human destroyer at that eye to the worksheet and keep it in
Minute 7
Think with the value of the actual control self administer than an event handler here in or indeed any are then used the antiwar statement and I want to handle the value changed event in an fast and the address of my daytime figure value changed
Minute 8
Procedure and I seem to do is create a printed list with us to keep track of all of the daytime pictures that have been added to the current worksheet so this will be a list of tools excel controls date Time picker said it equal to a new list to going back down to eat at control procedure after I greatly control also going to add it to the list so most to us
Minute 9
Daytime pickers as a class level variable at the Met to avoid any unexpected garbage collection if I would just have a procedural level scope variable soul saved each time Vickers and then added and then add the current instance of my daytime figure selection and it do is just provide the mechanism to transfer the value from the control onto the worksheet to be before I do that when you run it and I can better demonstrate what I mean by that survivor of the evidence then click add and edit it
Minute 10
Pecker which were cited for a few I use a State from other cells in the worksheet there s no good way to do that you can t just click in and then click on the daytime figure and get the value so there s no real good way to get the value so what I want to do is take a value message transfer to the worksheet and then keep it in sync and have a good way to do that so of the ways I can go about doing that is known is that the control sizes as I changed the size of the column would like to do is put the value October 24 2007 actually put it in the cell F7 here most of the reform of our there s no value here so there s no value for cell F7 the deep temperature control is actually above
Minute 11
Of the cell is not embedded in the cell so well do here is a man create a him from you name the reins in the cell that the daytime to her appears above the Minnesota equal to the same name of the set equal to the same name that I gave the daytime picker than the other sink the values set the short date string in the final thing I need to do here is just advanced
Minute 12
My index them in the daytime picker value chain event handler whenever you change the value in the daytime picker need to make sure update the cell containing the value so to retrieve the reins that contains value since I ve named it the same as the name of the daytime picker can declare a Range object
Minute 13
And to get that range use me arrange an impasse in the name of the daytime picker in the sink the value of the reins with the value of the daytime picker so now when I had the control those in the form of our region have the date here before now but it appears there I go back to that some of us you see the name of the named range daytime picker in the valley of the cell so that I ll if I want to refer to that date in formula
Minute 14
And it equals actually navigate over to the cell that looks like I ve navigated to the daytime picker that enter and I get the value of the day Michael as I can add multiple controls your suppose is daytime picker one daytime picker to appear and it s really easy to use those daytime picker is in and use them within the working four concludes a study of a point out one thing about craning controls at runtime set when you create these controls at runtime these controls are not persisted with
Minute 15
Is saving close workbook survey say the worksheet and close a one reopened in my control appears here valued or least VM image of it but functionality associated with the troll is a soap to do at controls at runtime and you want to process them and also need to implement some way to rebuild those controls when the workbook is reopened for for example you might want to take that list which I ve used here for event handling say that either in worksheet or within the he cast associated with the document
Minute 16
And then when you reopen the workbook rebuild the controls in the hookup event handler is a pretty good way to go about doing that that concludes this video on how to add controls to a document surface at runtime thanks for watching everything

Click here to go to original video page