Locking A Large Scroll Area

Feb 28, 2007

Can 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


ADVERTISEMENT

Scroll Area Set But Won't Stay Put?

Oct 29, 2007

I 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 Related

Search Selection With Scroll Area Set

Sep 14, 2013

I 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]....

View 9 Replies View Related

Lock & Limit Scroll Area

Jun 16, 2008

I 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.

View 2 Replies View Related

Scroll Area Not Saving When Workbook Closed

Nov 15, 2006

Ive 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

View 5 Replies View Related

Scroll Area And Hiding Unused Portions For Entire Workbook

Jun 27, 2012

I 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 Related

Limit Scroll Area - Code Activates Only On Mouse Or Keyboard Input

Apr 21, 2007

I 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?

View 2 Replies View Related

Paste Area Too Large

Jun 7, 2007

i am trying to first of all find the column that Alias appears in the first 3 rows and then paste that entire column to a new sheet.
most is working.

however i get an error 1004 saying the area i am pasting to is not large enough or the wrong shape.

Private Sub CommandButton5_Click()

'
' merge Macro
' Macro recorded 6/7/2007 by Ray O'Connell

Range("A1:K3").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False...........

View 9 Replies View Related

Dynamically Set Print Area And Page Breaks On Large Sheet?

Feb 28, 2012

I am trying to dynamically set my print area and page breaks on a large sheet.

Basically I want to get the last column and last row in used in the sheet. And to create boxes (24x30) until I get all the way horizontally and vertically.

View 2 Replies View Related

Locking Different Cell Ranges Then Locking Whole Sheet With Macros

Jan 4, 2010

I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.

The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.

What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.

I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.

I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....

If anyone has any suggestions I would be eternaly greatful.

Passwords on the sheet for the different buttons are abc or cobra.

You can download my Spreadsheet here.

[url]

View 9 Replies View Related

Locking One Cell If Another Had A Value In It (conditional Locking)

Oct 10, 2008

I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.

View 9 Replies View Related

Restricted Scroll Areas Won't Scroll

Nov 26, 2007

I 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 Related

Control Scroll Bars: Remove The Scroll Bars From Just One Worksheet But When Unticked

Jan 13, 2007

In 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 Related

Print 2 Selected Area (highlighted Area)?

Apr 11, 2013

How 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 Related

Large CSV File: Too Large To Open. Split

Aug 6, 2003

I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).

Is there a way to split ( ) this file so I can open two files instead?

View 9 Replies View Related

Programmable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells

Feb 25, 2009

Using 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 Related

Graph The Following Data In An Area Chart With Line Charts Superimposed On The Area Chart

Nov 1, 2006

I 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.

View 3 Replies View Related

How To Set Fixed Print Area In Excel So Cells Within Print Area Can Still Be Modified

Feb 12, 2013

I 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.

View 1 Replies View Related

Code Locking Up

Apr 13, 2008

I'm trying to run the below code, but every time I do, it locks up excel. The code is running on 900 rows of data within the "loop to delete rows" section and in the main for loop there are 49 values to evaluate for the string strSA. how I can improve this code? When I step through it with F5 it works fine.

View 14 Replies View Related

Locking Column A Only

Dec 10, 2008

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 1) = Now
End If
Application.EnableEvents = True
End Sub

everytime i lock the column a by using the protect sheet, the vba does not work or debugs. is there a way for me to lock the cell in column a when data is inputted or changed in column b? i am using date and time for column a

View 4 Replies View Related

Locking Timestamp

Mar 27, 2009

=IF(D2<>"",IF(A2="",NOW(),A2),"")

Whenever I type into field D2 it populates current date and time in field A2 and down the A column as I write into current D columns. The next step I would like to do in which he was able to figure out so far is how to lock in the date and time after something has been typed into D2. If I make any changes to the D2 field it updates the date and time, which I dont want to happen.

View 3 Replies View Related

Locking Workbook

Mar 27, 2008

I have a user that is trying to lock certian cells within a spreadsheet but to allow certian users still to be able to edit these cells.

When this users is trying to do this and locks the cells it is locking for all users and not applying the "allowed" list of users to make changes.

View 9 Replies View Related

Locking Worksheets ...

Apr 1, 2008

is it possible to lock a worksheet. For example, i have a single worksheet in a workbook that i do not want people viewing. Is there any way that a password would have to be used to gain access to it? I have to keep the sheet inside this workbook because information is pulling from other sheets inside the workbook.

View 9 Replies View Related

Locking Cells

Oct 6, 2008

I did review some examples your members have already provided however I have a slight twist.

I have multiple users adding to a spreadsheet from which data is later copied to a master sheet. I need to lock ONLY cells which contain data when user chooses to save spreadsheet or saves speadsheet on exit. If the cells do not contain data, they should remain unlocked.

View 9 Replies View Related

Locking A Graph

May 25, 2006

how to lock a graph in an unprotected worksheet?

View 7 Replies View Related

Locking The Top Rows

Jul 28, 2006

top three or four rows so that scroilling down the screen leaves the title rows where they are .

I know that this is possible and have done it several times in the past, age however, dulls the memory and I have just forgot the procedure,

View 3 Replies View Related

Locking Numbers

Nov 9, 2006

my spreatsheet calculations work, but the overall thing is too large to print; i am running many numbers and formulas. I made them all finally refer to a small 'summary' box which i created...but when i try to transfer the box to a new worksheet so that i can print it and only it; it automatically has no references for the formulas on the new (blank) sheet, and comes up all errors. to the point, how do i lock the way a box appears (when it contains formulas) so that it will remain sensible after it is transferred elsewhere?

View 3 Replies View Related

Locking Cell If Value

Mar 29, 2007

I am trying to unlock a cell if another cell is a certain Value and if it is not that Value to lock again i have got as far as unlocking it but i can not get it to lock if the value is faulse. This is what i have so far!!

Private Sub Worksheet_Change1()
If ("K4") = "Daily" Then
ActiveSheet.Unprotect ("Password")
Range("F6").Locked = False
ActiveSheet.Protect ("Password")
End If
If ("K4") = "Monthly" Then
ActiveSheet.Unprotect ("Password")
Selection.Locked = True
ActiveSheet.Protect ("Password")
End If
End Sub

View 2 Replies View Related

Conditional Cell Locking?

Mar 8, 2013

Is it possible to have a formula in a cell, based on the value in another cell, but if the other cell value is not true then allow for data entry in the cell

So
J10 executes a formula if the value in I10 (a list field) is 'Cash and Shares' =IF(I10="Cash & Share",ROUND(IF(AND(K10<>0,O10>0),K10/O10,0),0)).

However if the value of I10 is anything other than 'Cash and Shares' then do not execute the formula in J10 but allow users to enter another value.

Note: the cell needs to be locked. The worksheet will be protected and will allow users to Select Locked Cells and Unlocked Cells.

View 3 Replies View Related

Locking A Certain Text Boxes, And Not Others

Feb 4, 2009

For example:

Locking a certain text boxes, and not others. Under review, protect sheet, the Edit Objects function is all or nothing

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved