Excel Userforms for Beginners (2/10) - Use Excel VBA to Create A Userform and Manage a Database

[@mauricetanquary2431]

I've said thank you on several of your videos and i'm going to say it again. Your tutorials have without a doubt saved me hours, and I have had downright fun learning this entirely new skill. I think this is what the internet was meant to be, for the spreading of information and education to improve lives and the world. I know that sounds really grandiose for a spreadsheet tutorial, but free education from a skilled professional is nothing to sneeze at regardless of the subject. I hope the advertising you get from these videos for your business is worth it, but even if it doesn't make "dollars and sense" I wanted to say that it is very appreciated.

[@TigerSpreadsheetSolutions]

Ah, Maurice, thank you so much!

[@sokkosal8601]

That's a great place for learning excel that I have never met before. Thanks for all your video and files to download. They are very useful and benefit to the world. Really appreciate!

[@TigerSpreadsheetSolutions]

You're very welcome - do check out the other videos series too. Good luck!

[@maurocastagnera8949]

This project is just what I was looking for. I really congratulate to you for the good explanation, clear, simple and focused on the basic concepts so that even those who like me are not very experienced in the matter can still understand. Thank you very much!

[@TigerSpreadsheetSolutions]

You're welcome Mauro - plenty more VBA videos on the channel!

[@oneheavenly4594]

Chris Clips for VBA is the best option as it is understandable and precise. I love it.

[@TigerSpreadsheetSolutions]

Many thanks - keep enjoying the videos and do share them if they are helping you. :-)

[@mariyatsvetanova6482]

Another wonderful video! Very interesting and useful! I hope soon I can join the Member's Monday! Thank you, Chris!

[@TigerSpreadsheetSolutions]

Mariya - thank you and it would be a pleasure to have you in the member community! Let me know if I can answer any questions for you.

[@maurocastagnera8949]

Dear Chris, it is a pleasure to watch and watch again your videos several times because you teach in an excellent way the basic concepts to learn the VBA. Congratulations indeed, keep it up!

[@TigerSpreadsheetSolutions]

Good to hear from you again Mauro and happy you're still getting value from the videos :-)

[@tarapandey7839]

I'm very bad in learning new thing....but u made me curious to practice more n more about all your videos......thankful for each n every videos....you r very good teacher.

[@TigerSpreadsheetSolutions]

Tara - thank you so much. I aim to foster curiosity about the possibilities of Excel, so happy you have felt that. Good luck!

[@mizouman]

These videos tutorials become my refrence videos , I use them everytime I make new userform based projects , many thanks again

[@TigerSpreadsheetSolutions]

Good to hear that the videos have helped :0)

[@abhinav0686]

I enjoyed and learnt from the tutorial. You are a good teacher. It feels like you are actually talking to us. I have just 1 think to say. The message box should have been a "Do you want to close it?" yes or no. That seemed more apt. I litrally speant half a day till I found the answer to other places. Finally got that. I am an absolute bigginer, if "can they learn it this early on?" was your concern, i think a good teacher like you would have managed to teach it easly. Just some feedback to a great teacher. Thannk. Keep up the good work.

[@TigerSpreadsheetSolutions]

Many thanks and a big welcome to the channel. Really appreciate the thoughtful comment. Much more to come from the channel so please spread the word ...

[@jamesparson223]

Your pace is excellent, I am working through each video. I do forensic amounting and use Excel often, so your VBA/Macro tutorials are great. I am not blowing smoke, so many tutorials are hard so understand for a variety of reasons.

[@TigerSpreadsheetSolutions]

Thanks James - keep up the good work.

[@Mick-qq1pi]

Love your work Chris, you teach well and in detail. Most people would say blah blah blah in some cases, but I believe it works, because you explain the rights and wrongs and you're human. So we don't just see perfection is what I'm saying whilst watching but get perfect result. Keep it up I'll contact you soon with email as I want to do the same with these but use only part of.

[@TigerSpreadsheetSolutions]

Thanks very much Mick - hopefully the blah blah blah is indeed worth it!

[@Mick-qq1pi]

All good. Keep up the good videos. The blah blah part is just a minor thing I still give you 10/10 for all the efforts.

[@TigerSpreadsheetSolutions]

@@Mick-qq1pi Ah, Cheers Mick!

[@monicaanicito4342]

Hi Chris, your videos are by far the best out there! I am so glad I came across them. I am trying to learn how to create userforms and this first video has given me a great start. You have an excellent step by step way of teaching, I have liked and subscribed. Looking forward to viewing video #3.

[@TigerSpreadsheetSolutions]

Thank you Monica - really nice comment! Good luck with the rest of the series, let me now how you get on.

[@bajirao1975]

Dear sir,Really nice explained regarding the user-form for the beginner and it's really helpful to me.Thanks

[@TigerSpreadsheetSolutions]

You're welcome and good luck!

[@isaacbbe]

This is exactly what I needed. thank you very much. Looking forward to all your videos!

[@TigerSpreadsheetSolutions]

I hope you enjoy the series - let me know how you get on :-)

[@DanL-tg2fq]

trying to self learn, great speed for the vids. thanks very much

[@TigerSpreadsheetSolutions]

You're welcome Dan - self-learning really is the best way in my view. Do check out the other videos on the channel.

[@DanL-tg2fq]

will do :) . On 6/10 of this series first. Trying to make a spreadsheet for the calendar year that will allow data entry for a team of people to show a maximum of four off at any one time. Guessing it wont be easy hehe

[@TigerSpreadsheetSolutions]

It always a journey! Try to take your time and treat it as a learning process. Let me know how you are getting on.

[@AhmetOkanYILMAZ]

Thank you for the tutorial. You are the best.

[@TigerSpreadsheetSolutions]

Very kind of you - I hope you enjoy the rest of this series

[@henrikijonkoping4694]

I have to check the whole serie of clips Chris!

[@TigerSpreadsheetSolutions]

Hope you enjoy this series, Henrik! It's a classic ...

[@rupeshns03]

Hi Chris, loved to watch your video and indeed useful for the begineers like me. I need to write a code to select / link the values from specific column from a table into a form lable. Can you pls guide me on this? cheers, Rupesh

[@davecks3042]

Excellent video. Thank you Chris

[@TigerSpreadsheetSolutions]

Thanks for watching and I hope you enjoy the rest of the series :-)

[@behroozkavoshgar7917]

Thanks for your video, In your video the title of the form(i.e. userform1) is in left corner. My question is that how to adjust(or set) the title of the form in the middle?

[@TigerSpreadsheetSolutions]

I'm not sure if this is possible ... Google?

[@DJJSerpico]

Great job and GREAT PACE which is the most important thing to me. Huzzah!

[@TigerSpreadsheetSolutions]

John. Huzzah! Thank you.

[@jacobeaton6999]

I love YOU!!!! and I haven't even watched any videos, but this one and you are a great teacher. Keep up the awesomeness :)

[@TigerSpreadsheetSolutions]

Hi Jacob - I love all the viewers who are the heroes of the channel! Have fun with your coding.

[@balunqui1126]

thank you very much, you're amazing, i watched all your videos about excel userform, great tutorial... i like all..

[@TigerSpreadsheetSolutions]

Many thanks Balun - please do share the videos with somebody whom you think they might help...

[@alperturna4371]

Thank you so much!

[@TigerSpreadsheetSolutions]

you're welcome!

[@caitlintomkies5263]

Thank you for your Tutorials.

[@TigerSpreadsheetSolutions]

You're welcome Cai :-)

[@oslcparksville]

Excellent Series. I could not find a delete user option, Is ther one?

[@TigerSpreadsheetSolutions]

Hi Harold - is this something you could try to create yourself, using the techniques from this series?

[@JoeMontgomeryCPA]

Another great video. On point and informative. :-)

[@TigerSpreadsheetSolutions]

Good to hear they are helping Joe! Let me know your questions.

[@slurrypumper5985]

By the way from the last video, I'm not sure if I could teach myself to switch gears with my left hand. Does the clutch pedal go on the right hand side or does it stay on the left?

[@bloncaric10040]

Hi Chris, being grateful for videos, helped me make my company's reference. Have a problem though. Numbers added are persistently in text format and I want them in number format so to be able to do math consequently. Can't find the adequate solution to my problem. Is there something I keep on missing?

[@TigerSpreadsheetSolutions]

Hi there and no, I omitted it from the series I believe. Use =Val in Excel VBA to convert text to numbers on the way into the spreadsheet ...

[@jluke7024]

Hi! Great videos!!! I have learned so much from your videos. But is it possible for you to modify the code just a bit so that we can do away with the "engine" sheet?

[@TigerSpreadsheetSolutions]

Hi there - glad you like the videos! And yes, you could modify the code to allow you to delete the engine sheet. I would say using a sheet in the file allows you to easily manage aspects of the coding, however, since it is easier to access and amend a sheet than to amend code in the VBA editor.

[@1668hk]

well done!

[@TigerSpreadsheetSolutions]

Thanks for watching!

[@natariee7527]

this is sooooo good! thanks

[@TigerSpreadsheetSolutions]

Thank you and welcome! Let me know your questions

[@guillermomaura4447]

Chis, thank a lot for your videos are very useful and very professional Tutorials. I am facing problems in trying to insert a UserForm, this option (UserForm) does not appear when using Insert of VB. in the box of Insert only appears the following: Procedure, Module, Class Module and File, But not the option UserForm.

[@TigerSpreadsheetSolutions]

Hi Guillermo - thank you! I've actually not heard of that before. Are you using Mac by any chance? If your version of Excel is not up-to-date and you are using Mac, you may not be able to access userforms. I would say make sure your version of Excel is up-to-date. I recommend Office 365. Good luck!

[@guillermomaura4447]

@@TigerSpreadsheetSolutions Hi Chris, Thanks you for your reply. Thats right, I am using Mac and Office 365 though Office is up-to-date maybe Mac is the problem.

[@robertobraicovick6867]

Molto interessante, grazie!

[@chaimajerbi3580]

Great tutorial ! I was wondering can you add more than a database into a user form? To put it in anothrer way, can you assign the labels from the same userform to two different worksheets/database?

[@TigerSpreadsheetSolutions]

Potentially, but try not to overcomplicate workbook structure

[@ktg9502]

When I click the continue button a window pops up saying that my sub or function has not been defined.... I have done the exact same steps you did along the video... any idea of why this happened? thanx a lot

[@TigerSpreadsheetSolutions]

Pablo - that is a syntax error, so you'll have to check your typing very carefully, good luck!

[@jolinamaeenola3611]

Hi Chris, newbie here an need an advise recently got a client. How much do you usually charge for the project created? Also do you also include a post sales service for free ?

[@TigerSpreadsheetSolutions]

Hi Jolina It's up to your to work out the fee. You could look at freelancer websites to understand what others are charging. But, if you charge more, people tend to value your service more highly. So, it's a tricky one, but gets easier with time. There are many other things to consider - we have a series on the channel called 'How To Make Money With Excel' where I discuss these matters in detail. Make sure this issue doesn't stop the project. It's more important that you gain experience than you get the price exactly right first time. Good luck, Chris

[@jolinamaeenola3611]

@@TigerSpreadsheetSolutions This is all noted! thank you so much Chris for the insight. ❤

[@almacoles5501]

Does anybody know if Userforms (or alternative Userforms) are available in Office Excel 365? Even with it's Office Script language?

[@TigerSpreadsheetSolutions]

As far as I know, they are not. But it might be possible to integrate with another online facility such as Microsoft Forms? This is certainly not my area, however ...

[@almacoles5501]

@@TigerSpreadsheetSolutions I'm not confident that they would allow MS Forms to be used with Excel if they've never implemented it's integration to begin with. Something tells me that if you wanted to combine Excel w/ Forms, then you might as well subscribe to Microsoft's intention to drive such interest using Access 365 which integrates Forms, Reports, Databases that represents the next level of Data Management beyond an Excel 365 Spreadsheet. Thanks!

[@TigerSpreadsheetSolutions]

I meant using MS Forms to collect data, then downloading the results to Excel and analysing that way, possibly using VBA to automate the process. I've done some useful projects in that way. Thanks for the input - who knows exactly where things will go in the future ...

[@almacoles5501]

@@TigerSpreadsheetSolutions re: "using MS Forms to collect data, then downloading the results to Excel". That sounds equivalent to using Google Forms which writes and collects the data into an online Google Sheet, at which point could also be downloaded into Excel for analysis. Microsoft could have integrated User Forms in their online Excel 365 version in order to avoid all unnecessary downloading and uploading nonsense... it's a shame. But Thanks!

[@MrMesow]

Thinks for the fantastic tutorial, I would like to share my experience in practicing the exercise step by step. First when I tried to link the MACRO to the button it did not work and I received an error message (Error 424) I realized that when I renamed the UserForm I forgot to press Enter so the name did not change. The 2ed issue I had was when I wrote the command “Unload Data_UF” I received Run time Error 361 “can’t load or unload this object” I didn’t figure out what I did wrong yet I will continue with the tutorial and hopefully I will update my comment when I fund out. I will be grateful if someone tell me what is wrong.

[@TigerSpreadsheetSolutions]

Hi Masoud - good to hear the tutorials are helping. The second error might be caused by inaccurate naming of objects - is the userform definitely called 'Data_UF', for example? Is there something else (a VBA routine?) called Data_UF? Also check the userform is loaded / visible when this line of code is executed. Good luck!

[@aperson1181]

I'd created a presentation/tutorial in MS PowerPoint, run it in a terminal mode, where users navigate through vba coded buttons. Now, I'm figuring a way to capture who views the PowerPoint (also, trying to capture their feedback). Could anyone please help with a vba code that could be tied to a userform in the beginning or the end of the presentation that would allow users to fill and submit the feedback/registrations? Could an embedded Excel w/VBA help with this?

[@TigerSpreadsheetSolutions]

Interesting idea - but aren't there easier ways to log comments about files? Dropbox, for example, allows for comment sharing and discussion.

[@aflamaty969]

helpful , thank you

[@niktravel1820]

Hey Chris, how can i make after insertig data or after updating data from the userform - the active row to be selected.

[@TigerSpreadsheetSolutions]

Nikoley - we have a few follow-up videos to this series coming this spring, look out for those ...

[@genesiusjaromsky2722]

Hello, Great instructional video. I might have missed it. Is there any way to save this as form usable on an Amazon Fire? I have OfficeSuite from MobiSystems. All I need is a simple (3 field form). However, the Form control is not available in this application. Any ideas? Is there any way to save the .xlsx as a standalone application (.exe or what extension/format is used for an Amazon Fire). Thanks and God bless, Genesius

[@TigerSpreadsheetSolutions]

Thank you - I'm afraid I can't help you with that query, however. Good luck with it.

[@glaciervt]

What is the difference between Unload Data_UF & Data_UF.Hide

[@TigerSpreadsheetSolutions]

I'm not absolutely sure. Hiding the form means you can 'unhide' it later, rather than 'activating' it. The two events are slightly different, and any code you have attached to the 'activate' event probably won't run when you unhide the form. I imagine that unloading it rather than hiding it is more memory efficient since a hidden form is actually still there, it's just you can't see it. It's probably only a marginal difference but to me unloading is best practice.

[@glaciervt]

@@TigerSpreadsheetSolutions Thank you very much for your quick reply. Everything you said does make sense and I am going to use it that way. I am doing small project watching your videos and adding a bit extra code to it to challenge myself. Thank you for your videos, very helpful indeed!

[@niktravel1820]

Hey Chris, when i want to edit an information, the form search only by First Name, BUT sometimes i only know the last person name and therefore I can not find the record in the form. Can you tell how can the form search either first OR last name ?

[@TigerSpreadsheetSolutions]

Nikolay - it's possible! You could give an option in the userform to choose a list to search by (first or last name,) perhaps using an option button or tick box. Then you would need some code to upload new values to the combobox according to the option selected. Not easy, but possible. Remember, duplicate entries might be an issue!

[@focosolutions1604]

Great tutorials ... Thanks. My question is why in the subroutine the commands are structured: object . command (Data_UF.Show) whereas in the private sub the commands are structured inversely: command object and no dot (Unload Data_UF)? This is confusing. Can I use the command Data_UF.Unload in a private sub? Thanks again.

[@TigerSpreadsheetSolutions]

Thanks Foco. About your question - I do not know! I will post back here if I can find the answer...

[@Kenny-tl8bh]

I followed ur steps and tried to run the Add button macro. But when I go back to VBA, the Userform Is gone and I can't seem to make it appear like you did. Anyone can help how to make Userform appear again so that I can double click the "CONTINUE" button at the bottom of Userform to edit?

[@TigerSpreadsheetSolutions]

Kenny - it is strange that the userform should disappear. Did you try resetting the code? Hit the red 'stop' icon at the top of the VBA editor.

[@muaathe78]

I have changed the number after "The UF will be closed" to be 1,2,3 & 4

[@KnowEverythin]

nice... if I have a one text box and one combo box. in text box we input date and comobox we already place three shift i.e A,B,C. I want to make a database where per day per shift record are stored in my database whenever same date and same shift are entered it gives duplicate msg. pls help.

[@TigerSpreadsheetSolutions]

Hi Haradhan, in this series I create a simple validation mechanism to check if an entry already exists (video 7/10). You can adapt this mechanism for your purposes. Good luck!

[@michaelisumanu1195]

Great video Sir! However, I'm having a hard time adding data to a excel table (not a worksheet) using a userform in excel vba. Could you help me out, please?

[@TigerSpreadsheetSolutions]

Michael, thanks for the comment. I don't have huge experience with tables I am afraid - Google is your friend here. Good luck!

[@michaelisumanu1195]

@@TigerSpreadsheetSolutions Thank you sir. Much appreciated.

[@yuttanak6225]

you are amazing, clear and great teacher but could you please move the steps a little bit faster? it seems like you kept repeating words and again and so tiny details and say it again just okay guys you have to click this, to select this, to type this, to close this, to be careful on this, this is case sensitive guys etc. just don’t need to be so repetitive. Don’t take me wrong your videos and explanations are much appreciated and valuable it’s just a little bit annoying when you keep repeating something again and again and for those who watched this video, they should know Excel I believe they do so please - thank you

[@TigerSpreadsheetSolutions]

Thank you - I try to pace the videos as best I can according to my experience of teaching VBA. The videos are aimed at beginners and most people seem happy with the pacing, including repetition of important points. You could watch in 1.5x speed maybe?

[@yuttanak6225]

Tiger Spreadsheet Solutions yes sure, thank you for your reply

[@TigerSpreadsheetSolutions]

@@yuttanak6225 You're welcome and good luck

[@niktravel1820]

After inserting data from the form to the sheet how can i make that row selected ?

[@TigerSpreadsheetSolutions]

Use the variable that controls the next row to select a cell, eg. Range("A1").offset(Next_Row,0).select Selecting a range of cells on a row is more complicated, but possible. Check out our video "Excel VBA Position Control Mastery".

[@eyfcakeeatyourfreakingcake1900]

What formula do I Add if I'm using numbers instead of text?

[@TigerSpreadsheetSolutions]

Hi there - use =Val() in VBA when you input to the spreadsheet - this converts text to values, good luck!

[@niktravel1820]

Hey Chris, in my sheet i have formulas and when i use a form to input data the process gets so slow that sometimes i must wait for 2 minutes to input information from the form to the sheet. Could you please write a line of code that will allow the UserForm on new data input, to take the formula from the previous row and then update the formula with the current address and then populating the updated formula to the current row. This must happen only when NEW record is created. I thing this is the way to have fast input when the sheet contain formulas as well.

[@TigerSpreadsheetSolutions]

Nikolay - there is definitely a problem there with the code or how you have set up the formulae: - step through the code to try to understand which instruction is slowing things down - check the formulae - are they referencing large ranges? That could be slowing things down If no luck, start again and test at every step. When an inefficiency occurs, find a different way.

[@JoshFE]

HI Chris, I have opened up the user form sheet within VBA however I don't have the table of properties that are visible to you in the left bottom corner, can you assist?

[@TigerSpreadsheetSolutions]

Josh - View menu at the top of the VBA editor, then click 'Properties Window'. Good luck!

[@hectorjimenez2965]

thank you!!!

[@TigerSpreadsheetSolutions]

You're welcome Hector - good luck with your coding :-)

[@matthewanacleto7885]

All your VBA is Mac compatible?

[@TigerSpreadsheetSolutions]

The majority works fine on Mac

[@matthewanacleto7885]

@@TigerSpreadsheetSolutions So the answer is no.

[@caitlintomkies5263]

Is there a way to enable vba macros when a file is opened on an android?

[@TigerSpreadsheetSolutions]

Not that I know of Cai, but it's not something that I try to do often...

[@gregwebb1986]

Hi All, ive been using this and other series of videos from Tiger Spreadsheet and have successfully managed to create a database where data from a user form is dispersed across different sheets (due to type of data and volume related to a single incident) Am having trouble bringing the data back into a user form for reviewing all data. "column A" on several sheets contains a reference number for the incident in question that links all data. cannot figure out the code for it though. Anyone able to help in hinting or nudging me in right direction please? I think I will need an "application.worksheetfunction.match" line of code but struggling. any suggestions welcome. thanks

[@gregwebb1986]

Wohoo!!!!!!! i sorted this ... chuffed to bits...realised where i was going wrong in the syntax of code. jiggled it about a bit and hey presto !!!

[@TigerSpreadsheetSolutions]

Nice job Greg, well done! Debugging skills are so important ...

[@yaminisingh7271]

Thanks man! . I have a little query nd if u solve it fast it would mean a lot. So i made the button and user form and assigned the button with that particular macro as you demonstrated. And it worked and i saved it under normal .xls extension. Later i opened my file i showed me a mesg saying the userform is mesg that userform is not enabled. The exact mesg was - cannot run macro "name.xlsx.!macro name". The macro may not be available in this workbook or all the macro may be disabled. Now i really don't know wht is the matter. The only thing i can figure out is i have not saved the file under macro enabled file type. If u reply me soon it would mean a lot. Thank you

[@TigerSpreadsheetSolutions]

Hi there - yes, it sounds like macros are not enabled in Excel. Check in Excel options and ensure you hit 'enable macros' when Excel opens. Good luck!

[@yaminisingh7271]

@@TigerSpreadsheetSolutions thank you for reply. And yes i did misread the mesg flashed before saving so it was all over my fault 😬😅

[@francescadelger7681]

When I hit the add button, nothing happens, the code Data_UF.Show just highlights in yellow and then if I hit Run within the VBA editor, the Continue input form generates.

[@francescadelger7681]

Then I got a "Can't load or unload this object."

[@TigerSpreadsheetSolutions]

Francesca - I can only recommend stepping through the code using the F8 key to try to understand what is happening. Good luck!

[@SilatLintauAsli]

How do I contact you

[@TigerSpreadsheetSolutions]

Hi there - contact details are on the website

[@hotmido123]

The link to the files is broken

[@TigerSpreadsheetSolutions]

the files are being shifted to a new server and will be available in the next few days

[@southhillfarm2795]

Couldn't get the button to work.

[@southhillfarm2795]

Got it to work :)