Add Populated Time with airSlate SignNow

Add populated time

hello this is dr. Grande welcome to my video on adding a clock to a user form using Excel VBA as always if you find this video helpful please like it and subscribe to my channel I certainly appreciate it I have here an Excel worksheet that I'll be using for this example and right now it just has a participant's variable that's populated I'm going to be populating these other variables week 1 2 3 and this date and time variable with fictitious data using a user form that has a clock added to it I have the user form configured so that I can open it from this worksheet I have it associated with this orange rectangle here in the top right corner so if I right click here and go down to a sign macro you can see sheet 1 Okin form this will open the user form so I click on the orange rectangle and here is the user form that I'll be using and all the controls are already placed on this form so I have three text boxes one corresponding to each week I have a text box where the date and time will go the clock and enter command button start and stop command buttons as well as a close command button so right now there is no code behind any of these controls so I'm going to move over to the visual basic editor that's all f11 and you can see we have the code window for this user for this user form was constructed using the toolbox as the controls on it and I just selected the controls I wanted and place them on to the user form and then configure them using the property menus here on the left so first I want to add code that runs when the user forum is opened or initialized so on this user form I'm going to right-click and go to view code and you can see by default it places in the subroutine private sub user form click top right this drop-down arrow I'm going to change this to initialize it's actually going to add it and go back and delete the original default subroutine so under here private sub user form underscore initialize or add the code I need and this code is fairly straightforward the text boxes have a label above them and I'm setting the caption so that it equals the variable name on the worksheet that way from this view from the worksheet you can change these variable names and they will dynamically update on the user form that will only happen every time the user form is initialized next I'm going to add a subroutine I named reset form so put that below this initialize subroutine sub reset form sets the textbox values to empty and sets the focus back on the first text box then I'm going to add the code so that the values put in the text boxes associated with the 3 weeks will be placed or on to the worksheet so it will populate the worksheet so to do that I'm going to use command button 1 which is this enter command button has the caption enter so I double click that command button and under this subroutine paste this code active cell equals text box 1 value and I'm going to use offset one column at a time for textbox to 3 and the o'clock text box which will put in the date and time and then it's going to select the row below the active row so that you're ready to put in the next set of values in those weeks variables then we have reset form being called from the subroutine and of course that's this subroutine down here it clears the text boxes and resets the focus back at the xbox one outside of these subroutines I want to add a variable I'm going to declare a variable that will allow me to turn off the clock so I'm going to paste that in here up top dim X as the lien so I'll use this in a moment next I'm going to add the subroutine that controls the clock so I'll put that right here under that new variable private sub clock on a set this variable X up here to false then we have a do loop so let's do if x equals true then exits absolute if X is true this clock is going to stop otherwise clock text box equals format now comma and then we have the formatting for the date and time of course we could also just have the date for the time there's a number of configurations available here inside of date and time as well you can have the day spelled out the month spelled out the one I'm using here is just mm DD for wise the one H - MS for minute and two S's for second and a.m. p.m. again there's many options to configure the format here then do events and loop and we end the subroutine so now placing the code behind the remaining text buttons is fairly straightforward when I move back to the user form and I want to put the behind the start button so I'll double click that and that's simply call clock so I'm going to call the subroutine clock and then move back to the user form to stop the clock I just need to set the X variable to equal true so I'll double click on stop and this will just be x equals true and then to close the user form I'm going to use the close button double click on this and this will be x equals true because I want to stop the clock before I exit the user form and then the code unload me so that will unload the user form it'll close the user form so that's all the code I'm going to need for the controls on this user form I'm going to go back to the worksheet and with the way I've configured the code I want to select the first cell of the data will go into that cell b2 I'm going to open the user form using this orange rectangle and I have this set up with the start and stop button to demonstrate how this works however the code behind the start button can also be put in the initialize area so the clock is on when the userform opens and of course I already have the code from stop behind the close button I have another video that talks about how to disable this red X in the top right so the user can't close out the user form using that red X that would be a good idea here because if the user did not use this close button and use the red X that loop that controls the clock would keep running so let's try out this user form for week 1 onto a value here 50 week 255 and week 3 value 63 I'm going to start the clock here using the screen button you can see I have the date and the time as they were formatted in the code and I already have the weeks populated so I'm going to click enter and that's going to place those values in the corresponding area on this worksheet including the date and time clicking stop will stop the clock and of course that's the same code as I mentioned behind this close button X equals true and of course start will start the clock up again and of course it's going to advance to the current time so I'll close out of this using the blue close button here at the bottom and I'm just going to show you how to only add the time so we'll go back to the code view back to the user form here I want to change the configuration for this text box and that is o'clock text box and that is populated by this private sub clock so again this format here so to change this to just the time I'm just going to remove the month day and year and that comma and space just leaving the hours minutes seconds and am p.m. so now if I move back to the worksheet and open the user form when I click on start it just gives me the time if I was going to use this to populate data I would want to change this date and time variable to just time I hope you found this video on adding a clock to user form in Excel VBA to be helpful thanks for watching