Christina asks how to make sure that the students in her class stay within a tiny range of the spreadsheet. Episode 509 discusses two methods to solve this problem.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
That's Wednesday.
I'm happy to say that today, I'm back in Canada.
I'm in Vancouver, taping for more episodes of Leo Laporte's new show.
Now, call for help has been moved to Vancouver and it's been renamed the lab.
I'll let you know on the website, when those episodes will eventually appear.
But I'm glad to be going back and doing Leo show.
I also want to point out a couple of things we had the 500 episode number 500, contest a few weeks ago.
The winners of that are being announced slowly.
We're drawing one at a time, if you go to MrExcel.com/pod500.html You can watch as we choose the winners and they get to choose their prize.
I also want to invite you to take a look at the challenge of the month, where we have the March-April challenge.
Voting is open.
We have about five or six or seven formulas nominated and a good discussion there about the best way to solve the tricky calculation problem.
Okay! But back to today's problem.
We have a question that was sent in by Christina.
Christina says you know, "Hey! I work in a classroom, I want to keep my kids in a certain section of the spreadsheet.
I don't want them to go outside of that." Let's just say you know, A1 through E10.
I want to give them that area to work in and want to make sure that they stay outside of everything else.
Well, there's two ways to solve this and if you have younger kids and they aren't very clever.
This first method is going to work.
What I suggested was hiding everything outside of A1 through E 10.
So, for example going to row 11 holding down the [ shift ] key, pressing the [ end ] button, letting go and then the [ down arrow ], that will select all the rows below, row 10.
And then we can use format, row, hide and then similarly going to cell F1, hold down the [ shift ] key the [ end ] key and then the [ right arrow ] and do format, column, hide.
And now, the students are presented with basically, a nice tiny spreadsheet from A1 through E10 and unless they know how to unhide columns, they won't be able to go outside of that range.
Now, if you're worried about someone being able to go in and unhide the range, then we have to go in and use something called the scroll area.
I'm going to switch over to VBA, which is [ alt + F11 ] and some books will suggest that you double click on Sheet 1 and then go to the scroll area property, down here in the lower left hand corner and Specify A1 E 10, the problem with that is that when someone closes and reopens the workbook.
That value gets reset then the projects explorer we have to double click on this workbook and then from the left drop-down choose workbook, from the right drop-down, choose open.
Any code that we write in this tiny procedure will be run every time the workbooks open and it's basically just one line macro.
Let me paste it in here from the clipboard.
This workbook dot worksheets and then in parenthesis, in quotes, the name of the sheet.
So, sheet 1 Dot scroll area equals and then in quotes the range that you want to protect so, for example A1 through E10.
Now, this kids run every time that someone opens the workbook.
Basically, that will prevent anyone from selecting anything outside of A1 through E 10.
They won't even be able to go over and unhide those rows and columns.
So, there you have it.
Thanks to Christina for sending in this question.
If you have a question for the podcast, please feel free to leave us a voicemail or drop us an email and we'll get to you on a future episode.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
That's Wednesday.
I'm happy to say that today, I'm back in Canada.
I'm in Vancouver, taping for more episodes of Leo Laporte's new show.
Now, call for help has been moved to Vancouver and it's been renamed the lab.
I'll let you know on the website, when those episodes will eventually appear.
But I'm glad to be going back and doing Leo show.
I also want to point out a couple of things we had the 500 episode number 500, contest a few weeks ago.
The winners of that are being announced slowly.
We're drawing one at a time, if you go to MrExcel.com/pod500.html You can watch as we choose the winners and they get to choose their prize.
I also want to invite you to take a look at the challenge of the month, where we have the March-April challenge.
Voting is open.
We have about five or six or seven formulas nominated and a good discussion there about the best way to solve the tricky calculation problem.
Okay! But back to today's problem.
We have a question that was sent in by Christina.
Christina says you know, "Hey! I work in a classroom, I want to keep my kids in a certain section of the spreadsheet.
I don't want them to go outside of that." Let's just say you know, A1 through E10.
I want to give them that area to work in and want to make sure that they stay outside of everything else.
Well, there's two ways to solve this and if you have younger kids and they aren't very clever.
This first method is going to work.
What I suggested was hiding everything outside of A1 through E 10.
So, for example going to row 11 holding down the [ shift ] key, pressing the [ end ] button, letting go and then the [ down arrow ], that will select all the rows below, row 10.
And then we can use format, row, hide and then similarly going to cell F1, hold down the [ shift ] key the [ end ] key and then the [ right arrow ] and do format, column, hide.
And now, the students are presented with basically, a nice tiny spreadsheet from A1 through E10 and unless they know how to unhide columns, they won't be able to go outside of that range.
Now, if you're worried about someone being able to go in and unhide the range, then we have to go in and use something called the scroll area.
I'm going to switch over to VBA, which is [ alt + F11 ] and some books will suggest that you double click on Sheet 1 and then go to the scroll area property, down here in the lower left hand corner and Specify A1 E 10, the problem with that is that when someone closes and reopens the workbook.
That value gets reset then the projects explorer we have to double click on this workbook and then from the left drop-down choose workbook, from the right drop-down, choose open.
Any code that we write in this tiny procedure will be run every time the workbooks open and it's basically just one line macro.
Let me paste it in here from the clipboard.
This workbook dot worksheets and then in parenthesis, in quotes, the name of the sheet.
So, sheet 1 Dot scroll area equals and then in quotes the range that you want to protect so, for example A1 through E10.
Now, this kids run every time that someone opens the workbook.
Basically, that will prevent anyone from selecting anything outside of A1 through E 10.
They won't even be able to go over and unhide those rows and columns.
So, there you have it.
Thanks to Christina for sending in this question.
If you have a question for the podcast, please feel free to leave us a voicemail or drop us an email and we'll get to you on a future episode.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
