Lock & Limit Scroll Area
Jun 16, 2008I want users to be able to scroll top-down or sideways up to a certain point only.
What is the VBA code to accomplish this? Let's say for example I want to limit scrolling from A to L rows 1 to 50 only.
ADVERTISEMENT
Limit Scroll Area - Code Activates Only On Mouse Or Keyboard Input
Apr 21, 2007I have a worksheet that serves as a navigation page for the workbook and use the following code to restrict from scrolling off the viewable content. The problem is that the code doesn't kick in until either a click if the mouse or keyboard input. This initially leaves the worksheet open for scrolling until input is received.
How do I make this code activate when the wb is loaded? Code in the Thisworkbook module already makes the menu the visible sheet, and code limiting the scroll area is in the sheet module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.ScrollArea = "A1:L11"
End With
Range("A1").Activate
End Sub
I've also tried using "Range("A1").Select", but it doesn't work either. How can I get this to work correctly?
Scroll Area Set But Won't Stay Put?
Oct 29, 2007I set the scroll area on a worksheet and saved the file. The next time I open the worksheet the scroll area is no longer set.
View 7 Replies View RelatedLock Size Of Horizontal Scroll Bar?
Jan 23, 2014my spreadsheets consists of multiple sheets but i have hyperlinks to open those sheets on a cover sheet. what i want to do is stop people making the horizontal scroll bar smaller to expose the sheets. is this at all possible?
horizontal bar.JPG
Search Selection With Scroll Area Set
Sep 14, 2013I have a spreadsheet form with a bunch of cells receiving and writing data via vba. On the form is a search box. The data is on the same sheet as the form to prevent flickering by activating another sheet. The problem is that I cannot select the search area and set the scrollarea or the "Find" quits after the first find:
if I have 2 jacks then it will find the first and not the second.
If I don't set the scrollarea of course it goes to the selected cell and moves the form out of the picture.
If I select a cell in the form area after the search, the "Find" is reset to the beginning and locates the same cell again.
Here is the code:
Code:
On Error GoTo DataNotFound
Cells.find(What:=txtFindText, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate
[Code]....
Locking A Large Scroll Area
Feb 28, 2007Can a scroll area be locked at any size using VBA code? I am able to lock area A1:J20 in the Private Module of a worksheet but nothing larger.
View 6 Replies View RelatedScroll Area Not Saving When Workbook Closed
Nov 15, 2006Ive tried disabling scroll on sheet 1 of my workbook by entering the cell range into the properties window in VBA. However when I save the sheet and reopen it, it seems to reset and are able to scroll again.
I tried using the following:
Private Sub Worksheet_Activate()
Worksheets(1).ScrollArea = "A1:Q51"
End Sub
and a few variations of the above but this dosnt seem to be working. The only other code I have in the workbook is under "thisworkboko" which is this:
Private Sub Workbook_Open()
Application.DisplayFullScreen = True
End Sub
Scroll Area And Hiding Unused Portions For Entire Workbook
Jun 27, 2012I use all of column A, B, and C, but i only use D1 to H44 other than that. Is it possible to hide everything to the right of Column I and below D44:H44, but leave the scroll area for columns A B C able to scroll to the bottom?
View 6 Replies View RelatedLimit Print Area To Cells With Values
Jun 8, 2007In the range A1:H89 I have expanding and retracting values - all of which is determined by the numbers in column A. Column A is simply A1+1 copied down. The value of A1 is determined by the value of another cell, based on age. If the value in column A exceeds a specific figure (let's say 50), formulas in columns B - H result in answers of "" (this is "nothing"?). Assume that the value of 50 is reached in A20. All cells from A21 (inclusive) down, including B21 - H21, will have the values "" in them and show up as nothing on my screen. When I print, I'd like to only print those cells where data is, i.e. cells A1:H20.
Sub Drukwerk()
With ActiveSheet.PageSetup
.PrintTitleRows = "$7:$9"
.PrintTitleColumns = ""
End With
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = "A1:H" & LastRow
' ActiveSheet.PageSetup.PrintArea = "$A$1:$H$89"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "".......................
Restricted Scroll Areas Won't Scroll
Nov 26, 2007I am using the toolbox/ properties method to restrict the scroll area in the sheets of my workbook. However, for some sheets when i keep the right hand arrow pressed, the screen will not scroll to the last column. (i.e. the screen display will not move with the selected cell box)
View 2 Replies View RelatedControl Scroll Bars: Remove The Scroll Bars From Just One Worksheet But When Unticked
Jan 13, 2007In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?
View 2 Replies View RelatedPrint 2 Selected Area (highlighted Area)?
Apr 11, 2013How can I print 2 selected area (highlighted area). I know there is a trick to do that. Right now I can only select (highlight) one area to print, but would like to know how to print 2 or more areas at same time
View 7 Replies View RelatedProgrammable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells
Feb 25, 2009Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction
View 2 Replies View RelatedGraph The Following Data In An Area Chart With Line Charts Superimposed On The Area Chart
Nov 1, 2006I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
How To Set Fixed Print Area In Excel So Cells Within Print Area Can Still Be Modified
Feb 12, 2013I have a workbook with multiple sheets that I need to print.
Is there a way to create a fixed print area within which you can mess around with formatting without extending or shrinking the print area?
I have tried adjusting the margins settings so that they are all the same, but this does nothing to keep a fixed print area.
Lock Sheets With Formulas And Lock Sheets With Values
Jul 2, 2009do you know if there is vba code to lock formulas so someone sees but does not touch and the same for values see but cant change??
my sheets with formulas are SUMMARY and Sheet2
sheet with value is TABLE
Scroll Box Dirrectly
Nov 19, 2009I am trying to make a scroll box so I can enter data into the scroll box dirrectly. Such a simple thing.
View 11 Replies View RelatedCan't Scroll Down On Excel
May 13, 2013I'm trying to scroll down my work sheet but it's not letting me. Every time I move the scroll bar down the page it just keeps showing me the same part of the sheet eg cells 1-30 when I want to see all the way down to the bottom. I have some 20,000 lines I need to scroll through but I can't see all of them. To add to this, I keep having lines hide between other lines so theres a big gap like going from line 120 to line 4000 and I have to manually unhide them every time to see them.
View 2 Replies View RelatedList Box Scroll Bar
Nov 14, 2008Does anybody know how to force the scroll bar on a listbox to be at the bottom rather than the top when filled with data?
View 6 Replies View RelatedCan't Scroll Back To Top
Dec 26, 2008I have a spreadsheet with some scripting that automatically emails the report to whoever is on the sheet. I'm not sure what kind of scripting it is. I think my boss found it somewhere.
Whenever I scroll either down or up, the first 3 rows disappear and I'm not able to scroll back up. The scrollbar shows that I'm not at the top, but it won't let me go back up. The first 3 rows contain the button to send the email. If I close out without saving and open it again, its fine, until I scroll up or down.
Percentage Value In Scroll Bar
Aug 23, 2009I've been trying to set the scroll bar min/max/small change to a decimal (percentage value) within the properties, obviously this does not work.
Since users can enter a value in the link cell for the scroll bar, how can I modify the current min/max/small change via macros so the scroll bar will show a min on 1%, small change of 0.25% and max of 25%?
VBA What Is The Command To Scroll
Apr 24, 2007Simple VBA What is the command to scroll to over "x" number of columns? and/or rows?
I just the active cell to move over 5 cells to the right for example?
Scroll In Textbox
May 2, 2007I use a very simple textbox in a userform for sequential output only (it displays status messages of a long program run). The box nicely scrolls vertically up the lines but only till the userform has the focus (i.e. it is the actual selected window). But when I change the focus to othe window - scrolling immediatelly stops. Unfortunatly when I activate a new Excel sheet in the background program, the userform lost the focus and scroll is stopping.
Has anybody a solution for it? I have already tried positioning the textbox with sellength/selstart but I was unable to start the scrolling again from the proper position. I know that VB has the scrolltocaret() funcion but it is missing in VBA.
VBA Search And Scroll
Nov 15, 2007I have a document that is rather simple but has 32,984 Rows.
What I would like to do is program the sheet so if I enter a six (6) digit employee number in A2 (A1 is a header) then all the rows would scoll up and stop just below A2 in other words strop at row 3, if this makles any sense,
Also what I woudl like to do is the same option in A2 and have the same effect but here the user would have to enter only the first 3 characters of a name and then the system would scroll through all the options and start listing them as above starting at row 3.
The end result for both options would be to highlight the desired sorted rows..............
Text Box With A Scroll Bar
Jun 15, 2008I am attempting to create a lookup spreadsheet. The big picture is this: I need a quick way to look up what paperwork i have and what i still need for each of my 200+ clients. I have created checkboxes and a document matrix where document names get color coded when a checkbox is checked next to clients name. Now i am attempting to add another feature where when the box is checked off i can see notes ive put in for that client. I picture it as a textbox with a scrollbar linked to a cell on a different sheet where the notes are stored. I cannot to get it to work, i am able to insert the textbox but the notes appear in a single Line, no matter how big the texbox is, which makes the scroll bar not function.
View 9 Replies View RelatedScroll Bar On Message Box
Apr 9, 2009I have a aged debt report. Historically this has caused problems as when a customer paid there debt all comments were lost and we had to start from scratch if they got into debt again. Also comments had to be transfered when the data was updated.
I solved this by adding a comments sheet and a short macro that stored comments based on customer name. A lookup then showed the latest comment on the main sheet.
The main sheet looks like....
Chart Scroll Bar
Dec 28, 2009if it is possible to create a chart which only shows a portion of the data series at once, but has the ability to scroll through all of the data points using a scroll bar.
I am aware of one method which changes the input data based on formulas linked to the "cell link" feature of the scroll bar, which gets the job done. I am curious if there is an alternative method where all of the data is charted, but there is a fixed chart "window" of sorts that shows only a certain amount of the data (like a web browser - you have to scroll down to see the rest of the page). The scroll bar would ideally only reveal what is already there, rather than changing the data range via a formula. Does this make sense?
Scroll Bar For UserForm
Jul 27, 2007I am building a form for use in Excel. The form is going to be rather long. I want to use the vertical scroll bar, but I can't seem to get it to work.
Besides enabling the vertical scroll bar, what other steps do I need to take in order to utilize this feature?
Remove A Strange Scroll Bar
Feb 27, 2009I dont know the real name about this scroll bar (like you can see in my attachment), so I cant search in the forum, sigh! how can I remove the bar in the red circle?
View 3 Replies View RelatedStop The Page To Scroll
May 28, 2009I have a spreadsheet with about 300 rows and columns up to AB. Is it possible to stop the page to scroll beyond the 300 rows and AB columns.
View 8 Replies View Related