| Channel | Publish Date | Thumbnail & View Count | Actions |
|---|---|---|---|
 | Publish Date not found |  0 Views |
Welcome back to the MrExcel netcast, where we provide helpful tips and tricks for using Excel. I’m Bill Jelen, and today we’re addressing a mistake I made in a previous podcast. A viewer pointed out that my tip on using the scroll area in Excel was not as effective as I had thought. Let’s take a listen to their comment: /”The tip you gave on the scroll area, I tried that before, but it doesn’t stay when I save and reopen the workbook./” Wow, I had no idea!
In case you missed it, in a previous podcast from August, I showed how to use Alt+F11 to set the scroll area for a specific cell. This is a great way to prevent users from accessing hidden rows or columns. However, as our viewer pointed out, this setting is not saved with the workbook, making it useless. But fear not, we have a solution!
By using just one line of VBA code, we can ensure that the scroll area setting is always applied when the workbook is opened. Don’t worry, VBA may seem intimidating at first, but it’s actually quite simple. Just hit Alt+F11 to open the VBA window, then navigate to the Project Window and open the Microsoft Excel Objects folder. From there, double-click on the workbook and you’ll see the code for that specific workbook.
In the code, we will use the Worksheets function to specify the sheet we want to apply the scroll area to, and then set the scroll area to the desired range. This way, every time the workbook is opened, the scroll area will be automatically set, preventing users from accessing hidden rows or columns. Thank you to our viewer for bringing this to our attention and helping us find a better way to use the scroll area in Excel. As always, if you have any questions or suggestions, feel free to leave a voicemail at 866-581-0221. Thanks for watching and we’ll see you next time for another netcast from MrExcel!
Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
Table of Contents:
(00:00) Listener points out a mistake in a past podcast
(00:28) Explanation of the mistake and listener’s feedback
(00:39) Importance of the scroll area feature
(00:49) Previous tip on setting scroll area using Alt+F11
(00:59) Issue with the setting not being saved with the workbook
(01:09) Purpose of the scroll area feature
(01:19) Solution using one line of VBA code
(01:29) Step-by-step guide to accessing VBA window
(01:52) Code to reset scroll area every time the workbook is opened
(02:30) Importance of listener feedback and improving tips
(02:40) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Better way to set scroll area using VBA
Excel tip for setting scroll area
Excel tutorial on scroll area
Fixing scroll area issue in Excel
How to use Alt+F11 in Excel
MrExcel netcast on scroll area issue
Preventing hidden rows in Excel
Resolving scroll area problem in Excel
Saving workbook settings in Excel
Using VBA to set scroll area in Excel
VBA code to set scroll area in Excel
YouTube video on setting scroll area in Excel
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152723/
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!
Please take the opportunity to connect and share this video with your friends and family if you find it useful.
