Scroll Area #2

How do you set the Scroll Area  on a worksheet so that the ability to move 
around the sheet is limited to the selected area? 


0
9/20/2005 8:17:47 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
785 Views

Similar Articles

[PageSpeed] 40

Hide all other rows and/or columns.
************
Anne Troy
www.OfficeArticles.com

"Paul Dusterhoft" <PDUSTERHOFT@wi.rr.com> wrote in message 
news:L7_Xe.30304$mb4.1898@tornado.rdc-kc.rr.com...
> How do you set the Scroll Area  on a worksheet so that the ability to move 
> around the sheet is limited to the selected area?
> 


0
ng1 (1444)
9/20/2005 9:04:17 PM
Paul

Hiding the unused rows and columns then protecting the sheet is the usual
method.

Setting the scrollarea using VBA is another method.

Note:  Setting ScrollArea is good for that session only and only the
activesheet.  Has to be reset next time workbook is opened.

Best to place the code into a WorkBook_Open Sub in ThisWorkbook module and
specify which worksheet.

Private Sub WorkBook_Open()
   Sheets("YourSheet").ScrollArea = "A1:P60"
End Sub


Gord Dibben Excel MVP

On Tue, 20 Sep 2005 20:17:47 GMT, "Paul Dusterhoft" <PDUSTERHOFT@wi.rr.com>
wrote:

>How do you set the Scroll Area  on a worksheet so that the ability to move 
>around the sheet is limited to the selected area? 
>

0
Gord
9/20/2005 9:19:11 PM
You can also
Right-Click on your sheet-tab
Select View Code
If Properties Window is not shown press F4
In Properties Window, near bottom you'll see
Scroll Area - In blank box to right
enter desired Range, say    B2:G20
Exit out of VBE
Done




"Paul Dusterhoft" <PDUSTERHOFT@wi.rr.com> wrote in message 
news:L7_Xe.30304$mb4.1898@tornado.rdc-kc.rr.com...
> How do you set the Scroll Area  on a worksheet so that the ability to move 
> around the sheet is limited to the selected area?
> 


0
jmay (696)
9/20/2005 11:06:14 PM
Good tip Jim but this method is also lost when you close the workbook.

Has to be reset when re-opened, hence my Workbook_Open code.


Gord

On Tue, 20 Sep 2005 19:06:14 -0400, "Jim May" <jmay@cox.net> wrote:

>You can also
>Right-Click on your sheet-tab
>Select View Code
>If Properties Window is not shown press F4
>In Properties Window, near bottom you'll see
>Scroll Area - In blank box to right
>enter desired Range, say    B2:G20
>Exit out of VBE
>Done
>
>
>
>
>"Paul Dusterhoft" <PDUSTERHOFT@wi.rr.com> wrote in message 
>news:L7_Xe.30304$mb4.1898@tornado.rdc-kc.rr.com...
>> How do you set the Scroll Area  on a worksheet so that the ability to move 
>> around the sheet is limited to the selected area?
>> 
>

0
Gord
9/20/2005 11:27:48 PM
Wasn't aware of this feature (?). LOL
Thanks,
Jim

"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:1i61j151vgufgc3609d1lfnajp0qua0lm1@4ax.com...
> Good tip Jim but this method is also lost when you close the workbook.
>
> Has to be reset when re-opened, hence my Workbook_Open code.
>
>
> Gord
>
> On Tue, 20 Sep 2005 19:06:14 -0400, "Jim May" <jmay@cox.net> wrote:
>
>>You can also
>>Right-Click on your sheet-tab
>>Select View Code
>>If Properties Window is not shown press F4
>>In Properties Window, near bottom you'll see
>>Scroll Area - In blank box to right
>>enter desired Range, say    B2:G20
>>Exit out of VBE
>>Done
>>
>>
>>
>>
>>"Paul Dusterhoft" <PDUSTERHOFT@wi.rr.com> wrote in message
>>news:L7_Xe.30304$mb4.1898@tornado.rdc-kc.rr.com...
>>> How do you set the Scroll Area  on a worksheet so that the ability to 
>>> move
>>> around the sheet is limited to the selected area?
>>>
>>
> 


0
jmay (696)
9/20/2005 11:38:20 PM
Hi Paul

Right click on the worksheet Tab and choose View Code.
If the Properties window is not visible, press F4.
In Scroll Area, set the range of the area you require.

Regards

Roger Govier


Paul Dusterhoft wrote:
> How do you set the Scroll Area  on a worksheet so that the ability to move 
> around the sheet is limited to the selected area? 
> 
> 
0
roger1272 (620)
9/20/2005 11:48:50 PM
But even after saving the Workbook;
Your Scroll Area is Lost;
See Gord's reply to my suggestion above..
Jim

"Roger Govier" <roger@nospamtechnology4u.co.uk> wrote in message 
news:43309FE2.40202@nospamtechnology4u.co.uk...
> Hi Paul
>
> Right click on the worksheet Tab and choose View Code.
> If the Properties window is not visible, press F4.
> In Scroll Area, set the range of the area you require.
>
> Regards
>
> Roger Govier
>
>
> Paul Dusterhoft wrote:
>> How do you set the Scroll Area  on a worksheet so that the ability to 
>> move around the sheet is limited to the selected area? 


0
jmay (696)
9/21/2005 1:28:44 AM
Hi Jim

Thanks for the heads up!
I had not seen either your, or Gordon's post on my system when I posted my 
response. I equally was unaware that the setting didn't "stick" on saving.

In all cases where I use the technique to prevent users accessing parts of 
the sheet, I set it via code in the workbook open event, but thought that a 
manual setting would also work and be a simpler answer for the OP.

Thank goodness we never stop learning!!!

Regards

Roger Govier


Jim May wrote:
> But even after saving the Workbook;
> Your Scroll Area is Lost;
> See Gord's reply to my suggestion above..
> Jim
> 
> "Roger Govier" <roger@nospamtechnology4u.co.uk> wrote in message 
> news:43309FE2.40202@nospamtechnology4u.co.uk...
> 
>>Hi Paul
>>
>>Right click on the worksheet Tab and choose View Code.
>>If the Properties window is not visible, press F4.
>>In Scroll Area, set the range of the area you require.
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>Paul Dusterhoft wrote:
>>
>>>How do you set the Scroll Area  on a worksheet so that the ability to 
>>>move around the sheet is limited to the selected area? 
> 
> 
> 
0
roger1272 (620)
9/21/2005 10:36:55 AM
Reply:

Similar Artilces:

Gray area at spreadsheet
When I open various Excel spreadsheets, the entire area below the data is all grayed out and I can't make it disappear. I want to copy and paste some additional information in that area but when I do, it stays gray. I've checked the fill and pattern buttons, but neither of those rectify the problem. The rows below your data may be hidden. Click the small grey rectangle above the number 1, and to the left of the letter A. On the top toolbar click > Format > Row > Unhide. HTH George Gee "barlep" <barlep@discussions.microsoft.com> wrote in message news...

Transposing #2
I have a worksheet that has student numbers in column 1 and then their 15 course requests across columns. I need to get that information into a row format that lists their student course requests in rows, i.e., A1:A15 would contain their student number and B1:B15 would contain their course requests. Any suggestions on how this can be done? -- scorer1 ------------------------------------------------------------------------ scorer1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24644 View this thread: http://www.excelforum.com/showthread.php?threadid=382316 ...

Named range using conditional formatting #2
Hi, I have a named cell called Econvert which contains the conversion rate from Dollars to Euros. I am creating a template for others to use and I need to add a conditional format for when users forget to divide their inputs by the Econvert. For example, if they enter a salary number as $40,000 I want them to divide this number by Econvert so that the end result is =$40,000/Econvert. I would like the cell to become red if they forget to divide by Econvert which is why I think that conditional formatting would help. Any ideas? Thanks in advance. ...

AutoArchive #2
Is it any limitation to setup outlook rules wizard? say, number of rules, etc? thanks! cm ...

Which Upgrade Do I Need? #2
I thought I posted this an hour ago, but it didn't take. I am trying to help someone upgrade their copy of M/S Office. They are running OS 10.3.6 Their M/S Office Apps all say they are version 10.0.0 When I downloaded what I believed was the correct Updater, version 10.1.6, I got the alert message that Office X 10.1.6 Update cold not locate the correct version of Office on your machine. Please see the requirements on the download page to ensure that you are running the correct Update for your version of Office. On the Macopia page, Office 2001 is for OS 9 and they don't have O...

Seks v polnolunie #2
Nedotroga znakomitsya s dildo ...

Outlook spellcheck #2
I am having problems with O.E. spellcheck. I keep getting an error message. I've tried to get help from Microsoft Tech Support but haven't had any luck. Apparently it has something to do with the connection between my Microsoft Word program and Outlook Express. Any suggestions? Thanks Vicki How To Fix spell checking in Outlook Express 6 http://www.tech-pro.co.uk/howto_010.html Spell check not working in XP Outlook Express http://www.michaelstevenstech.com/spellcheck.htm Spell Checker Not Working in Windows XP http://www2.cajun.net/~theriots/blk/xp_oe.htm#xp_spell_checker ...

Scrolling with CScrollView
Hi everyone, Let me start by saying that I'm rather new to scrolling. I have come accross the following problem. I created a new SDI MFC application that uses CScrollView with Visual Studio .NET 2003. Then I have increased the size in CScrollView::OnInitialUpdate() to 1000 by 1000. Finally I have added the following code in CScrollView::OnDraw(), // TODO: add draw code for native data here CRect rect; GetClientRect (&rect); CString str_scroll; str_scroll.Format(_T(" x = %i y = %i "), GetScrollPosition().x, GetScrollPosition().y); pDC->DrawText(str_scroll, -1, &...

SBS Default Web Site & Microsoft CRM Version 1.2 & Host Header Value
I have set up a test enviroment with SBS 2003 premium running MS CRM 1.2. I have a problem that both the default web site and the Microsoft CRM Version 1.2 web site have both been assigned the same 'Web site identification' in this case the server IP address 192.168.16.20. While the CompanyWeb has also been assigned the same 'Web site identification' it has had a host header value setup for it allowing it to be distinguished. My understanding it that there are three ways to distinguish web sites; 1. IP Address 2. TCP Port 3. Host Header Value The first two are not an op...

Scrolling sync subforms
Hi all I have a continuous subform which shows all claim details, that have Part(s) against them. The claims are a one to many with the Parts. I wanted to show a count on the form of parts against each claim. If I add a count to the recordsource query then I lose the ability to edit the data in the subform. I need to do this to add a Selector code if the user wants to mark the claim as a selection. Instead I have created another continuous subform, this has just the claim ref and the count of parts. Each form has the same sort order and a vertical scroll bar - is it possibl...

Scroll-me-down
Hi there, I am using RichEditCtrl and all the associated select, format, replace for adding text to the end of my rich edit box. However, I have yet to figure out a definative way of making it scroll down as far as needed (bottom of page if page length is greater than height), it sometimes works, if the cursor position is right at the very end of the text when the text is added programatically, but I need a way for when it is not. Could anyone give me any tips? - MR Mark Randall wrote: > Hi there, > > I am using RichEditCtrl and all the associated select, format, repla...

scroll bar gone
hi have money 2005 premium and in my checkbook area where you pick the category the scroll bar is gone and it only shows me the categories that fit in that window. if i go into split and select the categories from within there the scroll bar is there. Does anyone see this problem or have a fix for it. Thanks, minnoce944 In that nobody else has M05, it would be hard to comment. "Mike I" <minnoce944@hotmail.com> wrote in message news:u9aY7B1fEHA.384@TK2MSFTNGP10.phx.gbl... > hi have money 2005 premium and in my checkbook area where you pick the > category the scroll b...

setting print area for multiple worksheets at once
Hello - Looking for a way to set the print area on a large number of simiarly formatted worksheets at once. Ideally want to incorporate it into a macro that does some other basic stuff as well. Thanks for any suggestions! -- annabel ------------------------------------------------------------------------ annabel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26520 View this thread: http://www.excelforum.com/showthread.php?threadid=397879 Good morning Annabel This code will loop through all your worksheets in a workbook and se the range to A1:F30. Sub Se...

Can you reset size of bar in scroll bar?
When working in a large Excel worksheet, the size of the scroll bar diminishes to allow you to scroll through the entire sheet. After intentionally deleting a large portion of the worksheet, the bar size on the scroll bar remains the same. Is there a way to reset the size of the bar on the scrollbar? You have to delete the rows (not just clear the contents), and then, the easiest thing is to save the workbook. -- Regards Juan Pablo Gonz´┐Żlez "vsholl" <vsholl@discussions.microsoft.com> wrote in message news:C4CF4E91-D282-4FB4-85EC-A19C5343F18A@microsoft.com... > Whe...

Outlook Plus #2
Never heard of Outlook Plus! , where did you buy it ? Geoffrey McGill <anonymous@discussions.microsoft.com> wrote: > Never heard of Outlook Plus! , where did you buy it ? http://www.asm-soft.com/outlook/ -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the name, smiths-aerospace.com is the domain. I don't speak for Smiths, and Smiths doesn't speak for me. ...

Scroll the screen view with the scroll bar
On some of my Excel sheets, the screen doesn't scroll along when I move the scroll bar. It just jumps to the new location when I stop moving the scroll bar. What is the correct setting to change so that the screen scrolls at the same time? Do you use different versions of excel? In earlier versions, when you moved the scrollbar, you saw a little yellow tooltip show you what row you were on. In newer versions, the screen moved. (I've forgotten the version that first added the live scrolling. Maybe someone can chime in with it.) avbs wrote: > > On some of my Excel sheet...

Graph with too many points
Hi all, I have a graph with a lot of points, so I wanted to create some kind of horizontal scroll bar so I would be able to use a better scale and move around my time line ? Has anyone of you done something like that ? thx -- giselle ------------------------------------------------------------------------ giselle's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24222 View this thread: http://www.excelforum.com/showthread.php?threadid=378389 Hi, Jon Peltier has written an article on the subject. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=...

Outlook extremely slow #2
Outlook had been working fine until I installed Norton Antivirus. It now takes several minutes to load. Could this be the problem? Is there any way to fix this? Which version of outlook and Norton? Try disabling the email scanning and see if it speeds it up... also, NAV has an office scan you can disable in NAV's options menu. if you use word as your editor, that could be the cause. "HJ" <anonymous@discussions.microsoft.com> wrote in message news:0c2801c47838$72236340$7d02280a@phx.gbl... > Outlook had been working fine until I installed Norton > Antivirus. It ...

Tender Summary Report #2
We want a Tender Summary Report for a day (or days) including transactions, work order and layaway. Our current Tender Summary Report can only show the transactions. We've tried to update tendersummary.qrp, but couldn't work out. We still want the same group method: Register.description as first column, then tender type, Amount (or Deposit for Work Order), transaction #/Work Order#. Is there any way to do it? Thanks hi jny, There is one report avaialble under Utiliities > Crystal Report > RegAnaly.def (Register Analysis Report) . and I think this will help you what y...

mdbdata #2
What are *.stf files created in \exchsrv\mdbdata folder? http://support.microsoft.com/default.aspx?scid=kb;en-us;258542 Suren Pillay wrote: > What are *.stf files created in \exchsrv\mdbdata folder? XADM: .stf Files May Appear in the Folder That Contains Database Log Files http://support.microsoft.com/default.aspx?scid=kb;en-us;258542 -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms sp...

Is this possible? #2
What I'm trying to do is find a way to automatically update the hour meter readings on a second separate Excel spreadsheet when I update it on the first hour meter readings of the first spreadsheet. Sam As simple as linking the second sheet cells to the first sheet cells. In Sheet2 in A1 enter =Sheet1!A1 OR just copy the readings cells on Sheet1. Click on Sheet2,select a cell and Paste Special>Paste Link The not-so-simple is you may require more than this. i.e. you want the past hour's readings on the second sheet to remain and the new readings added. Post back with mor...

SharePoint Scroll Bar
I'm quite new to SharePoint and I was wondering how to disable the Scroll Bar in a content query web part. Thanks Submitted via EggHeadCafe - Software Developer Portal of Choice JQuery and partial views in an ASP.NET MVC application http://www.eggheadcafe.com/tutorials/aspnet/a8081c04-776d-4322-b403-84508b0ae3b5/jquery-and-partial-views.aspx Try to overwrite it with CSS overflow:hidden Regards, Shady Khorshed - SharePoint MVP <Daniel Arnold> wrote in message news:201015121218daniel.arnold@eaststaffsbc.gov.uk... > I'm quite new to SharePoint and I was wond...

Summation of 3 biggest numbers... #2
hi all, is there any quick method to add up 3 biggest numbers in a se of number? e.g. I have the following data in a row: 100 20 90 30 And I want to add up 3 biggest numbers, i.e. 100 + 90 + 30 = 220 Thanks -- lau000 ----------------------------------------------------------------------- lau0001's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3332 View this thread: http://www.excelforum.com/showthread.php?threadid=53144 thanks all, I have solved the problem by "large" function.:) -- lau0001 -----------------------------------------------------...

creation of synchronization log messages #2
I have outlook XP on win2k, and my deleted items folder is being filled by "synchronization log" messages at the rate of 1 per minute. They appear to be simply a log (no problems indicated). Here's a sample from the log... 16:44:06 Synchronizing Hierarchy 16:44:06 Synchronizing Favorites 16:44:06 Synchronizing Folder 'Inbox' .... 16:44:07 Done 16:44:08 Microsoft Exchange offline address book 16:44:08 0X8004010F Where is a setting in outlook where I can disable this? Thanks! ...

Vertical Scroll Bar #2
Hi I was hoping someone could help me with this... sometimes when i have a lot of data running down my spreadsheet the vertical scroll bar block becomes very small to accomodate the amount of data i have. but then when i get rid of a lot of the data the length of the vertical scroll bar block stays very small despite me only having say 50 rows of data. what i would like to know is whether there is a way to make the vertical scroll bar block come back to bigger, so it's easier to grab than when it is very tiny??? Thanks Nick Nick Select the first blank row below your data, say...