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 RepliesIve 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
I've just finished my project (with loads of help from you guys) and wondered if there was any way of making it look less like the spreadsheet it is when it's opened.
I've removed everything I can and I'm left with a vertical scroll bar (needed) and the top menu bar (not needed).
Is that about it or can I get rid of that, and maybe size the window accordingly when the workbook is opened (so the dead space on the right is not seen) and perhaps stop the vertical scrolling at the bottom of the sheet so it doesn't scroll down to 65555 and the dead space under the sheet isn't seen.
I like the look of when only the used columns and rows are shown. I like to hide all unused columns and rows, and have the background and a minimalist spreadsheet.
HOWEVER, is it just me, or does Excel move a lot slower when thousands of rows and columns are hidden? Particularly, opening files seems to be slower. I'd love to delete them entirely from existence, so Excel only has a few rows and columns to work with, but that doesn't seem to be an option.
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 RelatedI 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]....
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 View RelatedI 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.
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?
I have got a spreadsheet which is being used as a database. I would like the spreadsheet to close automatically if the user has not "touched" it for a specific amount of time (say 1hr).
2 scenarios:
a/ The user is working in Excel but with another workbook.
b/ The user has the excel spreadsheet open but is working with another application, say Word
I experimented with Workbook_WindowDeactivate but I do not know how to stop the process if the Workbook is reactivated (Maybe a DO ...LOOP calling a function returning a True/False statement on the event Workbook_WindowActivate !!). My way of thinking is the following
Workbook is deactivated
Start a timer
If the workbook is not reactivated with 1hr, save and close (no user intervention wanted)
Else stop timer
Repeat process
how can I hide the row no 13 when I select "CLINKER" in E3 cell. I have used this code but its giving error.
View 10 Replies View RelatedI have a project that needs to be done today. What I'm trying to do is hide an area of a worksheet using a button click, then display that area when the button is clicked again. This area is approximately the lower right quadrant of the worksheet, so it shares column and row information with other data that must remain visible. Is there any way to do this?
Alternatively, I was thinking about making a duplicate of the worksheet, but omitting the area I want to hide on the duplicate sheet. The button would then hide and unhide the two worksheets, hopefully making it have the same effect as hiding or unhiding just that area. If I do this, I would need to maintain the exact same data in the visible area of the two sheets.
I am trying to hide all rows and columns with zero balance in the cells with the use of a macro button.
View 8 Replies View RelatedI have a spreadsheet which contains about 800 rows. Each row contains a column with the name of a different workbook. I need to scroll through each cell containing the name of a workbook and open that workbook, extract two specific cell contents from each workbook opened, and paste that information into the two cells next to the original cell containing the workbook name.
Example:
Summary Workbook column C: contains names of workbooks.
For each workbook name, I need to open the respective workbook.
Then in the first tab of the workbook I need to extract the information in E6 and K7 and copy that information back into the summary workbook in the two cell to the right of column C.
I currently have one spread sheet. At the top of that spread sheet there is an object that hyperlinks to cell A180. When i click that hyperlink, it take me to cell 180, but it appears at the bottom of the spreadsheet, rather than bringing it all the way up to the top of my current viewable screen.
View 3 Replies View Relatedto retrieve the scrollrow and scrollcolumn values for a worksheet just before its workbook is deactivated. I am currently using the following
Private Sub Workbook_Deactivate()
msg = MsgBox(ActiveWindow.ScrollColumn & " " & ActiveWindow.ScrollRow)
End Sub
Unfortunately, this isn't working the way that I intended. If, for example, I deactivate the workbook containing this code by switching to another open workbook, the msgbox displays the scroll row and column from the active sheet in the new workbook that is opened. How can I "catch" the scroll settings for the workbook containing the above code before it is deactivated?
I regularly utilize .xlw files to open sets of workbooks all at once. Usually, they're made up of 10-15 workbooks, using the cascade arrangement.
Problem is, each time I re-open my .xlw file, the workbook that was supposed to be in the top is hidden. If I re-configure and re-save the .xlw file, the top workbook still disappears.
If I open the native file for the top workbook, it is not hidden when I open it.
How do I stop a user hiding a workbook using VBA? I have tried:
Private Sub workbook_deactivate
If windows(myfilename).visible = False Then
windows(myfilename).visible = True
End If
End If
This works but when I try to close all files sometime I have to press the top cross twice. This may be because one workbook references the rest.
1. I'm looking for a better way to get a UserForm (frmParts) to show right from the start as soon as I open the workbook while hiding the application. I use the following when I initialize the application:
Code:
Application.Visible = False
frmParts.Show
2. Later on, I add some parts to my inventory, but if I don't make the application visible, it doesn't save the data. So, I have to make it visible and then hide it again. Is there a right way to accomplish the save without having to unhide/hide the application? This is the applicable code I used (ComboBoxes & TextBoxes involved):
Code:
'copy the data to the database
Application.Visible = True
Application.ScreenUpdating = False
With ws 'ws=Inventory
[Code] ..........
I am trying to write a macro that will do the following:
Copy workbook "X" as a new workbook, but name the new workbook from a cell entered in cell B2 of the newly copied workbook. The final process is then to hide workbook "X".
[URL]
"Is there a way to insert a column in a workbook that doesnt run all the way through the page?" Is a page break a potential fix?
I've set up a short bit of code to select the print area for several pages in a workbook, but have no idea how to get the code to "activate". I tried placing a command button on a page to activate the code, but for some reason it won't see or act upon any sheets outside the sheet the command button is on. Same goes for the "event" proceudres on pages (activate, change.selection, etc).
View 9 Replies View RelatedI have a worksheet that has headings along the and summary stats down the right and bottom of an area where details can be entered.
Depending on a persons user name columns are hidden/visible so that they can only enter information relevant to their section. The workbook is also shared.
What I want to do is stop people from being able to overwrite the headings or formula that calculate the stats but as the workbook is shared and the VBA that hides/unhides the columns needs the sheet to unprotected I am having to use code to prevent this: ....
I have a range of cells (L1:Q1) I want to copy from a worksheet called Email from one workbook and copy it into an already created and saved workbook called 'TDocuments.xls' located in the root of my C: drive.
Thing is I need to paste this range into the first blank row of TDocuments.xls, so it will need to find that first and then paste the values (without formulas) into the TDocuments.xls and save it.
I need a code (and where to put it) to open my application excel with only the userform so, with the workbook hidden.
Application.Visible=False doesn't work because it close all excels and moreover I can see the workbook for 1 or 2 seconds before hiding.
I want to create a macro that will allow me to copy a specific area or dataset of one sheet to all of the other sheets within a workbook. For example, on my first sheet, i want to copy A1:C3. I want that information to show up on all the other sheets in A1:C3.
View 2 Replies View RelatedI 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 RelatedIs it possible to lock portions of spreadsheets? Like a certain range
Also how do you get the HTML posting thing to work?
If you look at the workbook I have attached, I have made a mock up of a file that I have to use. Cells A1:F12 are part a "Situation Status" file. My team uses this file to update that status of certain events. It shows the current planned estimated completion date for each event in each area. We are required to strikethrough every time the estimated completion date slides out. In some cases, the dates have move 3 or more times so there are cells with multiple dates with strikethroughs. The current date is always the last date in the text string and does not have a strikethrough.
Cells H1:J17 are part of a matrix that I am making to try and capture only the most current ECDs. I want I3 to show "8/10" instead of "8/1 8/3 8/10."
The Event Status and Event Matrix are part of two separate files that I have. The Event Status file is constantly changing so I can't simply say =RIGHT(B4,4) because I could wind up showing the date of a different situation.
Is there a way to lookup the situation name (i.e. "Situation 1"), the Area number, and the event number and return ONLY the latest ECD (the date without the strikethrouh)?
I have 4,300 cells populated with data. I have sorted the data and now I need to rank the data in 1% increments. For example, the first 1% of data (43 cells) will be ranked #1, the second 1% of data (43 cells) will be ranked #2 etc. How could I automate this process? I was trying to figure something out with the "Rank" command but that doesn't seem to work in this situation.
View 3 Replies View Related