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.
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 ?
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?
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.
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?
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!
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?
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.
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.
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
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.
Recording a macro creates a VBA procedure. The term "No VBA" is therefore somewhat misleading. In fact, you cannot save your Excel file as ".xlsx", but must use the macro/VBA-enabled ".xlsm" format, which may even be blocked for security reasons. An Excel build-in data entry form that doesn't actually use VBA is explained at ruclips.net/video/cNpFB8M1-dI/видео.html (which won't be the solution for many, but is still a very useful time saver for many)
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 🙏👍
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.
very well explained! And working properly!!
OMG, every single sentence from you contains valuable information for me! Thank you so much for such a clear video, liked and subscribed!
Just what I needed. Thanks!
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 ?
This is the first time I'm watching your video❤
Awesome video, thank you!
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 🙏👍
thank you very nice
Hi, I like this video. Coz I learn new. Beside that if from the data how to generate an report as monthly report month by month
Thanks for your video.
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! 🙏👍
Its not giving me the option to insert down. Struggling to figure this out
Any idea how i can add a modify/edit button?
@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! 🙏👍
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?
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
Mashallah sir,,,,,,,,,,,,,,,,,,,but i have a question that option button should be disabled how we do it
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?
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.
Thank you
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?
Can I still put data in worksheet if it is protected sheet?
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. 👍
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?
Is there a way that each time a user push the submits botton its sends the data to Outlook address?
Hard time to understand how to lock cell and sheet in macro. I receive a lot of error .
Can you create something similar for a school exams records?
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
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.
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...
Recording a macro creates a VBA procedure. The term "No VBA" is therefore somewhat misleading. In fact, you cannot save your Excel file as ".xlsx", but must use the macro/VBA-enabled ".xlsm" format, which may even be blocked for security reasons.
An Excel build-in data entry form that doesn't actually use VBA is explained at ruclips.net/video/cNpFB8M1-dI/видео.html (which won't be the solution for many, but is still a very useful time saver for many)
Your tutorial is remarkably similar to that of ruclips.net/video/UXzOlBI_Zk0/видео.html, which was published 9 days earlier.
Too much time is spent to explain basic formatting this subject is supposed to be intermediate level
Just shut up and be thankful!
hi how can i contact you