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.
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 RelatedLet's say I have Sheet1-Sheet6. I also have combobox1 (with item 1, 2, & 3 as the list items) & combobox 2 (with items 1 & 2 as the list items).
If combobox1 = 1 & combobox2 = 1 then hide Sheets 2,3,4,5,6
if combobox1 = 1 & combobox2 = 2 then hide Sheets 1,2,4,5,6
If combobox1 = 1 & combobox2 = 3 then hide sheets 1,2,3,4,6
If combobox1 = 2 & combobox2 = 1 then hide sheets 1,3,4,5,6
If combobox1 = 2 & combobox2 = 2 then hide sheets 1,2,3,5,6
If combobox1 = 2 & combobox2 = 3 then hide sheets 1,2,3,4,5
I would like to also make both of these combo boxes required fields and to default text to say 'Select One...'
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 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 RelatedIf 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?
I 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 received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets
Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long
dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1
dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1
If dr = 60001 Then ................
i want to protect my all sheets at once
View 3 Replies View RelatedI have a spreadsheet with some macros in it. When I run the macros, I would like to unprotect the worksheet and then protect the spreadsheets when the macros are done. The excel tabs I would like to protect/unprotect are called Graph and Data.
View 3 Replies View RelatedI have a workbook that is protected by password. I have to unprotect sheets.
How can I unprotect sheets and workbooks while I am updating or changing?
I need a simple Macro that will, sheet by sheet, search the entire workbook. However, it only needs to search Column A, and there need be no text box, because the user will not define the text. The search text will simply be "X"
The real issue that I'm having is that I need it to find the first item, then offer me the option of selecting "Next" "Previous" or "Cancel" "Cancel" of course would mean that the item found is the one being searched for.
I have a workbook with 3 spreadsheets. When I put ptotection on 2 of the sheets, the custom view will not work on the other. Any way to make the views work?
View 5 Replies View RelatedI have the following code as workbook module but it is not working, I have tried fiddling with it but I cannot work out what is wrong, can anyone help please.
Private Sub Workbook_Open()
Dim ws As Worksheet ....
I've created a shared spreadsheet with multiple sheets. All the content was created to be visible without having to scroll at 87%. My question is can I protect or lock the zoom level so that when anyone else opens the spreadsheet, it will only be visible at 87%? I have my screen resolution at "Smallest" so not sure if that too will affect the viewing level.
View 7 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 attempting to run a macro that will do a find and replace on a protected sheet but this is not working. I am using the following code to Protect all Sheets, which I found on this site when workbook opens using the Workbook Open Event and uses UserInterFaceOnly :
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="111", _
UserInterFaceOnly:=True
Next wSheet
End Sub
And then a find and replace similar to this:
Sub FindAndReplace()
Selection.Replace What:="w", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Is 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 have a sheet that I protect but i use the below code so that I can still use my gouping '+' or '-' symbols to hide/unhide rows.
ws.Protect Password:="PASSWORD", userinterfaceonly:=True
ws.EnableOutlining = True
This is ok but When close the workbook and reopen it, my sheet becomes fully protected and i cant use the group icons. Can anyone either suggest better code or a way to initilise the workbook to act in this way when it loads up. For Reference Below is my full
Sub ProtectAll()
Dim ws As Worksheet
sSheet = Control.Name
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case sSheet1
Case Else
ws.Protect Password:="PASSWORD", userinterfaceonly:=True
ws.EnableOutlining = True
End Select
Next ws
End Sub
I have a workbook w/ 120+ sheets, and the first one is a table of contents, while each following sheet is a record sheet for one of 10 individuals. That is, sheets 2-8 are for Alice, 9-12 for Bob, 13-29 for Charles, etc. Individual names are in column C, sheet names are hyperlinked text in column A on the table of contents sheet.
I want to know how to hide all the sheets which are not meant for the individual based on the autofilter of Column C. If I select Alice as the autofilter criteria for Column C, I want all the sheets except the table of contents and her sheets 2-8 to be hidden.
Is there an easy way of filtering a field on one sheet and having it identically filter the same field on multiple other sheets ?
View 5 Replies View RelatedI 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 RelatedI need to change VBA to Password Protect all sheets to allow user to select protected. I have:
View 2 Replies View RelatedI have an excel with several sheets with task for different departments. All of them are assigned to a person responsible for that task. I applied auto filter which gives me the overview for a person Per department, now i would like to gather all departments into one so we have an overview for each person what his tasks are.
View 1 Replies View RelatedI need to Autofilter across many sheets. I have found some examples on doing this such as..
Autofilter Across Multiple Sheets
But I need to take it a step further. Autofilter several Worksheets (skip blank ones - the above link gives code that bombs when it sees a Worksheet with out data.) Then copy those results to a 'summary' page.
So...
1. Autofilter all Worksheets / skip blank ones
2. Show results of all Worksheets on summary page.
1) This spreadsheet has 7 main sheets, UK, IBE, FIN, BENE, FRA.
2) Each main sheet has 8 sub sheets i.e UK CAT A to H, IBE CAT A to H etc.
3) col BO on the main sheets will have CAT A,B to H and can contain some other text but i'm only concerned about the CAT. Also Cat may be wrote CAT, cat or Cat etc
At the moment i'm using a loop, I had some code from another thread yesterday but this code creates new sheets if it can not find a sheet named the same
I will need to run this as 1 i.e all 7 together or 1 at a time.
Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:
View 3 Replies View RelatedI'm thinking of writing a small tool to store user information on certain files.
The user data will be input by means of comboboxes on a userform.
Each combobox will need to contain a list of values previously used for that combobox. (I'm planning to use a separate ini file for each combobox)
All of the user data entered for a file will be saved into a single ini file.
Most of this is straightforward however I'm not good with comboboxes. And working this in with Ini files makes it harder!
I'm struggling with the following concepts:
SOLVED - How do to make a combobox populate with all the values from its ini file
SOLVED - How to identify if a selected value in any combobox is new (i.e. wasn't in the original list presented to the user)
SOLVED - (From above) How to write this new value to that comboboxes ini file (so it will appear in future lists for that combobox)
How to input two values from any given combobox
(From above) How to record two values for one field into the files ini file.
i have a table on sheet1
A B C
(Names) (Total) (Status)
1. John 500 Active
2. Dave 200 Active
3. Andrew 175 Active
4. John 225 Active
On my userform there are 2 comboboxes. In combobox 1 I have Rowsource as Column A (Names), if I select John I want Combobox 2 to list 500 & 225.