One of the fields I want them to enter is a library branch and I have used a data vaslidation list to provide a dropdown box.
To make it act more like a windows drop down box I have hidden 7 cells above this box with the 7 possible entries. This means that with autocomplete, if they start typing in the cell it will autocomplete the entry for them.
This work fine, but if someone presses enter from the cell above my hidden rows, one of the hiddenn cells is selected!
WHY!
I have no explanation for this behaviour, nothing like it has ever happened before, and I have no idea how to get rid of it.
I have a feeling the answer to this will be 'no' based on the searching I've done, but is it possible to allow formatting of rows/columns on a protected worksheet, yet prevent the user from unhiding some rows/columns I don't want them to see? I've allowed them to format rows/columns so they can "size" them to fit the text they enter, but that has opened up the ability for them to unhide columns. Using Shrink To Fit would be cumbersome due to the number of cells involved.
I have a sheet that uses Blocks of cells eg: A1:X10,A12:X22 etc to hold data/text etc.
I am filling colums A to V with data and Ranking and summing them in W & X, I don't need to show K, L M ...for example, until they are totally filled, so I hide them manually until needed.
Have a Macro to print "results" A1:X10,A12:X22 etc, and I set a "print area" or "print selected"...works fine to fit to 1x1 Page.
Problem: If I hide L, M, N etc, the macro runs fine but still uses the unhidden size of the selection to fit the page ie: I get A-B-C-D...-W-X as required, but the other half of the page is blank, because the size selected is for all including the hidden columns.
Q: How can I (Simply) print what's visable to 1x1 Page, fitting more in each time I unhide a column or two.?
When I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.
I have a spreadsheet with various hidden rows and columns that I do not wish the user to have access too as it contains too much detail for their purposes.
Copy/paste and copy/paste special also copies across the hidden columns and rows so what I need to do is to copy it to a new workbook but then remove the hidden rows and columns and just delete the existing formulas in the cells which total the data and insert a new autosum.
I have mapped data from Sheet1 to Sheet2 and created graphs in Sheet2 using the data. but when I hide the rows in Sheet2 the Graph is being cleared off. Need a solution so as to display the Graph in Sheet2 while hiding the rows in the same sheet.
I have a worksheet that is shared. It has hidden rows and columns. Problem is those hidden rows and columns are unhidden when someone apart from me opens the worksheet! Only way round it is to have the worksheet unshared. How do I keep the rows and columns hidden?
I need to find, then replace a particular value ("/0"), however, some cells may be hidden. And I just discovered that I can not replace the value of a hidden cell! Am I missing something, or do I have to unhide the row/column that the cell is in and then replace it? ...this does not replace the "/0" with "0" if the row the cell is in is hidden.
Sub test()
Dim rMatch Set rMatch = ActiveSheet.Cells.Find(What:="/0", LookIn:=xlValues, LookAt:=xlWhole) rMatch.Value = "0"
I need to average the columns of data and ignore both hidden rows and zero values. I have tried writing if statements as well as the subtotal function. Both functions either ignore null values or hidden rows but not both.
The system wont let me update a sample workbook but Im wondering whether there is a formula or combo formula for this.
I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.
GOOD =SUMPRODUCT(SUBTOTAL(103,OFFSET(B$2:B$5,ROW(B$2:B$5)-MIN(ROW(B$2:B$5)),,1))*(B$2:B$5="Pass"))
BAD (returns 0 and includes a circular reference) =SUMPRODUCT(SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),1))*($B2:$F2="Pass"))
Regarding the age old problem of trying to ensure macros are enabled in a workbook, using the process of hiding all sheets bar one:
Automatically Close Workbook If Macros Are Disabled
Reafid inserts some excellent code so that worksheets are hidden BOTH before closing AND on a normal Save event (I have also attached Reafid's zip file with the workbook that includes his/her code).
Having done much searching it seems to be the best solution out there, however I can still break it fairly easily and I'm worndering if anybody can stop me doing the following to break it:
Open the file in its restricted state (i.e. only Warning tab shown, all other sheets very hidden) and NOT enabling macros on opening. I'm therefore at the position Reafid intended; macros are disabled so all sheets are very hidden. Then to break it, in my personal macro workbook I have a simple macro that unhides all worksheets in ThisWorkbook, I run that personal macro and everything in Reafid's file is unhidden.
Reafid's file therefore has been opened with macros DISABLED and now all worksheets are visible.
I have a spreadsheet which I'm using as a log-in front for a series of other workbooks. The usernames and passwords are stored hidden cells within a "very hidden" worksheet. The workbook is protected and VBA code itself is password protected.
In short no-one can directly view the passwords (unless the password to unprotect it all is known.
What I have found is that someone can use a formula to reference the cells containing the user details. "=a1" for example.
Is the a method hiding the contents of a cell from excel itself? I want the vba script to be able to see the value, but any "=a1" formulas to return a blank.
I have a data set with rows of data, with a header row along the top. I want to sort my rows of data by column when the cell above the appropriate column is pressed. I know this seems cumbersome - but it fits in with the sheet. The sheet is protected, and we only want the user to be able to sort in this fashion. My problem is, with my code, I get an error when you select a range of two of the header cells. The case selection on the target.column gets confused. How can I check to see if it's just one cell? Is there a property of a range that reflects how many rows or columns it contains? I tried the following (marked with the ' )just from picking out of the pop-up list of properties of the Target range in SelectionChange, but obviously it doesn't work. My original code is the un-'-ed code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' If Target.Rows = 1 And Target.Columns = 1 Then If Target.Row = SortRow Then Select Case Target.Column Case PriorityColumn SortData PriorityColumn Case JobColumn SortData JobColumn Case NotesColumn SortData NotesColumn Case SupervisorColumn SortData SupervisorColumn Case ShutdownColumn SortData ShutdownColumn............................
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
I have put a 3d shape on the excel sheet, and would like it unclickable. Is there way to accomplish that with the sheet still clickable. I know that you can use protection from Tools manual to protect the sheet. Is there way to protect just specific object or range of cell unclickable?
I have a workbook with various pages that are all hidden except the main page, on the main page it allows users to select items froms drop down boxes that returns a figure to cell B7 on the selection page.
What i would like to do is press a command button and the hidden worksheet that relates to that figure in cell B7 opens which allows the users to print it then after printing or closing the workbook is hidden again.
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23 Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
I have data in B4:B55 and need a formula to return a count of rows, including rows that are blank. However, there are hidden rows that need to be omitted from the count.
I would like to have the cell A26 equal to "VAT" when I enter a VAT percentage and set the cell A26 to "NOVAT" or blank when the columns G:H are hidden.
I am trying to create an invoice but the invoice show #N/A in certian columns when there is no number which already have formulas, how do I modify them using the if function?
when I want to send by email I find I cannot copy these smaller sheets - every attempt at a copy will also copy the hidden columns.
How to copy a spreadsheet and NOT pick up the hidden columns?
I realise this might be nearly impossible or considered useless because no hidden columns would mean either making the copy with blank hidden columns or altering all formulas to suit a sheet which now does not have all the original columns.
I have a worksheet that currently has columns AB to XFB hidden. I want to unhide them, so I selected column AA and then drug the mouse over to the right (toward the empty space), intending to right-click and select "unhide". I do this all the time with no problems.
However, from the instant of my drag to the right, Excel has become totally unresponsive. I see it using 25% of my quad CPUs in task manager, but there is no active memory delta and it has been sitting for over 20 minutes now.
I'd rather not lose the work I just entered, but I can't seem to get Excel to come back to life by pressing escape or clicking in the worksheet. I've noticed slowdowns before when there is a lot of actual data in the cells selected, but these columns were empty to the best of my knowledge.
Is there a maximum amount of hidden columns within Excel? I have double-checked protection, and I have enough columns total for the action I'm attempting to do. I am using Excel 2003, and working on a rather large spreadsheet for a forecast. The basics of it is that I have macro's within buttons set-up so that users can hide columns and just view certain columns for approximately 30 columns per week and 5 weeks. And I can run the hide macro's for up to 4 weeks at a time, but when attempting to hide the 5th week it gives me a "Run time error 1004" Unable to set the Hidden property of the Range class.
I have a spreadsheet with rows as budget line item and columns for the months they occur.
I have a cell on each row (in column B) displaying the total of that row for all the months. (columns C to Z)
I want to be able to hide certain months (columns), and have the total not include those hidden columns; but the formula for the totaling cell ( "subtotal(109,C3:Z3)" ) appears to continue counting all the columsn, even the hidden ones
When you put a label in a column, that is wider than the column, and you don't wrap it or shrink to fit, etc. it "spills" into adjacent columns so the whole label is displayed. In some cases this might be desirable, but in others not.
You can prevent it from spilling by putting a blank space into the next adjacent column -- that truncates the display of the label -- but clearly this is not a good solution.
Is there a way to prevent labels from doing this so that only the portion of the label that is in the cell where the label is entered is visible on the spreadsheet? Of course you can see the entire label by selecting the cell itself, and that's what I want to do without it spilling into other columns.
I have several years worth of monthly data that I would like to keep hidden so it is printer friendly but I also want to have a chart of all that data... Is this possible? Only idea I have is to basically recreate the data sheet but I don't really like that solution since it adds steps to the process and increases the file size...
I have a linear count from 1 to 160 (J3:FM3) and I hide columns manually over time depending on a certain criteria. However, I would like to count how many columns I have left. I believe you need to use the subtotal function, but I do not understand how to use it.
