I am attempting to set a range of cells (say c5:h25) that can be scrolled through horizontally whilst the rest of the sheet remains frozen.
I'm trying to set this so the scrollable range will automatically expand horizontally as user data is added to the last (h etc) column.
I have a situation which I hope is fixable. I have frozen panes above and to the left of cell F5, therefore all columns to the right of column E scroll left and right and all rows below row 4 scroll up and down as required. My problem is that I also need the whole top row to remain static all the time.
View 3 Replies View RelatedI have an Excel sheet that I want to scroll up and down and I want an area in the upper right-hand corner to display static information. This information could change as more input into the sheet is performed, but it always must remain in the upper right-hand corner.
For example, let's assume the sheet has 200 rows to enter in a credit card application. There would be an area in the upper-right that would display information for some of the cells that were typed in. So as you type information, labels would display information from the form. I know that I can program the selection change event to re-position the box and labels, but if I scroll off the screen, the area does not stay there until I click on a cell on the page. I am using XL2003.
What is the benifit of the worksheet function AREAS?
How can we use it with other functions?
i am making a spreadsheet for recording employee telesales activity each day.
administrators will update a shared workbook each day with the information
employee names are across the top and the activities are down the side (calls, CVs sent, interviews, meetings) each day has it's own block of data. on a friday I would like to run a macro to select these blocks and paste them into the first free row on a separate worksheet.
i have something similar working elsewhere which copies one column and pastes that into the last free column of a new sheet but i have no idea how to do it for rows going down a page. I have attached a file example to clarify.
I have a worksheet with a column("A:A") of about 1000names. The user will start at the top and work down, entering stuff into other columns for some ofthe names. My need is: How do you automatically scroll, with code, say row 48to the top of the worksheet when the user reaches row 48 with?.
View 2 Replies View RelatedWhen the user scrolls the worksheet (using a scroll wheel, or the scroll bars) I would like to have a VBA-code triggered, but in Excel 2003 there is no "VBA-event" for scrolling...
Is there any way of trapping that a scroll has happened, even if the selection didn't change?
I have an excel sheet embedded in a Powerpoint presentation, but I have the problem that users are editing the sheet and accidently or unknowingly scrolling around. This results in the window resizing itself when the exit the object and messing up the appearance of the presentation. Is there a way of easily preventing scrolling (either via VBA or other)? I can obviously hide the scroll bars but that does not prevent the use of the mouse's scroll-wheel. I have also already hidden all unused columns and rows so that they cannot oversize the window.
View 5 Replies View Relatedable to keep a chart stationary while scrolling through a worksheet entering data. A few points:
1) 'Freeze pane' wouldn't work for me since the chart is too large and I'd prefer to have the data sheet unfrozen for easy perusal.
2) I saw mention of opening a separate window with the chart in it, while having the workbook window aligned next to it. I don't know if that can work, but I'd also prefer to just have the chart on the worksheet and stationary.
When I limit the scrolling area in a worksheet, and it works there and then, when I close and reopen the whorkbook, the limiting does not work.
This is how I do it:-
I Right-click the sheet tab for the sheet which I want to place a limit.
In the resulting Context menu, I choose View Code. The VBA editor appears and asks for the password to view the code. I keyin the password
I then set in my limit as B2:O25
Then I close the VBA Editor and display the worksheet where I try and I find it working.
I close and save the workbook.
When I reopen the limiting has disaapeared!
I have a workbook with 8 sheets, one of which is the "main" sheet that holds all of my user data for logins at my job (I'm in IT). There are about 4300 users and 23 fields for each user, so there are 4300 rows in the sheet. Other sheets in the book are important, but not as much as this one.
The workbook has a total of over 1000 lines of VBA code right now, as I'm building a tool that finds users, adds users, modifies users, etc. (I know, Access would be smarter for this, but for some reason they want me to use an xls). Most of this code works with the sheet that I'm having the problem on.
This ONE sheet has seemed to disable the functionality of the scroll wheel, and I can't use Page Up or Page Down while in the sheet either. Also, if I select cells with VBA (Sheet2.Range(<range>).Select), it will select the cell, but it will not draw focus to that cell on screen (the sheet will not move at all). The other 7 sheets in the workbook have all regular functionality.
This just started happening a couple days ago, and I've been building the code for about a week, so I'm wondering if it's something in the code...?
Does anyone have any idea what might cause this?
how do you freeze the top row (header row) so that it stays at the top of the worksheet as you scroll down?
View 3 Replies View RelatedIf I had =A1 in B1, could I "freeze" B1 such that its value updates ONLY upon workbook open?
View 10 Replies View RelatedI am looking to freeze row 18 in an excel sheet, however in doing so this really limits the amount of rows below row 18 that can be viewed and scrolled through.
I would like to know if there is any way that you could freeze row 18 and when you scroll down on the worksheet, you can scroll down past rows 1-17 and when row 18 reaches the top row it would lock there? And you would then be able to continue scrolling down the worksheet beyond row 18..
I am having problems with the VBA Editor freezing at the most inconvenient of times when writing code and then clicking to the Sheet I'm working on, it just freezes up the whole of Excel and it has to be shut down and opened up again.
It seems like a memory problem at first, but there is sufficient memory
( 768Mgs, OS is XP Pro, Excel 2000, 9.02).
The work is not lost becuase I'm able to Save even at PC Re-boot, fortunately.
I've tested on 2 different PC's and the same happens. The only way to avoid the freeze it seems, is before I switch to view a worksheet, is to Save then click off VBA Editor and re-open.
When doing so, another symptom is it seems a huge amount of memory is freed up, ( pardon the terminology)
There is also a message that pops up when re-opening that same workbook that there are Formulas Linked to another workbook, but unable to find any after tediously searching every worksheet by using "Find, Look in Formula" there is nothing found, yet.
This freezing may happen twice a minute whilst pointing the mouse cursor from any VBA Edit window or worksheet and it's getting to a point of repetitiously having to Save, click off VBA Editor, and Click back on again each time I need to look at a respective Worksheet.
I would like to have the column titles stay so I have frozen row 1. But I would also like to freeze row 200 so I can keep up with the running totals at the bottom of the form as I fill in information. Is it possible to freeze a pain at the top and the bottom?
View 4 Replies View RelatedI need is for a block of cells (A1:I4) to be visible at all times. I've frozen the sheet so that the top four rows are always visible, but unfortunately the sheet is quite large, horizontally-speaking, so that when users scroll horizontally, important stuff scrolls off the screen.
View 9 Replies View RelatedI have a table that is frozen in b5 meaning that the first column ("A") and rows 1-4 are frozen. The table is large with ever expanding rows. What I want to do is, a good few rows down (say row 300), freeze the row at the bottom of my view and use that row as a mean average row. Therefore allowing me to enter much more data above, but constantly be able to see the averages of the data I am adding.
View 3 Replies View RelatedHow can I lock the contents of an array so that they won't disappear when using auto-filter. "Freeze Panel" doesn't seem to do the job properly.
View 9 Replies View RelatedCan we freeze particular cell and sheet means no editing is possible for those particular cell or sheet.
View 1 Replies View RelatedI am using Row 3 as a my template to insert a row for every new line of data i enter. I have a macro that copies the format and formula on this template row to insert at the end every time. The problem is on ROW 3 Cell F i am using a Today() function and everytime i insert a row the date is populated. But the next day the date automatically update.
In my search on this forum, i found this macro to supposedly lock in the dates, but i doesn't work.
VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range("F2:F10000"), Target) Is Nothing Then
storage = Target.Value
Target.Value = Format(storage, "dd/mm/yy")
End If
Application.EnableEvents = True
End Sub
How can I freeze a sheet's tab location so it doesn't roll off of the screen? I have a workbook with 30 worksheets all hyperlinked off of the 1st sheet which is the INDEX sheet. The INDEX sheet only exists to make it easier for users to go directly to their desired sheet without tabbing horizontally through many sheet names. However when users use the bottom left arrow buttons to tab through the worksheets, the leftmost INDEX worksheet tab scrolls off of their screen. I realize that they can right click on the left/right tabbing arrows to see a list of all worksheet names, but it would be nice if the INDEX worksheet tab never rolled off of the horizontal tab scroll bar.
View 2 Replies View RelatedI have a progress bar on a form......it is there for no reason other than for me to practise such things...the code came off these forums and the bar is powered by a random number...
now my problem is the moment it start running i cannot do anything on the form....i would like my bar to do it's thing but still be able to click on the cancel button to get out of the form any help on this would be fantastic
Private Sub UserForm_Activate()
Dim dTime As Date
Dim i As Integer
Do Until i = 100
For i = 1 To 100 Step 100 / 8
dTime = Now + TimeValue("0:00:01")
Application.Wait TimeValue(dTime) '
ProgressBar1.Value = RandNumGen(1, 98, 1)
'AboutScreen.Repaint
Next i
'AboutScreen.Repaint
Loop
End Sub
Here is my code. When it pastes it takes so long my comp freezes up. How do i fix? ....
View 9 Replies View RelatedI am trying to write a vba code to change the zero values of the sheet to either 1, -1, or 0 according to certain rules. my sheet looks like this:
chcount=10chcount=10
0000
0000
0000
0000
1111
0000
1111
0000
0000
0000
0000
18111
0000
-1-1-1-1
0000
0000
0000
0000
-16-1-1-1
Basically I need the macro to select clusters of zeros in each of the columns with a label of 10 characters (this is an arbitrary number I chose when writing the macro), and if the values of the cells immediately above and below the cluster are the same, change the value of the zeros to the value of those cells, otherwise, the value of the custers remain as zero. So far I have the following code.
I'm using Excel 2003 and was wondering if there was a way I could Lock/Freeze the contents of a column. For Example:
Cell A1 has the formula =B1+C1+E1 - figure shown is £405
Cell A2 has the formula =C2+D2+E2 - figure shown is £650
Is there anyway I can freeze column A so when I delete column B,C,D,E ect the correct figure will still show?
I have a worksheet (Data Lookup) that initially I just required row 1 to be frozen. Now my worksheet would be much easier to read if I was able to freeze row 115 and row 263 when they reached the top of the sheet respectively.
e.g. Row 1 frozen, when I scroll down the sheet and row 115 hits row 1 that would also be frozen and the same with row 263 when it comes into contact with row 115.
I have looked around and cant find a solution and to be honest I'm not sure if it is even possible.
I know I can freeze panes eithe across a column or row but is it possibleto do both at the same time so that I can have a header row and a few columns on the left of the screen frozen?
View 2 Replies View Relatedfreezing columns and rows it does not seem possible to freeze MULTIPLE rows AND MULTIPLE columns simultaneously in the same spreadsheet.
In my spreadsheet I would like to freeze columns A, B, C as well as rows 1 - 8.
click on cell A4 -right click - insert cell- shift to right, so figure 2 moves to C4 pushing the yellow cell to D4 right?
what am trying to do is to make column c to be frozen or locked or whatever you want to call it, so if I insert a cell in A4 shifting to the right, the value in B4 jumps to D4 without shifting the yellow cell