UserForm with Multiple Database Sheet (Transfer Data to Worksheet on Condition)

[Music] hello everyone welcome back you were watching the data laughs my name is Dilip in this video we are going to learn how to create a customer call log form so let's move to excel window first and then see their example which I have already created and thereafter we will create that form from a scratch so here you can see that this is the customer log call log form suppose you are taking a call and you just need to enter some information like customer need ID customer name country product and call somebody then and once you click on submit button so the main advantage of this form is like on the basis of country selection you you will submit the data to dad a specific country sheet like you can see that in this country drop-down we have India China United Kingdom Germany Japan and France so in the same way we have already created like India China United Kingdom Germany Japan and France so these six different countries are over there right and all these six countries are available in this drop-down so whenever any of the user while technique all will select any of the country like supposed user will select the country as India and fill all the details and submit the data so it means this particular data is associated with this particular country that is India so entire data that is associated with a specific country that will goes to that will go to a that a specific worksheet like India so winds once you will select their country and submit the data yet I will get transferred to India once you select the country a China then data will be transferred to China and so on right so let's see the example so I am going to enter to one create customer ID and customer name mr. Kumar let's select the ideas no sorry country as India and product.i I added some fictitious product like one two three four five let's select one and customer problem like the cold summaries there is is some okay so we have entered some information and that information is related with India so let's select a click on submit so before click on submit let's move to data specifically that in India you can see that there is no data here right so let's move to a form click on submit and once you submit it will confirm the ok data has been submitted let's move to India in India a specific and you can see that there is one entry minute by user that is associated with or the India and that's why it is reflecting over here let's move to form again enter suppose two three four and this specific like customer ID like mister think and assuming this this particularly is associated with the country China so let's select China and the product is product number three and this is from China right and let's click on submit it is submitted now let's go to India you can see that there is only one entry let's move to China now you can see that there is one entry of level for China so let's again making three four you know of two two three four again this is mister its thing now this time this is associated with the Germany so let's select product as product pipe and this is from Germany right click on submit now you can see that it has been submitted let's move to Germany and you can see that this specific record has been transferred to in Germany worksheet so in this way you can dynamically transfer you data to be a strict are no specific worksheet and this is the example right so you can apply this type of technique signore other like creating a data entry form for other purpose right and this will be very very useful because he data hat is segregating basis on the selection user selection here we have applied the condition to move the data basis on the country section you can use any of like maybe product maybe features or anything right so in this in the same way you can create a data entry form and let will be totally dynamic and bases on some like and data will get transfers on transfer from one form to a worksheet basis on some criteria okay so let's start developing this form from scratch so before doing that let's close this save this form and press ctrl W ok now create a new worksheet so press ctrl N and now you can see that a totally blank working to be is available press ctrl s to save this workbook and give the designated path okay now give the name as customer Paul Log form okay maybe version 1.0 you can to segregate the previous form and the new form become make sure you should select the macro enabled file otherwise it will not work so you know here you can see that I have selected xlsm as a extension right so now we need to create a form so let's create a form and then we will add a different worksheet like the 6 to different worksheet for a country name so first of all let's create a form so to create a form just double click on Home tab to explain the ribbon and just go to view and hide the grid lines so you can see that on our grid lines have been disappeared now now we need to create a create the header section for call customer call log form so let's select c2 - n3 okay c22 n3 just go to home and Merchant Center okay and apply the fill color adds this one blue and right there like give the header as customer all logged form okay now select this range and change the font as let's Cambria select Cambria okay and size would be maybe 26 or 28 26 is good enough and the phone close should be white okay and apply the blue border mystical in Bordeaux section just click on this one bottom border okay so now it's done let's give the date range over here so date let's date and go to cell formatting and this one again border okay leave the key column here just make it a little bit shorter and now just select this range from here to here and go to a Home tab and then Merchant Center and then again middle line right and go to then Home tab in cell formatting select the node right and enter the formula has to be okay that's it now we need to create the fill or so let's select a b7 - excellent right so this is d7 to abdomen in hometown like click on merge and center and then go to cell formatting sailor style and input right and again sailor style and give us header I drink to now enter the heading as the first fill is customer customer ID right select the font color as black now let the this column G Z column and select the edge to M and Merchants enter again ok and now go to a style and flick the stylus you note okay now check the phone now we can see that this is 13 let's make it one ball and again increase the sizes 13 okay so we have created the first level and entry that is good so it would be the customer ID so make the width of G column a little bit shorter okay now select this this range copy this and paste for customer name then country then product and this is for product summary right not product summary it's for call something right let's change the header or the label so this is for customer name and this is for country this is good product name and this is for called somebody okay so call summary should be multi-line text so let's select the edge scripting to m15 so we have already selected edge are 15 to M 15 so we need to select a h15 - maybe you can say that at age 15 - m-19 or m18 right margin center okay and wrap text go to stellar style and select the node okay let's create the drop-down for country reflect the country like this one at 11 and go to data and the click on data validation under data tools okay and in data validation window and allow section just select the list and enter the name country name over here so that's India then China then United Kingdom United Kingdom then Germany then Japan and then France click on ok so now you can see that the drop down list has been created for country let's create the drop-down list work product name as well so select the Ring h13 and go to data again and indeed I told section this select click on data validation in data validation window or let the allow like as list and as a source just give the static value that is product one comma for the two comma product three product five-oh let's click on ok so now you can see that we have also created on the product list here ok now we need to create two different command button over here one for submit and one is for reset so let's go to insert tab and then here shape flick the rounded rectangle okay split over here make it a little bit shorter like this one okay change the style shape style just let the this one blue intense effect blue X and 5 I'll right click on this button edit text and right submit ok now select the select this button go to Home tab and then click on enter in middle and make it a little bit font size 12 or maybe 13 right it's looking good right now let's copy this button select this button and press control C and then press control V okay now I'm going to form it and change the these to intense effect orange xn2 now double click on this and change the label adds re as it field is it okay let's move this submit button over here and we sit over here okay so now we created the submit button as as well as reset button now we need to apply the border so let's select this okay and press control one would do border section and the color should be purple and this one take this line and outline click on ok so now you can see that we have created the form let's change the form name from sheet 1 to 1 ok now we need to add 6 our different worksheet over here for each and every country so that's click on add 1 2 3 4 5 6 ok select all the to check the sheet 2 forms it like slept eight one eight three three four five six and seven while pressing you know Shift key and go to view tag and in grid line okay now start giving the header so the header should be first column should be serial number and second column should be customer ID okay and the third one is customer name and then country product call somebody admitted by submitted on okay let's select all these columns and make it a little bit wider okay look good just like all these columns again go to home and make it center and middle line right select this column header and give it a color over here like back color so maybe blue dark blue and phone side should be white okay click this a 1/2 H 1 and again select couple of rows maybe 10 20 and apply the border as all border okay so now you can see that we have created the sheet 2 sheet 3 4 5 6 7 right so let's start changing the name the first should be India then China then United Kingdom then Germany Japan France okay so let's change the column header so that you can easily identify while moving from one sheet to watch it okay so let's change the back color to maybe this color right for India China let's select the red United Kingdom that's like moving up purple in Germany let's keep the blue clear for Germany Japan maybe you can give the color edge for Japan as green and France black so now we have successfully created all those like all the different worksheet for India China the United Kingdom Germany Japan and France and we have already created the form so now we need to move to visual basic window and start coding right so let's click on a Developer tab and then click on visual basic here in Visual Basic for applications just click on insert menu and click on module just give the module name is MDL or data transfer okay now let's start the coding so first of all we should start coding with a reset button so let's start reset in the school form okay create some room over here and make it a line let's declare a variable Bing I I misses as maybe message box result right and I need a message is equal to message box do you want to reset disk Tom right and the button should be leaving yes and no plus icon should be VV question okay title should be reset reset confirmation okay if I message is equal to BB no then ABS it sir it means whenever user will select egress then it will move forward otherwise it will get terminated terminated it means you know this particular procedure will not process any of the instruction further now this would shoot and the fit mem is palm because we are going to clear the data form and the range should be at 7 comma h9 comma it's 11 o mo h 13 and H 15 that's dot value is equal to blank so now it's done so with the help of this this procedure we will be able to clear all the data which are available in the form like this form okay so this is h 7 h 9 it's 11 it's 13 and h 15 okay and we will assign the reset form or sub procedure on this reset button later all right so let's move to visual basic again and let's start being a pudding for submit data right so submit is sub submit underscore details so first of all we need to declare worksheet variables so let's declare name I set contrary as worksheet right and the second is being assets form as worksheet one variable is to hold the current row should be my current row as integer okay and one is to hold the variable for control am true team yes you only want to click country name add string okay let's create some room so that you can easily see the coding now we need we need to set the form so let's set message form is equal to in this workbook not foods form okay and now we need to identify the selection like the selection of country name from the cell it's 11 H 11 for country right so let's and we will hold that country name under in this particular variable that is called as containing so that's s country name is equal to SH form instead form the dot so to form dot arrange and h-11 dot value okay now we need to offset the such country wash it so that set s H country is equal to this workbook dog and we need to give the name as the name of the worksheet student the name of the worksheet would be s s s continuum because we are moving the databases on the selection on support user will select the country as India so our the target worksheet will be India right suppose and easily we select this China and other countries in our target box it will be China and other country as well right so let's copy this name s is s country name and give the input parameter for this work food or okay so it will bog dynamically it is basis on the user selection on the form so make sure while transferring your data you should give the correct name otherwise it will not transfer and it will give the error let's identify the current route I current row is equal to message this is country dot range e application dot Rose dot count right and then end Excel up plus one so it will give the next a blank row number over here right and we Willis an I current row will hold that rule number now let's start doing the coding to transfer the data so let's so we'd message country and with okay so God itself and the first the rule number is I current a row and the column number is one took first cell is let's move to any of the worksheet over here in data let's select the India so first is serial number and then customer ID customer name country product call summary and so on right so let's move to visual basic and so first is serial number so eyes current a 2-1 okay and then again cells current row and the column should be 2 is equal to SH form so s H form dot range it's seven years out customer LD okay so let's copy this code for this particular line of code this customer ID customer name country product and somebody called something okay so let's change the reference to column values in this column is 3 and customer name is available in each name column number for and the country delivering available in 11 by and put up is available in thirteen column numbers six and the Col somebody is available in obtain X 15 let's copy this range one to more time one is for submitted by and the second one is submitted on okay so let's replace this this should be seven and value should be application dot user username and this is eight column number eight and that should be in time so let's start format and the time should be now the little eyes right now function over here and then the poem it is mm I'm sorry didi mmm why why why why space hh:mm:ss okay so this written now we are with the help of this code we can transfer the data now we need to resit in the form so again Sh form dot dot range h 7 h 9 @ xi h 13 modded coma okay comma H 15 so this is customer ID customer name country product and call summary dot value is equal to blank now let's give the message box let's show a message box for confirmation that data is submitted so ms/ms the MS of all data submitted successfully ok that's it now we can see that how we covered the reset form code like the sub procedure and the supposition for so many details let's go to debug and click on compile balute think there is some error as its form dot okay sorry so what we did we have done some mistake we should not close you know each and every cell reference we just need to put form over here okay so in this way we can clear the data now that's debug compile now you can see that it's working let's move to excel go to form right click on submit data like this button and assign macro select the measuring macro and some wind details click on OK again the sum reset and assign macro that should be Lisa click on OK so now we have successfully assigned and the submit code on submit button and visit code on Orissa good so let's first of all test this the functionality of preset button fluids enter some value over here okay [Music] now click on recept click on yes you can see that he type in successfully the form has been successfully edited right not let's test the function of submit let's enter customer ID 1 and customer name right mr. e and the country should be lets india product name product 1 and testing for india ok now click on submit oh okay sorry so here we missed X and XLS start row okay so we need we do not need the value we just need the tool number over here and that's why it was showing error that if the type is met because we declared the variable as in Peter however we were just assigning the value as you text now it will work let us submitted successfully let's close this mutual basic application and go to India here you can see that I'm glad it has been captured successfully let's again click on form and let's do one more testing vi5 pipe and customer name is mr. Ravi and country's let's let the France yeah and put up should be you know for and this is for proms you should still come there right and let's click on submit it has submitted successfully let's go to brunch and you can see that the data has been transferred to France so in this way you can create a dynamic data entry form and it will really help you if you if you just want to segregate the data at the time of data collection so that further you do not need to work on certification activity and then create the report you just need to refer the relevant state and start preparing the data or summarizing the data so hope you enjoyed this video and this video will be very helpful for you thank you for watching please like share subscribe and comment have a great day bye bye

Channel: TheDataLabs

Views: 61,313

Rating: 4.9028831 out of 5

Keywords: Automated form in excel, Transfer Data from One sheet to other on selection, Dynamic Form in Excel, create automated forms in excel, dynamic data transfer to other worksheet

Id: ehrGvarWqiY

Length: 33min 41sec (2021 seconds)

Published: Sun Dec 22 2019