Today, Bill shows off some of the truly dynamic functionability of Microsoft Excel. Oz wants to use a worksheet as a data entry form and then post the record to a database. In Episode #1503 - the first of a two part tutorial - Bill shows us how to use some VBA to make this happen.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1503: Excel As A Data Entry Form.
Well, hey, everyone. Welcome back to the MrExcel netcast. I'm Bill Jelen. Today's question actually is based on a discussion [ unintelligible – 00:12 ] the LinkedIn Excel Gurus group.
Someone…Oz had asked about getting Excel to act like a database with a user form. So, pretty clever. He wanted to have a nice little excel data entry form like this one, you know.
So, maybe, you know, I publish books. This is a new title planning form and you can picture this. You would use Excel to have various fields where people enter data. You could even have data validation, you know, that would pop up in various places, or maybe formulas to calculate some fields, and then you want to be able to post this data to a database.
So, I’m going back here to the second worksheet, the database, and we want to just find the next row and then, you know, copy that data over and possibly even clear out the data for the next records. That way, they can go back through and enter things. So, let's switch over to VBA. This is some VBA…this is some VBA that I posted in that message board.
So, we have two worksheets, the form worksheet and the database worksheet and, you know, we are going to assume that when they run this, they're sitting on the form worksheet.
So, the first thing I want to do is figure out where the next row is and this is, you know, that famous bit of code that I use in almost all of my macros. Go back to the database worksheet, start from the very bottom, ROWS.COUNT, that's 1048576 if you're in the new Excel or 65536 if you're in compatibility mode.
Column 1, press the END key, the UP ARROW key, and see what row you're at. +1. So, this is going to figure out where the next record is going to go and, you know, then, from there, I have 9 fields that I want to write, so we're writing to the next row and the values are coming from -- and this is just the tedious part -- they’re coming from D13, D14, D23.
You know, you’re going to do a lot of switching back and forth between Excel to figure out, you know, where the next value comes from, and in my particular case, they're not even quite in order, D14 then D23, because I was trying to match it to the database. So, the database, you know, has fields in this order but I wanted people to enter data in some other order. So, you have to just, you know, a little bit of careful checking to make sure that you have everything right, and then clear out all of the cells so that way we’re ready for the next record. So, this is called move record.
In order to have this post, we could either use a forms control, or if you don't have the DEVELOPER tab, it's simple enough. Just add some sort of a shape. So, maybe we can…let's come up with an arrow here. We’ll just add a little arrow, and text box, say POST RECORD, and we can center that. There we go. Now, to make that run the macro, right-click, ASSIGN MACRO, and say MOVERECORD, click ok, alright?
So, let's do a little test here. We've entered this data, click POST RECORD, alright, and that record is now written to the next row in the database. We're ready to roll again.
So, just a simple little data entry form and it's so much better looking than the old ALT+D O database, you know, form that we used to have from Excel 2003. I don't even think you can find this in the ribbon anymore. It was just such a miserable little record thing.
So, you know, this way, you get to add some formulas, get to add some validation, create a better-looking form, and then, you know, allow people to post that record to the database.
Now, tomorrow, we're going to come back and take a look at a better way, some improvements, to this method.
Okay. Well, hey. I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1503: Excel As A Data Entry Form.
Well, hey, everyone. Welcome back to the MrExcel netcast. I'm Bill Jelen. Today's question actually is based on a discussion [ unintelligible – 00:12 ] the LinkedIn Excel Gurus group.
Someone…Oz had asked about getting Excel to act like a database with a user form. So, pretty clever. He wanted to have a nice little excel data entry form like this one, you know.
So, maybe, you know, I publish books. This is a new title planning form and you can picture this. You would use Excel to have various fields where people enter data. You could even have data validation, you know, that would pop up in various places, or maybe formulas to calculate some fields, and then you want to be able to post this data to a database.
So, I’m going back here to the second worksheet, the database, and we want to just find the next row and then, you know, copy that data over and possibly even clear out the data for the next records. That way, they can go back through and enter things. So, let's switch over to VBA. This is some VBA…this is some VBA that I posted in that message board.
So, we have two worksheets, the form worksheet and the database worksheet and, you know, we are going to assume that when they run this, they're sitting on the form worksheet.
So, the first thing I want to do is figure out where the next row is and this is, you know, that famous bit of code that I use in almost all of my macros. Go back to the database worksheet, start from the very bottom, ROWS.COUNT, that's 1048576 if you're in the new Excel or 65536 if you're in compatibility mode.
Column 1, press the END key, the UP ARROW key, and see what row you're at. +1. So, this is going to figure out where the next record is going to go and, you know, then, from there, I have 9 fields that I want to write, so we're writing to the next row and the values are coming from -- and this is just the tedious part -- they’re coming from D13, D14, D23.
You know, you’re going to do a lot of switching back and forth between Excel to figure out, you know, where the next value comes from, and in my particular case, they're not even quite in order, D14 then D23, because I was trying to match it to the database. So, the database, you know, has fields in this order but I wanted people to enter data in some other order. So, you have to just, you know, a little bit of careful checking to make sure that you have everything right, and then clear out all of the cells so that way we’re ready for the next record. So, this is called move record.
In order to have this post, we could either use a forms control, or if you don't have the DEVELOPER tab, it's simple enough. Just add some sort of a shape. So, maybe we can…let's come up with an arrow here. We’ll just add a little arrow, and text box, say POST RECORD, and we can center that. There we go. Now, to make that run the macro, right-click, ASSIGN MACRO, and say MOVERECORD, click ok, alright?
So, let's do a little test here. We've entered this data, click POST RECORD, alright, and that record is now written to the next row in the database. We're ready to roll again.
So, just a simple little data entry form and it's so much better looking than the old ALT+D O database, you know, form that we used to have from Excel 2003. I don't even think you can find this in the ribbon anymore. It was just such a miserable little record thing.
So, you know, this way, you get to add some formulas, get to add some validation, create a better-looking form, and then, you know, allow people to post that record to the database.
Now, tomorrow, we're going to come back and take a look at a better way, some improvements, to this method.
Okay. Well, hey. I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
