How to Easily Create an Data Entry Form in Excel | No VBA
ฝัง
- เผยแพร่เมื่อ 4 ต.ค. 2024
- In this Excel video tutorial, we gonna see How to Easily Create an Data Entry Form in Excel with macros, but without VBA, Visual Basic for Applications. Make a Data Entry Form in Excel can help us on a daily basis to automate tasks and save time.
We will see how to enable the developer tab in Excel, because we need to use two tools within it, record macro and option button form control to be used as a checkbox or a mark option.
Using forms in Excel offers several benefits. Overall, forms streamline the process of data entry and management in an Excel worksheet, making it more efficient and less error-prone.
Ease of data entry: Forms provide a more user-friendly and structured way to input data into a worksheet, which can be useful when there are many fields to fill in.
Data validation: You can set rules and constraints on forms to ensure that the entered data is correct and within desired parameters.
Error reduction: Since forms guide users through the data entry process, there are fewer chances of errors compared to direct input into the worksheet.
Standardization: Forms help to standardize the format of data, ensuring consistency throughout the worksheet.
Security: Depending on the settings, forms can help protect the integrity of data by preventing unauthorized changes.
Ease of use: Forms may be more intuitive for users who are not familiar with Excel, making it easier for them to input and view data.
#JopaExcel #Dashboard #Excel
![เปิดบ้าน พี่ตูน Bodyslam หลังใหม่คนแรก ที่ภูเก็ต l [Nickynachat]](http://i.ytimg.com/vi/flFwcmyDQSk/mqdefault.jpg)
Highly appreciated. Never imagined I'd be dealing with macros. Found it to be well-explained and easy-to-follow. Thank you!
I'm glad you find it useful! Thanks for the feedback 🙏👍
OMG, every single sentence from you contains valuable information for me! Thank you so much for such a clear video, liked and subscribed!
This is a very beginner friendly tutorial. All movements have clear explanation and with a little bit of imagination, the viewers can modify the way the macro works.
This is the first time I'm watching your video❤
Very useful and smart because you made it simple to understand and apply for my own needs without any VBA 👌🙏👌
Thanks, Alex! Glad it was useful 🙏👍
Thanks for your video.
Its not giving me the option to insert down. Struggling to figure this out
That was helpful. I need to know how to maintain first entry at number one while the following goes after the first entry. Thank you!
Glad it was helpful! 🙏👍
Thanks, Jopa.
A couple of thoughts:
1) your idea about first copying/pasting all values into the Form and then copying pasting them again on the Inventory sheet was new to me. However, I thought that whenever one selected "copy values", the formulas naturally wouldn't paste. I'll have to test this out to see what happens.
2) An alternate way to hide the helper cells is to make them all white
3) One should also protect the worksheets so that users can modify only certain cells. That is, the helper cells on the Form and the cells in Inventory should never be directly modified by users.
Thanks for the feedback! 🙏👍
About the number 2-). I actually did make it all white in the origianl video, but I removed this part when editing the video, the video was getting too long and I don't like to waste the viewers time, but anyway...
About 3-). You're right, protecting the sheet is an excellent idea. I will bring more videos on the subject and I will definitely consider your feedback. Thanks a lot.
@@JopaExcel Hey. Thanks. Btw, re my first point, when I queried chatGPT 4.o about it, it said if you just select "paste values", the formulas (i.e. =cell) will not be pasted. So, what's the issue?
@@Me_ThatsWho It is possible to copy and paste cells with formulas. You don't just need to copy and paste only values. Just copy a cell that has a formula inside, and then use "Home tab" then "Insert", "Insert Cells" and "Shift cells down". It works, please test it and you will see.
@@JopaExcel You misunderstand me. In your video, you stated that you did NOT want to directly copy and paste into the spreadsheet for fear that would carry over formulae too (such as ' =A1 '). As you'll recall, that was why you first copied/pasted onto the original sheet before copy/pasting a second time into the spreadsheet. With my comment, I was just indicating that coying/pasting twice was unnecessary if you use 'copy values'. Get my point ?
@JopaExcel, many many thanks! I found what I wanted. Can you please comment how to clean the form AFTER being filled for a new user/entry PLEASE?
You are amazing, thank you so much
You're so welcome! 🙏👍
Any idea how i can add a modify/edit button?
hi! really informative video, but does this still work if the entry form is in a separate excel workbook from the sheet storing the records?
Thank you
One more thing, how could I make the data entry separate from the database so it´s only "one-way": people feed but has not access to the information PLEASE?
Mashallah sir,,,,,,,,,,,,,,,,,,,but i have a question that option button should be disabled how we do it
Great tutorial. But I have a quick question. Is it possible for the submitted data to go into two different rows in the Data Sheet? This is required for accounting double entry purposes.
I think it can be done with a small alteration in the Macro process.
Hard time to understand how to lock cell and sheet in macro. I receive a lot of error .
Love your videos
Thank you, sir! 🙏👍 Are you from Italy or Puerto Rico? Solo pregunto porque yo sé hablar un poquito de Español 🤣
Puerto Rican Italian
A few comments, hopefully constructive.
1) A bit too fast, little slower on delivery.
2) Hated that you hid the columns prior to running the macro. Would have been nice to see the full macro run through what was happening in the hidden columns.
3) Maybe a summary at the end of the buttons selected; or some way of having a screenshot list to work from rather than searching through the whole video.
4) Liked that during the video you circled in red to highlight what you were specifically describing…so many just say it and your left trying to search the screen for what they’re talking about, nice job there!
Liked your concept and the style of presentation. Again just my opinion on comments.
Cheers!
Thanks for the feedback. I will do my best for improve for the next videos. 👍
What if i wanted for the inputted data to appear in certain cells in the second book? Like, i wanted to create it as a way to automate creating a printable document with headers and footers, company logo and etc. with the inputted data to only appear as the body of the document per-se. is that possible?
what if i wanted to insert the data to certain cells instead? is that possible, I plan to make this but in the second book it would look like a document ready to print with headers and footers, company logo, but the body of the document is the data i inputted in from the first book? is that possible?
Can you create something similar for a school exams records?
There's a pop up error if i protect either sheets.
Hi, how do you fix it? It might be needed to avoid changes to the tab/worksheet...
Cannot be used if you want to avoid Macros eventhough the title give that impression.
I understand what you mean, but you are confusing the terms. To automate tasks you can use Macro, but you don't necessarily need to know VBA. As briefly as possible, Macro is just the tool to record clicks, you don't need to know any programming for that.
Thanx a lot but if I want the new data become in the second row how can I do that
We can do this dynamically. It starts on the first row, then the second, third, fourth and so on. You can use the navigation keys together with CTRL to always jump to the last row and then the down arrow to skip to the first blank row.
If I have more than one sheets can I apply the same method to all sheet?
For sure. You can use the same method.
I'll give an example just to make it easier to understand. Let's say you have three sheets, the first with the name of the products, the second with the customer, and the third with the record of all sales made.
You can create a new spreadsheet with the form like I showed in the video. This sheet with the form, will fill in all the other parts of the sheets.
It might even be interesting for you to create one form for each sheet. If you have three sheets, make three forms, etc. And, for each form, you can create a macro.
if i hide the the sheet (inventory management) does it still works?
If you hide a sheet, the macro will not work. The macro uses the name of the sheets to work. When you hide a sheet, the macro cannot find it.
Maybe we can open the VBA code behind the macro and modify it to unhide the sheet, the perform the operations, and then hide it again. Just an example below:
Sub ModifyHiddenSheet()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("inventory management")
' Unhide the sheet
ws.Visible = xlSheetVisible
' Perform your operations here( example: adding data to cell A1)
ws.Range("A1").Value = "Abc 123 abc 123"
' Hide the sheet again (if necessary)
ws.Visible = xlSheetHidden
End Sub