When I protect my sheet and have the option to allow the users to autofilter the sheet, how come it doesn't work? I want to allow people to autofilter the data in the worksheet but not modify the contents of the cell.
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
I have spent ages creating a work diary and have protected the sheets using VBA however on finishing the sheet one important thing that needs to happen is the ability to copy and paste information from the boxes to another worksheet. i.e MONDAY - SUNDAY
Does anyone know if there is a way to unprotect a sheet. The person who created this spreadsheet no longer works for our company and although we can see the spreadsheet, the formulas are hidden so we can't figure out where the data is being pulled in from. Any ideas?
I have a worksheet that is password protected. The userforms used to input all the data have an initialize event that unprotects the sheet to input the data and then before protect the sheet upon closing the userform. This works perfect so that input data cannot be changed once entered; however, the problem I have is that I need to allow autofiltering so users can sort through their input data. I have a command button that unprotects the sheet and places the autofilters in the correct locations, but once this is activated the worksheet then is open for modifications by the users. Is there a way that I can allow autofiltering while still keeping the data protected? If .
I am trying to make it so a user can input a variable such as a date and then press a button which will autofilter a lot of data by what the user entered. The problem is some of the data I want users to be able to filter by needs to be filtered using "begins with" or is "greater than" or "less than". I can only make it so I can filter by "equals" when I try to do for example "begins with" using "*" it doesn't work out. Here is an example of my code
I have a spreadsheet with approx 12000 rows.8 columns. it represent material in our inventory and each row is a specific records that contains a lot of info. I need to be able to have a field in a top pane (could be top row i guess. or a small form) where i can start typing text and as i am typing, the spreadsheet auto filter the rows to display only whatever fit my parameters i am typing. a few example of what i will type in are:
bearing*2217 valve*gate*3*150 asco*8344
as you may have guess, "*" is a wildcard. The spreadhseet are generated using crystal reports over night, so i am not sure how i can or should use macros or add in for this..
I am able to autofilter a sheet to show only rows with a date in Column U but no "x" in Column BC. Now I want to be able to put an "x" in BC in only the filtered rows. The purpose is if someone enters a date in Column U, the macro will show only those rows minus the rows that have already had an "x" added to BC.
For the most part I've gotten this to work using but it will also put an "x" in Column BC, row 3001 on down.
How can I get it to stop at the last row?
Cells.AutoFilter Field:=21, Criteria1:="<>" Cells.AutoFilter Field:=55, Criteria1:="=" Columns(55).SpecialCells(xlCellTypeVisible).Value = "X"
I am trying to figure out how to save a worksheet's autofiltering settings and be able to re-create those settings.
I would start off with a spreadsheet with a few Autofilters on. I would like to make a macro that will record the initial Autofilter settings, then the macro would do a bunch of stuff that would need to 'Show All'. After these actions take place, I would like to be able to return to the initial Autofilter settings. I'm not even sure which VBA command should be used for this sort of thing.
I have a workbook whose worksheets use protection (don't want my calculations stomped on).
When the Protection is on, the search function does not work. You call it up, write what you are looking for, it accepts it, and then when you tell it to search it ignores you.
When you turn the protection off, the search function works just fine.
HOWEVER....Other workbooks I have with protection on its sheets do not share this problem, just this one workbook. And the problem is on all sheets in this workbook. The problem is not on any of the sheets in the other workbooks. I can't see anything different between them, but then I may not know what to look at.
Q1. How can we insert new rows in a protected sheet?
For example: In the attached sample protected excel sheet "insert rows.xlsx" , if for Employee ID and Name fields, the user has more no. of records than provided, how can he insert new rows considering the fact that Company ID and Name field are locked.
Or any way to address this issue via some other method like adding a scrollbar,etc.
I've locked certain columns on my worksheet so that users cannot overtype target dates etc. I've password protected the worksheet.
The password protection means that for some reason the users can't use the autofilters that are on the header row.
Can someone help me solve this problem; I still need the locked cells and password protection on the sheet but the success of the sheet depends on users being able to filter for specific rows using the autofilter....
I am looking for VBA code that will unprotect the Workbook, prompt the user for the number of rows to be added, find the last row in the Worksheet, insert the requested number and reprotect the Worksheet. I would like the location where the rows are inserted to NOT be dependent on the "active" cell.
I have a protected sheet to avoid delete of functions. In a specific area, however, i have merged cells of five columns in rows. Because in these lines i have write some text and overcomes the rows, after, what i do is: i unmerge the rows and choose 3 rows from 5 columns together and merge. Thus, the text is in one framework. In fact, every time, i want to merge different regions, but always i want to merged any areas who have marked with the mouse. Then, i delete the text and i need to redo the context of three lines, three separate lines. I hope you understand what i need.MergeAndUnMerge.xlsm
I am working on an excel table in my worksheet contains formulas and locked cells. lets say table range is from column "a" to "f" and there is formula on cell in columns "c" and "f". I just locked cells in column "c" and "f" to prevent people changing the formula accidentally. but when the sheet is protected i am not able to insert or delete rows as the rows has locked cells in columns "c" and "f".
I tried the below macro to unlock the sheet, insert row and lock the sheet again:
So by running the macro it asks the password and do the job but the issue is it needs password and i do not want people to know my password. I don't want them able to unlock the workbook and make any undesired changes. I want them to be allowed insert or delete rows and input data in unlocked cell. I want the sheet to be locked all the time and only when people want to insert or delete a row it be unlocked and be locked again after inserting or deleting whit no need to insert password in this case only.
I am using Outlining on a sheet called "Today".The sheet is manually protected but now the outlining does not work as it says sheet protected.Is there a code i can use so that i can still have the Outlining working in a protected sheet.