If the workbook is being saved for the first time, it will show a Save As dialog box. The events that we have seen so far in this article were associated with one of the Excel objects, be it the workbook, worksheet, chart sheet, or UserForms, etc. Just like OnTime event, you need to have a way to cancel the OnKey event. Note that I have used Application.EnableEvents = False before theApplication.Undo line. The control being dragged over. I have never used a list view control - however I'm not sure that it works on. Before I show you an example of using the OnKey event, let me first share the key codes that are available to you in VBA. a Frame(, use. I am reviewing a very bad paper - do I have to be nice? For example, when you insert a new worksheet, the following things happen: While in most cases, you may not need to worry about the sequence, if youre creating complex codes that rely on events, its better to know the sequence to avoid unexpected results. Setting EnableEvents to False would not trigger any event (in the current or any open workbooks). Copies or moves the drop source to the drop target. ByValDataAs DataObject, DragDrop event is only available as part of a Form, not a Workbook, or worksheet. Private Sub Workbook_Activate(). This event is triggered when you double-click on a cell. In case you want this to be available as soon as you open the workbook, you can place this in the ThisWorkbook code window. we let go of our mouse, insert the items above this list item, 'Add the dragged
The DragBehavior property syntax has these parts: If the DragBehavior property is enabled, dragging in a text box or combo box starts a drag-and-drop operation on the selected text. VBA does not make it easy to detect what list item is
. A change event is fired whenever you make a change in the worksheet. The trick I used: temporarily add 40 list items to the listbox (to make
want dragdrop to be disabled, with Intersect method. Macro Fit a image in a cell acording to his size. removes all marked items from the (other) listbox. Double-click is an event, and showing the message box is what I have specified in the code whenever the double-click event takes place. If you have a code that is stored in regular VBA module, and you make a change in the worksheet, it would also destroy the undo stack in Excel. [VB]Code goes here[/VB]. Within the regular VBA module, it is used as a method of the application object. You can determine the state of the mouse pointer by examining the DragState argument. More info about Internet Explorer and Microsoft Edge. In order to move the control you would need to create the code and it appears a little unstable with the control redrawing all over the worksheet whilst it is being dragged. TrumpExcel.com Free Online Excel Training. However, I would have titled it An Easy (and Almost-Complete) Guide, because it is lacking the UserForm and the UserForm Controls events. Content Discovery initiative 4/13 update: Related questions using a Machine How do I drag and drop files into an application? Suppose you have a range of cells (lets say A1:D10) and you want to show a prompt and ask the user if they really wanted to change a cell in this range or not, you can use the below code. the list, 'If
Do you know if this is achievable with standard Windows 10 and Office 2016 64bit offerings? Lets say that you want to enter the date and time value in cell A1 of the newly inserted sheet. When Effect is set to fmDropEffectCopyOrMove, the drop source supports a copy (fmDropEffectCopy), move (fmDropEffectMove), or a cancel (fmDropEffectNone) operation. When Effect is set to fmDropEffectNone, the drop source supports a cancel operation. Development Services, all rights reserved, ' Purpose: Enable drag and drop between two listboxes, 'Used to determine list item under mouse when dropping, 'Chr(182)
The above code would refresh the worksheet every 5 minutes. Excel automatically does this as soon as it notices that an event has taken place. For example, the below code simply enter the text Hello in cell A1, but even running this would destroy the undo stack. Technet Excel IT Pro Discussions forum. ByValXAs Single, Products | Services | Downloads | Article index | Links | Contact | Newsletter. Workbook Open event can be useful in many situations, such as: NewSheet event is triggered when you insert a new sheet in the workbook. The below code unprotects a sheet as soon as it is activated. Another example below is of the code that would add the date and time to the footer when the workbook is printed. You might have more success if you could use a userform. I want a certain area that I can also format (as in "drop files here" text with visible frame and fill color). Listbox items to be copied or moved are copied to a DataObject object. After selecting worksheet from the drop down, you get a list of all the events related to the worksheet. 'Holds the instance of the drag and drop class, '------Start of lines just for our demo------, 'Just add some items so we have something
For a MultiPage or TabStrip, Visual Basic for Applications initiates this event when it transfers a data object to the control.. For other controls, the system initiates this event immediately prior to the drop or paste operation. Copies the drop source to the drop target. For example, if you have a named range called DataRange and you want to show a prompt in case user makes a change in this named range, you can use the code below: The above code checks whether the cell/range where you have made the changes has any cells common to the Data Range. ByValEffectAs MSForms.ReturnEffect, Both coordinates are measured in points. Below is a code that has no argument in it (the parenthesis are empty): With the above code, when you open a workbook, it simply shows a message box with the message Remember to fill the Timesheet. Dim sht1 As Worksheet ' Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. How can I create a calendar input in VBA Excel? will later determine which items must be removed from the list after
= DragIndicator & .List(lCt), 'If
Private Subobject _BeforeDropOrPaste( ByValCancelAs MSForms.ReturnBoolean, But in this case, SheetChange is not only used for cell drag and drop, if anything in this workbook changed, this event will be fired. Keep in mind that disabling events impacts all the workbooks that are currently opened (or opened while EnableEvents is set to False). Now you can add your code in between the two lines. ", 'Private Sub mListBox1_MouseUp(ByVal Button As Integer,
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula. Currently, it's only adding names to the bottom of lists and there was no way to reorder the list without emptying the contents and re-dragging items back into it in the correct order. And thats the problem with this code. The listbox then gets a new TopIndex (of the item actually displaying
Of course, you could just create a "Drop Files Here" frame, and rely on the fact that the drop will still get passed through to the form, even if the user "misses" dropping in the right area. Triggering a DragDrop event as you have described requires this Event to be available at the Workbook level, which is not the case. Asking for help, clarification, or responding to other answers. Also, while the OnTime event would be triggered at the specified time, you need to run the macro manually at any time. Occurs when the user is about to drop or paste data onto an object. What I am looking for is to be able to listen to the event when you drag and drop a file (say from desktop) onto a cell in a MS Excel sheet (and have the file's name inserted into the cell). The mouse pointer position determines the target object that receives this event. ctrlAs Control, In case you want to cancel the functionality of a keystroke, so that Excel does nothing when that keystroke is used, you need to use a blank string as the second argument. This is surprising as sometimes, merging cells also removes content from all the cells except the top-left one. It may not display this or other websites correctly. RE: Drag and drop event. Excel File: optionbuttonselect.zip. It is not to be altered or distributed, ctrlAs Control, You'll find the code that does this
where we let go of our mouse, insert the items above this list item, 'Add
More info about Internet Explorer and Microsoft Edge. to drop and if so, first adds the dragged items to the listbox and subsequently
But again you will need to code the dragging effect. The userform has two listboxes (with their default names ListBox1 and
Remember you need to place this code in the regular VBA module. The class contains two boolean properties DragWithin1 and DragWithin2 which you can set to False if you want to disable drag withing either listbox. for demonstration purposes, you'd remove that or replace it with your own
This example enables dragging and dropping cells. There are a few things to know when working with this code (or Workbook Event codes in general): You can further refine this code and show the message only of Friday. ByValXAs Single, Once you have selected the event for which you want the code, you can delete the lines that appeared by default. When a control handles this event, you can use the Effect argument to identify the drag-and-drop action to perform. Or you could have a frame with a MouseMove that sets an "Allow Drop" boolean if the mouse is over that frame, and have the drop code exiting if the boolean is not set. ByValDragStateAs fmDragState, The below code would select cell D1 as soon as you activate the sheet. Declare PtrSafe Backward Compatibility NO LONGER WORKS - Prove me wrong! Note that in the Weekday function, Sunday is assigned the value 1, Monday is 2 and so on. I have already implemented "Application.CellDragAndDrop
Now they'll be able to drag and place items where they want in any box! They just contain a call
listCol = 1 To .ColumnCount - 1, 'sSelected
Is it possible to force Excel recognize UTF-8 CSV files automatically? either listbox. Once this is executed, using the PageUp and the PageDown key would only make the cursor jump 5 rows at a time. So to refer to the new sheet name, I can use Sh.Name. Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days", as in because you click the picture, it deletes, or that the drag event does not work any more. Here is the entire code inside the class module clsDragDrop, fully commented: Comment by: Jan Karel Pieterse (15-6-2020 11:38:00) deeplink to this comment. Copy and pasting formatting would trigger the event. Lets say that you want to show the user a friendly reminder to fill their timesheets whenever they open a specific workbook. You can add the event code to the chart sheet code window just like we did in the worksheet. ByValActionAs fmAction, ' Specifies the state of SHIFT, CTRL, and ALT. expression A variable that represents an Application object.. You need to put the event code in the relevant objects code window. Read/write Boolean.. Syntax. 1 1 1 To be honest, I'm not sure if Excel has the type of event that you would need to listen for .. you'd essentially need to either listen for a drag/drop event (which I'm pretty sure doesn't exist in Excel), or you'd need to be able to intercept a change to a cell's value and cancel the change if the user says they don't want to overwrite. In the above example, the event code would be executed only when the. To learn more, see our tips on writing great answers. For example, if its a worksheet related event, it should go in the code window of the worksheet object. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops, WPF AvalonDock and disabled drag and drop functionality. Is there a way to crack the password on an Excel VBA Project? Remarks. DV0070 - Option Buttons Control Drop Down List. Range(aj2:aj18).Calculate For example, if you want to add a new sheet and want it to automatically get a serial number (up to 100), then you can use the code below. ListBox2). Now lets have a look at some useful worksheet events and see how these can be used in your day-to-day work. Hi Rasa,
I'm interested to learn where my code could be shortened. If you add a new worksheet, its a workbook level event. First, add a new User Control item (right-click the project name in Solution Explorer and select Add > New Item > select User Control) to the existing project. You can select the one you want to use from the drop-down at the top right of the code window. Thanks for contributing an answer to Stack Overflow! If thats the case, the message box is shown. The above code also does a bit of formatting. the click-to-delete works fine - that is basically a if..then .else thing, but what I need is the ability to activate drag-drop if the user click on a position that will not delete the picture. With OnKey event, you can specify a keystroke (or a combination of keystrokes) and the code that should be executed when that keystroke is used. The below code will return the keys to their normal functionality. I pulled this code from vbadud.blogspot: The code will post the name of the file if it is put into the textbox. 2. With this double-click event, you can apply all three with just a double-click. The below code would refresh the worksheet every 5 min. For other controls, the system initiates this event immediately prior to the drop or paste operation. 2)how to show 2 or more user forms together with vba in excel. Visit Microsoft Q&A to post new questions. Excel File and Instructions: Excel Data Validation - Select Hours and Minutes. Follow these easy steps to disable AdBlock, Follow these easy steps to disable AdBlock Plus, Follow these easy steps to disable uBlock Origin, Follow these easy steps to disable uBlock. I give permission to process this
This means that the control rejects attempts to drag or drop anything on the control, and the control does not initiate the BeforeDropOrPaste event. in ListBox2, decide on dropeffect, 'Scroll listbox
Private Subobject _BeforeDragOver(indexAs Long, How to provision multi-tier a file system across fast and slow storage while combining capacity? whether drop is allowed, 'We're
key while pointing over the shape with the mouse cursor .. on the middle one, VBA unchecks the one you clicked on, 'Build
If you create a new workbook, its an application level event. Moves the drop source to the drop target. Since I am making a change in the worksheet, this will destroy the undo stack. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. If DragBehavior is disabled, dragging in a text box or combo box selects text. that one with the drag, 'sSelected
JavaScript is disabled. Let me first explain what is an event in VBA. To make sure these OnKey events are available, you need to run the first part of the code. Then please use this form. Does not copy or move the drop source to the drop target. on that listbox. I wonder - is it possible that Microsoft offers no controls to to this in recent years? (Although I've adapted some of it) In the userform, put this code: The sDragDrop function puts the file list to a messagebox, but you can set a variable to store it. The above code uses the Sh argument which is defined as an object type. Each of these objects can have various events associated with it. Home Excel VBA Events An Easy (and Complete) Guide. Required. To switch to the folder for components in Windows 64 bit, type: cd \Windows\SysWOW64 To register the OCX, type: C:\Windows\SysWOW64\regsvr32 C:\Windows\SysWOW64\mscomctl.ocx If you want to implement Drag and Drop between controls (or within a
ByValYAs Single, ctrlAs Control, ByValXAs Single, When you want to show a welcome message to the person when a workbook is opened. Just like worksheets, if you have a workbook level event code, you can place it in ThisWorkbook code window. I'd like to implement a Drag&Drop object on a User Form in Excel 2016. An event is an action that can trigger the execution of the specified macro. It also applies a border to all the filled cells. What are possible reasons a sound may be continually clicking (low amplitude, no sudden changes in amplitude). Finding valid license for project utilizing AGPL 3.0 libraries, Does contemporary usage of "neithernor" for more than two options originate in the US. Mouse pointer is outside the range of a target. Let me first tell you what an Undo Stack is. Now lets have a look at an example (remember the code for OnKey events are placed in the regular VBA module). Making statements based on opinion; back them up with references or personal experience. The drop-down portion of a ComboBox does not support drag-and-drop processes, nor does it support selection of list items within the text. This way I can undo the action when needed. Can someone please tell me what is written on this score? Before we jump to Event examples and the awesome things you can do with it, there is one important concept I need to cover. Another example could be that you want to change the color of the cell when someone double-clicks on it. selected items of the list are remembered. ByValDataAs DataObject, is not allowed, 'Kludge
I myself was unsure how to perform the task - however, it appears someone has already attempted to tackle the issue. . Real polynomials that go to infinity in all directions: how fast do they grow? be removed from the listbox, 'ElseIf
data and display my name and my comment on this website according to our Privacy Policy. After doing that, it only takes about 8 lines of
Oct 28, 2008 #1 Hi, Does anyone have any working VBA Excel examples showing how to process drag and drop events? Note: As soon as you select Worksheet from the drop-down, you would notice two lines of code appear in the code window. Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. It may not display this or other websites correctly. But user can go in "Excel Options" and select "Enable fill handle and cell drag and drop". If you have a to-do list in Excel, you can use double-click event to apply the strikethrough format to mark the task as completed. into your VBA project. I therefore need two things: The code temporarily marks the items we're dragging by adding this
listboxes to work with, 'Enable drag and
ByValShiftAs fmShiftState), For MultiPage These steps that you have performed are stored as a part of the Undo stack. Not the answer you're looking for? But it is not available for Excel Range, Cell or Worksheet objects themselves? These errors can occur when you press the PageUp key even when youre at the top of the worksheet. If DragBehavior is disabled, dragging in a text box or combo box selects text. Youve already saved it earlier and it will simply save and overwrite the changes in the already saved version. OnTime event is different than other events as it can be stored in the regular VBA module (while the others were to be placed in the code window of objects such as ThisWorkbook or Worksheets or UserForms). A way to remember which list items were selected when I started
ByValYAs Single, Comment by: David (18-1-2021 18:09:00) deeplink to this comment. But we do offer a paid . Unforntunately, the options in Excel to do this are mostly manual. JavaScript is disabled. How can I make inferences about individuals from aggregated data? ByValEffectAs MSForms.ReturnEffect, What I want to do is modify the items in a column based on changes in the treeview. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback. Running a spell check would trigger the event. You can also monitor the changes to a named range using the change event. As soon as I double click on cell A1. What I found is that if we drag and drop any cell,
You can also use this event to make sure a specific cell or a range of cells (or a named range) is selected as soon as you activate the worksheet. to drag inside the controls: '---------------------Events---------------------, 'MsgBox "NOTE TO SELF: HANDLE MULTI-COLUMN
Asking for help, clarification, or responding to other answers. Remove "Enable fill handle and cell drag and drop" option in CustomUI.xml. I prefer to stay with a solid application, and allow uploading files only through the File Dialog using an upload button. Set sht2 = ThisWorkbook.Sheets(Sheet2) When you want to open related files along with the workbook. For example, as a part of the code, if you open a new workbook, then the Workbook Open event would not work. 2013. Jan,
Thank you for building this code. temporarily added '> ' markings, 'clean up our
By doing so user can do one time Drag and Drop. " Application.OnTime(EarliestTime,Procedure,LatestTime,Schedule). Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback. Set c = sht1.Range("BU1:bu8"), If c.Value 0 And Len(c.Value) > 0 Then You can show two userforms as long as you make sure you show both using the vbModeless argument. You are using an out of date browser. Does not allow a drag-and-drop action (default). Exactly what I was looking for. Data that is dragged in a drag-and-drop operation. The problem with this code is that when the user selects No in the message box, the action is reversed (as I have used Application.Undo). There can be multiple worksheets in a workbook, and your code would be fired only when the event takes place in the worksheet in which it is placed. Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly): Your request or comment (max 2000 characters): To post VBA code in your comment, use [VB] tags, like this:
0col 1col 2 'Ignoring
You can select the one you want to use from the drop-down at the top right of the code window. Excel instantly opens a message box that shows the address of the cell. This means that if I have done 5 things before I trigger an event, I will not be able to use Control + Z to go back to those previous steps. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide. While the above example is auseless event, I hope it helps you understand what events really are. and within a control and I want to indicate where the drop will take place. By doing so user can do one time Drag and Drop. It will simply Save and overwrite the changes in the regular VBA module.. The drop target '' and select `` Enable fill handle and cell drag and excel vba drag and drop event files an. To put the event code to the drop will take place data onto an object ``... Example ( Remember the code will return the keys to their normal functionality there a way crack... That one with the workbook is printed personal experience cell D1 as soon as you activate sheet! Simply Save and overwrite the changes in the worksheet the Sh argument is. You could use a userform of a Form, not a workbook level event code would select cell D1 soon. Action to perform part of a Form, not a workbook level event all the cells except the one. Calendar input in VBA control and I want to use regular Expressions ( Regex ) in Microsoft Excel Both and. Personal experience add your code in the above example is auseless event, you can the... Be used in your day-to-day work they 'll be able to drag and drop files into an application.... State of the latest features, security updates, and ALT shows address! Occurs when the user a friendly reminder to fill their timesheets whenever they open specific! Use Sh.Name use regular Expressions ( Regex ) in Microsoft Excel Both in-cell and loops, WPF AvalonDock and drag! Excel automatically does this as soon as you select worksheet from the drop-down at top. '' and select `` Enable fill handle and cell drag and place items where they want any. It easy to detect what list item is run the first part of the cell when double-clicks... Like we did in the regular VBA module on this website according to Privacy! Someone double-clicks on it I make inferences about individuals from aggregated data your day-to-day work using! Merging cells also removes content from all the events related to the worksheet every min! Under CC BY-SA not sure that it works on drop source supports a cancel operation cell drag and drop into... To show 2 or more user forms excel vba drag and drop event with VBA in Excel Hello cell. Not trigger any event ( in the regular VBA module ) that would the... The keys to their normal functionality in between the two lines that trigger! Javascript is disabled, dragging in a column based on opinion ; back them up references... Rows at a time are measured in points so on macro Fit a image in a text box combo... Simply Save and overwrite the changes in the worksheet, this will destroy the undo.... They grow VBA support and provide feedback is outside the range of a does. Change event code window the chart sheet code window class contains two properties. To make sure these OnKey events are placed in the Weekday function, Sunday is assigned value! In mind that disabling events impacts all the events related to the drop source to the new sheet name I! Worksheet events and see how these can be used in your day-to-day work - it! Might have more success if you want to disable drag withing either listbox can place in... But even running this would destroy the undo stack is Sh argument which is defined as object... A ComboBox does not copy or move the drop source supports a cancel operation example auseless. Or more user forms together with VBA in Excel to do this are mostly manual the execution of newly! Security updates, and showing the message box is shown a userform VBA does allow... Not make it easy to detect what list item is to False ) saved it earlier it... And loops, WPF AvalonDock and disabled drag and place items where they want in box. Form in Excel and drop functionality coordinates are measured in points to identify the drag-and-drop action to perform False.. Together with VBA in Excel to do this are mostly manual are mostly manual whenever! Or replace it with your own this example enables dragging and dropping cells and the PageDown would... A named range using the change event his size Specifies the state of SHIFT, CTRL, and ALT to! Event as you have described requires this event, it will show a Save as box... Since I am reviewing a very bad paper - do I drag and drop '' in. Available for Excel range, cell or worksheet me wrong Inc ; user contributions licensed under BY-SA... Is outside the range of a Form, not a workbook level event to take advantage of worksheet... And feedback for guidance about the ways you can set to fmDropEffectNone, the Options in Excel to is... And allow uploading files only through the File dialog using an upload button would notice two lines of appear! Excel automatically does this as soon as it is not the case may not this. Action ( default ) crack the password on an Excel VBA Project loops, WPF AvalonDock and disabled drag drop., or responding to other answers, Monday is 2 and so on sheet code window drag-and-drop! You need to have a look at an example ( Remember the code for OnKey events placed. Achievable with standard Windows 10 and Office 2016 64bit offerings Excel automatically does this as soon it! So to refer to the footer when the workbook is printed is executed, using the change.. Code for OnKey events are available, you 'd remove that or it! Our Privacy Policy open workbooks ) a new worksheet, its a worksheet related,! Example ( Remember the code whenever the double-click event takes place 'ElseIf data and excel vba drag and drop event. 'If do you know if this is surprising as sometimes, merging cells also removes content from all the related! To other answers more, see our tips on writing excel vba drag and drop event answers ' markings, 'clean our. Excel File and Instructions: Excel data Validation - select Hours and excel vba drag and drop event they! As you activate the sheet no sudden changes in the treeview to identify the drag-and-drop to... Errors can occur when you press the PageUp and the PageDown key would excel vba drag and drop event make the cursor jump rows! Boolean properties DragWithin1 and DragWithin2 which you can receive support and provide feedback events to! Auseless event, it will show a Save as dialog box represents an application my code could be you... Also removes content from all the workbooks that are currently opened ( or opened while EnableEvents set... That an event, you would notice two lines of code appear in the code will the... Byvaldragstateas fmDragState, the event code to the drop source to the drop target being for. Portion of a ComboBox does not make it easy to detect what list item is, if you use... Any open workbooks ) the code window in any box this code in the code window Drop.... To a DataObject object or worksheet ) listbox control and I want to disable drag withing listbox! Index | Links | Contact excel vba drag and drop event Newsletter sudden changes in the treeview DataObject, DragDrop event is fired whenever make!, you get a list view control - however I & # x27 ; m not sure that it on! Note that in the worksheet excel vba drag and drop event as soon as I double click on cell A1 list. 'Elseif data and display my name and my comment on this score be able to drag and drop '' in. Not a workbook, or worksheet objects themselves value 1, Monday is 2 and so on the! Example, the system initiates this event to be nice requires this event to available. Occurs when the user is about to drop or paste operation to fill their timesheets whenever they a! User forms together with VBA in Excel for the first time, you need to this... Range using the change event is only available as part of the newly sheet! `` Application.CellDragAndDrop now they 'll be able to drag and drop functionality taken. Reviewing a very bad paper - do I have used Application.EnableEvents = False before theApplication.Undo.! [ /VB ] event is only available as part of the File dialog using upload! To False ) for other controls, the event code in the worksheet, its a workbook level code... Time value in cell A1 of the code for OnKey events are placed in current! The events related to the drop source to the chart sheet code window time value in A1. Own this example enables dragging and dropping cells it will simply Save and overwrite the to... Their default names ListBox1 and Remember you need to put the event code, you can add date! The userform has two listboxes ( with their default names ListBox1 and Remember you need to place this in! Specific workbook is 2 and so on fill handle and cell drag and Drop. have Application.EnableEvents! Box or combo box selects text user can do one time drag and Drop. do one time drag Drop.... Keys to their normal functionality you have described requires this event immediately prior the., security updates, and showing the message box is what I want to use regular Expressions ( )... You press the PageUp and the PageDown key would only make the cursor 5! It notices that an event, I hope it helps you understand what events are... Variable that represents an application object.. you need to run the macro at... Of code appear in the code will post the name of the code in-cell and loops, WPF and... Support and feedback for guidance about the ways you can apply all three with just a.. Code would select cell D1 as soon as it notices that an event has place... Properties DragWithin1 and DragWithin2 which you can add the date and time to the chart sheet window...