How to Easily Create an Data Entry Form in Excel | No VBA

Подписаться 18 тыс.

Просмотров 12 тыс.

50% 1

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

Опубликовано:

13 май 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 35

@lesrondee Месяц назад

Highly appreciated. Never imagined I'd be dealing with macros. Found it to be well-explained and easy-to-follow. Thank you!

@JopaExcel Месяц назад

I'm glad you find it useful! Thanks for the feedback 🙏👍

@SalesManager-lp2qu 8 дней назад

@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?

@NumanAbdulmanea Месяц назад

You are amazing, thank you so much

@JopaExcel Месяц назад

You're so welcome! 🙏👍

@alexrosen8762 Месяц назад

Very useful and smart because you made it simple to understand and apply for my own needs without any VBA 👌🙏👌

@JopaExcel Месяц назад

Thanks, Alex! Glad it was useful 🙏👍

@palermopuertoricankitchen Месяц назад

Love your videos

@JopaExcel Месяц назад

Thank you, sir! 🙏👍 Are you from Italy or Puerto Rico? Solo pregunto porque yo sé hablar un poquito de Español 🤣

@palermopuertoricankitchen Месяц назад

Puerto Rican Italian

@ayacleopa5467 Месяц назад

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!

@JopaExcel Месяц назад

Glad it was helpful! 🙏👍

@Esgala2024 Месяц назад

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?

@seymourtompkins 13 дней назад

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.

@JopaExcel 13 дней назад

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.

@seymourtompkins 13 дней назад

@@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?

@JopaExcel 9 дней назад

@@seymourtompkins 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.

@seymourtompkins 9 дней назад

@@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 ?

@Esgala2024 Месяц назад

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?

@khalidmajeed2886 18 дней назад

Mashallah sir,,,,,,,,,,,,,,,,,,,but i have a question that option button should be disabled how we do it

@SalesManager-lp2qu 8 дней назад

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?

@mocassine1 Месяц назад

Can you create something similar for a school exams records?

@hamada5247 Месяц назад

Thanx a lot but if I want the new data become in the second row how can I do that

@JopaExcel 16 дней назад

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.

@pradeeshp2487 Месяц назад

If I have more than one sheets can I apply the same method to all sheet?

@JopaExcel Месяц назад

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.

@alschroeder1724 Месяц назад

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!

@JopaExcel Месяц назад

Thanks for the feedback. I will do my best for improve for the next videos. 👍

@rodeld.rengel4905 Месяц назад

There's a pop up error if i protect either sheets.

@SalesManager-lp2qu 8 дней назад

Hi, how do you fix it? It might be needed to avoid changes to the tab/worksheet...

@kasper643 Месяц назад

Cannot be used if you want to avoid Macros eventhough the title give that impression.

@JopaExcel Месяц назад

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.

@RMFJUNKYARD 17 дней назад

if i hide the the sheet (inventory management) does it still works?

@JopaExcel 17 дней назад

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.

@JopaExcel 17 дней назад

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