Back in August, the podcast showed a trick for precenting people from going outside a certain range of the worksheet. Today, a caller points out that this setting is lost when you close and re-open the workbook. Today's postcast shows how to create a simple one-line macro using Worksheets("Sheet1").ScrollArea = "A1:J10" to ensure that the setting always comes back. Episode 365 shows you how.
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:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
You know, I say that you can always call in with questions, you leave a voicemail at 866-581-0221.
Today's call in is actually not a question, but it's pointing out that one of my past podcasts, I kind of gave a bad tip, let's take a listen: “The tip you gave on the scroll area, I tried that tip before in the past, but as far as I know, there's no way that stays.
That settings, when you save your workbook, when the workbook’s reopened, doesn't return back to normal.” Wow!
OK so, this goes back to a tip from August, where I talked about how to use Alt+F11, and then go into the properties for a worksheet to set the scroll area to be a specific cell.
Sure enough, when you close the workbook and reopen, that setting is lost, making it completely useless.
The scroll area is a great way, basically, to keep someone from going out into the hidden row.
So you can save your scroll area up to be, let's just say, A1:H15, and then they can't go out and unhide any rows you have to the right-hand side.
I didn't realize that the setting was not saved with the workbook.
So, let's see if we can do just a tiny little bit of one line of VBA to make this workbook always reenter at that setting.
VBA is not a scary thing, although if you've never been there, it's going to look a little foreign.
To get there we hit Alt+F11, Alt+F11 will take you into the VBA window, and on the left-hand side you should have a Project Window.
And usually, the project might be sort of collapsed, so we want to open that Project Window, open Microsoft Excel Objects, and then double-click on this workbook, that'll get us into the code for this workbook.
There's two drop-downs at the top: the left drop-down which is Workbook, and the right-drop down automatically changes to Open, this is the code that's run every time that the workbook is open.
So, the code, Worksheets(“ , and the name of the sheet, so in this case it's Sheet1).ScrollArea = “A1 to, let's say J10.
And basically then, every time that this workbook is open, and someone has macros enabled, that little macro will go through and reset our scroll area, so that way, people will be forced to stay in the area.
Hey, thanks for the call, that's a great one.
Anytime I say something is not right, I want to know about it and see if we can find a better way to do it.
So there we go, we're amending that old netcast, a better way to set the scroll area using VBA.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
You know, I say that you can always call in with questions, you leave a voicemail at 866-581-0221.
Today's call in is actually not a question, but it's pointing out that one of my past podcasts, I kind of gave a bad tip, let's take a listen: “The tip you gave on the scroll area, I tried that tip before in the past, but as far as I know, there's no way that stays.
That settings, when you save your workbook, when the workbook’s reopened, doesn't return back to normal.” Wow!
OK so, this goes back to a tip from August, where I talked about how to use Alt+F11, and then go into the properties for a worksheet to set the scroll area to be a specific cell.
Sure enough, when you close the workbook and reopen, that setting is lost, making it completely useless.
The scroll area is a great way, basically, to keep someone from going out into the hidden row.
So you can save your scroll area up to be, let's just say, A1:H15, and then they can't go out and unhide any rows you have to the right-hand side.
I didn't realize that the setting was not saved with the workbook.
So, let's see if we can do just a tiny little bit of one line of VBA to make this workbook always reenter at that setting.
VBA is not a scary thing, although if you've never been there, it's going to look a little foreign.
To get there we hit Alt+F11, Alt+F11 will take you into the VBA window, and on the left-hand side you should have a Project Window.
And usually, the project might be sort of collapsed, so we want to open that Project Window, open Microsoft Excel Objects, and then double-click on this workbook, that'll get us into the code for this workbook.
There's two drop-downs at the top: the left drop-down which is Workbook, and the right-drop down automatically changes to Open, this is the code that's run every time that the workbook is open.
So, the code, Worksheets(“ , and the name of the sheet, so in this case it's Sheet1).ScrollArea = “A1 to, let's say J10.
And basically then, every time that this workbook is open, and someone has macros enabled, that little macro will go through and reset our scroll area, so that way, people will be forced to stay in the area.
Hey, thanks for the call, that's a great one.
Anytime I say something is not right, I want to know about it and see if we can find a better way to do it.
So there we go, we're amending that old netcast, a better way to set the scroll area using VBA.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
