If I protect a sheet but tick the boxes to allow sorting and autofilter I still get an erro when I try to use the sort buttons on the autofilter.
It allows me to use the filters but as soon as I sort I get told to unprotect the cells im trying to sort.
Is there a way around this where I can keep formulas from being touched but allow sorting of data through autofilter?
When I either manually check the box for allowing sorting and autofiltering, or I do it through vba with:
Code:
With Sheets("Inventory")
.Protect Password:="###", AllowSorting:=True, AllowFiltering:=True, AllowUserInterface:=True
.EnableAutoFilter = True
End With
I still am unable to use the icons. The icons are clickable to filter or sort, but every time they say that the cells I am selecting are protected. I want them to be protected from editing the data, but want to allow them to be sorted or filtered.
I have a spread sheet with various tick boxes, that when ticked calculate an accumulative percentage in a cell. This cell is the basis of my graph.
E.g. if the cell displays 80% - the chart with show 80% - simple.
However, I want to write a vba code that changes the fill colour of the chart depending on the percentage.
i.e. if the percentage data = 0-49% I wish the chart to display as red. 50-69% - yellow and 70%+ = green.
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 uable to use autolfilter when protecting sheet via vbacode even though i have checked the autofilter box to be able to use it whilst protected, why does it work when i manually do protection but does not work via vba?
View 9 Replies View RelatedI have autofilter enabled on the header row across the top of my worksheet. Is there a quick line of VBA I can use to toggle EACH one to (ALL). This would act as a "Reset" in my spreadsheet and display all content. I had a solution that worked, until I made the workbook shared / password protected.
View 3 Replies View RelatedI have a protected worksheet with some macros running on it, and I have been requested to make the workbook shared. Trying to implement this, I keep getting the standard "1004 You cannot use this command on a protected sheet" error when applying an autofilter.
Here is what is making this problem a little tricky:
- As the workbook is shared, I cannot do Protect UserInterfaceOnly because you can't change the protection settings on a shared workbook without unsharing it. Needless to say, I cannot unshare the workbook.
- When I protect the sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both are on. Still, I keep getting the error.
The failing line of code is:
shData. Range("_filterDataBase").AutoFilter lngField, strArg
where shData is the codename of the sheet, lngField is the number of the field and strArg is the filtering criteria.
My guess is, that the error comes from the fact that (for some silly reason) in VBA the .AutoFilter method is used to create a new filter, not only to use an existing one. The members of Sheet.AutoFilter.Filters collection are read-only, and cannot be used to modify the existing filter.
I have a sheet protected and filters enabled. I use this code to enable sort and filters:
[Code] ........
Nevetherless, when I try to sort, I receive this message in excel: "The cell or chart you re trying to change is on a protected sheet.
To make changes, click Unprotect Sheet in the Review tab (you might need a password)."
What settings should I use in order to allow a user to sort data in a table when some of the cells are locked?
When I protected the sheet I ticked the option to allow user to sort data but it doesn't work...
I am getting an error when I protect a worksheet and save it. Upon re-opening the worksheet, I try to use the cells that are being formatted by a macro, but I get the following error...
Run-time error '1004':
Unable to set the NumberFormat property of the Range Class.
If I open the worksheet and it is already protected from when I last closed it, then I get the error. If I unprotect the sheet, then close it and reopen it, then I don't get the error. Does anyone know what I might be missing or doing wrong.
For some reason I thought this would be simple, but not as simple as I thought. I have a UserForm that enters data into a sheet, but before it is entered, the user is prompted for the sheet password (sheet is protected). What I tried to do is use an error handler to exit the sub if the password is incorrect, user hits Cancel, etc. Here is the code I am using:
View 10 Replies View RelatedExcel 2003
Windows Vista
I keep getting this error: (at the line highlighted in green)
Runtime error '1004'
PasteSpecial method of range class failed
**** Open workbooks to transfer temporary data
[Code]....
I am currently working on a project which at various times does 2 different things. These things are to be done on a "PROTECTED" (UserInterfaceOnly:=True ) worksheet.
The first is a Range.Replace command.
The other is a Hyperlinks.Add command.
Now when I'm testing ( typically the worksheet is not protected ) everything works like a champ. But as soon as I protect the sheet the following scenario happens.
1. The replace DOES NOT work.
2. The hypelink.add DOES NOT work.
when the sheet is protected it ends up throwing a runtime error ( 1004 ) "Protected Sheet error" the real odd thing about this is that it doesn't throw the error on the command itself. On the hyperlink the error happens about 3 command further along in the flow............
I have this code that works fine until I password the sheet and the range (b9:d65536) is protected. When I protect the sheet and make a change I get a debug error. What would I need to add/change to handle protected cells on a protected sheet?
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ....
I am being asked to create a workbook where 80% of it is protected. And user input is restricted to specific columns and rows.
However the user wants column R unprotected so that Comment boxes can be used, but when I protect the workbooksheet the Insert option is not available when you right click.
Everything works fine except when I password protect the sheet. Then when the autofilter is used an error message is triggered and the error relates to the first range selected in the combobox code. However, the combobox itself works fine on the protected sheet as I included code to unprotect the sheet at the beginning of the code run by the combobox and reprotect it at the end.
Any advice suggestions gratefully received.
Is it possible to keep the sort icon available on a worksheet which is protected? I have issued a spreadsheet to colleagues which contains formulas so I have protected it, but I have now been informed that they need to be able to sort the data according to a ref number.
I thought of using code (which I'm not very good at) and used some from another excel document, but couldn't get it to work...the code was ....
I'm not fond with macros, but this is what i need help with.
I need two macros one to sort ascending (Ctrl+A), and one to sort descending (Ctrl+D).
This is what the macro to do. Unprotect, sort selected column, protect.
Is there any way to allow autofiltering and sorting even with locked cells within the range to be sorted? I've conditionally locked some cells based on user input, and at the end of my vb code I have allowsorting and allowfiltering. But I want to sort cells that are locked even after that. Does anyone know if that's even possible with VB?
View 4 Replies View RelatedI have a column containing 365 entries, one for each day of the year. This I intend to send out to Army bands, to have them fill in their forecast of engagements. I do not want them to be able to amend the original diary, but to scroll to the bottom of the list, add a new entry with the applicable date and then be able to 'sort' back to chronological order. This would then allow a separate entry for every engagement to allow me to interrogate the spreadsheet. As I have protected the sheet, the 'sort' facility does not function even though when protecting I ticked the box to allow users to 'sort'.
View 2 Replies View RelatedIs there a way I can sort multiple hidden protected worksheets with a password “Protect”
I have 12 worksheets “Jan”, “Feb”. Etc
“The Range on each worksheet is the same ("A11:CR200") ....
I need to protect my worksheet and only allow users to edit certain ranges. I am allowing the use of autofilter which I can select when I apply protection. However, I am now aware I cannot sort protected cells..which is essential. I basically need to be able to sort a column titled 'surname'. The worksheet is a record of pupils attainment in my class.
Now, I have a macro which seems to do the job: ....
I am using the autofilter function on a spreadsheet and the first column displays numerical data ranging from 1 to over 100. When I apply the 'Sort Ascending' from the autofilter on this column, it is attempting to display the data in alphabetical/numerical order however the order it is displaying appears such as this 1, 10 , 100, 101, 102, before moving on with data that starts with 2 (2, 20, 21, 22, 23 etc). Is there a way I can filter this column to display literal numerical data such as 1, 2, 3, 4 etc?
View 2 Replies View RelatedWith the sizes in in B6 AND B7 these result in an error in B68 , the formula is in B68 and the tables are on sheet 3 , the increments have to stay as they are.
View 6 Replies View RelatedI have a userforms which transfers data to protected sheets.The userforms worked until i protected each sheet.Can there be any code which i add to the current userform code so it can still transfer the data to protected sheets
The other issue also is now that the sheets are protected the Outlining also does not work.Can this also be changed so the Outlining works on protected sheets
I have 5 userforms which all transfer data.
[Code].....
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?
When I protect my worksheet the option buttons, on occasion, give the error message "The cell or chart that you are trying to change is protected and therefore read only. To modify a protected cell or chart, first remove protection using the Unprotect sheet command. You may be prompted for a password." Sometimes I click on an option button and this error pops up and other times i can click the same button and no error occurs.
View 1 Replies View RelatedWhy am I getting a runtime error on the following code:
Code:
If Target.Address = "$E$5" And Target.Value "" Then
Range("E5:G5").Copy
If Sheets("Sheet5").Range("F5").Value = "" Then
Sheets("Sheet5").Range("F5").PasteSpecial xlPasteValues
Else
Sheets("Sheet5").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If
When I select only one cell it's ok but when I select multiple cell to delete it sends me a run-time error '13'.
Excel 2003, sheet is protected.
I create and delete a conditional formatting rule programmatically. The issue I am facing is when I try to delete the conditional formatting rule that I programmatically created, I ran into the following errors:
1. Exception from HRESULT: 0x800A03EC
2. Attempted to read or write protected memory. this is often indication that other memory is corrupt.
I get these errors only when I have user created conditional formatting rules prior to deleting – I don’t want to delete all conditional formatting rules using FormatConditions.Delete(). Further, I can’t use FormatConditions[index].Delete() because I don’t have a index reference for the one that was created from the application.
Note: I have checked the Trust access to the VBA project object model.
I created several macros objects to do simple hide columns, filter on, filter off, etc. type functions, using the Tools / Record Macro feature. Then discovered they won't work in a protected worksheet. Is there a way around this situation, other than turning protection off? Here's an example of the code for one of them, although I get the error or every attempt to perform a macro once the sheet is protected:
View 4 Replies View Related
