 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008Using Autofilter on a Protected Sheet
I am using Excel 2003. I have a protected sheet that allows the user to enter quantities in only 1 column. All other cells are locked. I have a control button that has code that uses the "Autofilter" statement. This button filters out all blank rows or zero quantities that residein the unlocked column. When I click this control, I get the following Visual Basic error" Run-time error '1004': "You cannot use this command on a protected sheet..." Is there any way that I can protect the sheet and accomplish the filtering of the rows? Thanks in advance! You could always unprotect the sheet, do the filter and then reprotect the sheet. If all your users (just you???) are using xl2003 (or higher), you can protect the sheet: Tools|Protection|Protect worksheet and check the "User Autofilter" option (near the bottom). > I am using Excel 2003. > [quoted text clipped - 12 lines] > Is there any way that I can protect the sheet and accomplish the filtering > of the rows? Thanks in advance!  SignatureDave Peterson Hi Dave: Thanks for your replies. I think what you suggested in your 1st reply is what Gord suggested in his post. I'm not sure if your 2nd post negated your 1st post. > You could always unprotect the sheet, do the filter and then reprotect the > sheet. [quoted text clipped - 21 lines] > > Is there any way that I can protect the sheet and accomplish the filtering > > of the rows? Thanks in advance! Protecting the sheet in code (using the userinterfaceonly parm) means that you won't have to unprotect it, do the work, and reprotect in each of your macros. > Hi Dave: > [quoted text clipped - 32 lines] > > > > Dave Peterson SignatureDave Peterson Generally you have a procedure that unlocks the sheet, performs the filter, then re-locks the sheet. Sub filter_locked_sheet() ActiveSheet.Unprotect Password:="justme" 'your filtering code ActiveSheet.Protect Password:="justme" Gord Dibben MS Excel MVP >I am using Excel 2003. > [quoted text clipped - 12 lines] >Is there any way that I can protect the sheet and accomplish the filtering >of the rows? Thanks in advance! Hi Gord: Thanks for your reply. My code is as follows: Private Sub CONDENSEButton_Click() Rows("8:38").Select Selection.AutoFilter Selection.AutoFilter Field:=5, Criteria1:="<>" ActiveWindow.ScrollRow = 1 Range("A1:L1").Select End Sub I tried your suggestion, but could not get it to work. I'm sure the problem is with how I surround your code with my code above. Your help would be greatly appreciated! > Generally you have a procedure that unlocks the sheet, performs the filter, then > re-locks the sheet. [quoted text clipped - 23 lines] > >Is there any way that I can protect the sheet and accomplish the filtering > >of the rows? Thanks in advance! Private Sub CONDENSEButton_Click() ActiveSheet.Unprotect Password:="justme" Rows("8:38").Select Selection.AutoFilter Selection.AutoFilter Field:=5, Criteria1:="<>somevalue" ActiveWindow.ScrollRow = 1 Range("A1:L1").Select ActiveSheet.Protect Password:="justme" End Sub "<>" doesn't seem to be any valid criterion as far as I can see so I added the somevalue. You may also want to experiment with Dave's "userfaceonly" code. Gord >Hi Gord: > [quoted text clipped - 41 lines] >> >Is there any way that I can protect the sheet and accomplish the filtering >> >of the rows? Thanks in advance! "<>" means the same as non-blank. kind of "<>"&"" > Private Sub CONDENSEButton_Click() > ActiveSheet.Unprotect Password:="justme" [quoted text clipped - 58 lines] > >> >Is there any way that I can protect the sheet and accomplish the filtering > >> >of the rows? Thanks in advance! SignatureDave Peterson Yep. It sure does.....forehead a little tender about now<g> Gord >"<>" >means the same as non-blank. [quoted text clipped - 63 lines] >> >> >Is there any way that I can protect the sheet and accomplish the filtering >> >> >of the rows? Thanks in advance! You sure you didn't mean <>? <hehehe> > Yep. > > It sure does.....forehead a little tender about now<g> > > Gord <<snipped>> Using the Tools|Protection option won't allow your code to run. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) > I am using Excel 2003. > [quoted text clipped - 12 lines] > Is there any way that I can protect the sheet and accomplish the filtering > of the rows? Thanks in advance! SignatureDave Peterson Hi Dave: By adding this line before my code fixes my problem: ActiveSheet.Protect UserInterfaceOnly:=True So this works: Private Sub CONDENSEButton_Click() ActiveSheet.Protect UserInterfaceOnly:=True Rows("8:38").Select Selection.AutoFilter Selection.AutoFilter Field:=5, Criteria1:="<>" ActiveWindow.ScrollRow = 1 Range("A1:L1").Select End Sub Problem solved! ________________________________________________________________ However, I have another control button called "SHOW ALL ITEMS" that expands the worksheet to its original size. I added this button in case the user wants to make any revisions. Unfortunately, I receive the same error message as before. My original code was: Private Sub EXPANDButton_Click() 'removes AutoFilter if one exists AutoFilterMode = False End Sub This did not work to resolve the error. So I tried this code: Private Sub EXPANDButton_Click() 'removes AutoFilter if one exists ActiveSheet.Protect UserInterfaceOnly:=True AutoFilterMode = False End Sub This did not work either, same problem. Any suggestion how I might fix this? > Using the Tools|Protection option won't allow your code to run. > [quoted text clipped - 34 lines] > > Is there any way that I can protect the sheet and accomplish the filtering > > of the rows? Thanks in advance! That line doesn't need to be included in the code that does the work. It only needs to be set once. That's why I included it in the Auto_Open procedure (placed in a general module). > Hi Dave: > [quoted text clipped - 85 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave: As written in my previous post, that line (ActiveSheet.Protect UserInterfaceOnly:=True) was not included in my original code for that control. The same 1004 error occurs with or without that line. In addition, you mention your module: Option Explicit Sub auto_open() With Worksheets("sheet1") Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub 1. What is the differnce between a module and a macro? 2. Where do you write it? 3. Is it necessary to accomplish what I want? 4. If I use this module, do I have to delete the "ActiveSheet.Protect UserInterfaceOnly:=True" in my "Private Sub CONDENSEButton_Click()"? > That line doesn't need to be included in the code that does the work. > [quoted text clipped - 90 lines] > > > > > > Dave Peterson There are several types of modules within your project. You can create a general module by: selecting your project Insert|Module Then paste the Auto_Open procedure in that module. Change the name of the worksheet to what you need. (Or is it really named Sheet1?) And change the password to what you need, too. Then delete that auto_Open procedure from where you originally put it. ==== There are worksheet modules. I bet you put your CONDENSEButton_Click() procedure in one of those. There are workbook modules. And class modules and Userform modules... ======= #1. The module is the "holder" for the macro. It's kind of like how you put formulas in a worksheet. The formula would represent the macro, but the worksheet would represent the module. #2. In that general module. #3. Not necessary. Most things can be accomplished in lots of different ways. But if you have several macros that change things on protected sheets, do you want to add lines that unprotect and reprotect the worksheet each time? #4. If you're going to use the UserInterfaceOnly:=true, then that line only needs to be run once. I'd do it when the workbook opens (in Auto_Open). If you don't want to use the Auto_Open procedure, then change your code to: > > > Private Sub CONDENSEButton_Click() > > > me.unProtect password:="whateveryouwanthere" [quoted text clipped - 5 lines] > > > me.Protect password:="whateveryouwanthere" > > > End Sub > Hi Dave: > [quoted text clipped - 120 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave: Thanks for your extensive help and time and patience! My problem now is ONLY with my EXPAND control button. My CONDENSE control button works fine. I only need help with the EXPAND control button macro. Dan There are a few things that can't be done in code if you use the userinterfaceonly parm. Removing the autofilter arrows is one of them: Private Sub EXPANDButton_Click() me.unprotect password:="whatyouwant" me.AutoFilterMode = False me.protect password:="whatyouwant" End Sub If you wanted to keep the arrows, but show all the data: Private Sub EXPANDButton_Click() me.unprotect password:="whatyouwant" If me.FilterMode Then me.ShowAllData End If me.protect password:="whatyouwant" End Sub > Hi Dave: > [quoted text clipped - 6 lines] > > Dan SignatureDave Peterson Hi Dave: "You're The Man!" Success at last. Where did you learn this stuff!? Is there a book you would recommend? Thanks again, Dan > There are a few things that can't be done in code if you use the > userinterfaceonly parm. Removing the autofilter arrows is one of them: [quoted text clipped - 25 lines] > > > > Dan Glad it worked. Depending on what you're doing, sometimes using userinterfaceonly makes sense. Sometimes, embedding the unprotect and reprotect in each procedure makes sense. And John Walkenbach's books are very good to start. Debra Dalgleish has a list of books at her site: http://www.contextures.com/xlbooks.html > Hi Dave: > [quoted text clipped - 39 lines] > > > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.