Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: code for Auto reapply filter with protect sheet

  1. #1
    Join Date
    21st March 2012
    Posts
    5

    code for Auto reapply filter with protect sheet

    Dear .......
    I want to code to Auto reapply filter with protect sheet
    I used to this code to Auto reapply filter but I can't protect this sheet

    VB:
    Private Sub Worksheet_Calculate() 
         
        If Me.FilterMode = True Then 
            With Application 
                .EnableEvents = False 
                .ScreenUpdating = False 
            End With 
             
            With ActiveWorkbook 
                .CustomViews.Add ViewName:="Mine", RowColSettings:=True 
                Me.AutoFilterMode = False 
                .CustomViews("Mine").Show 
                .CustomViews("Mine").Delete 
            End With 
             
             
            With Application 
                .EnableEvents = True 
                .ScreenUpdating = True 
            End With 
        End If 
         
    End Sub 
    
    
    Thank you very much
    Last edited by Dave Hawley; March 23rd, 2012 at 22:10.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th July 2011
    Posts
    532

    Re: code for Auto reapply filter with protect sheet

    Add this just before you re-enable screen updating and event:
    VB:
    ActiveSheet.Protect UserInterfaceOnly:=True 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    21st March 2012
    Posts
    5

    Re: code for Auto reapply filter with protect sheet

    Thank you....
    But I mean a code can't work when I used protect sheet
    Thank you very much

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th July 2011
    Posts
    532

    Re: code for Auto reapply filter with protect sheet

    run this macro first (eg when the workbook opens)
    VB:
    Sub a() 
        Dim sh As Worksheet 
        For Each sh In ThisWorkbook.Worksheets 
            If sh.ProtectContents = True Then sh.Protect UserInterfaceOnly:=True 
        Next sh 
    End Sub 
    
    
    edit: if you get no joy with that you could always just do this:
    VB:
    Private Sub Worksheet_Calculate() 
        If Me.FilterMode = True Then 
            With Application 
                .EnableEvents = False 
                .ScreenUpdating = False 
            End With 
            ActiveSheet.Unprotect 
            With ActiveWorkbook 
                .CustomViews.Add ViewName:="Mine", RowColSettings:=True 
                Me.AutoFilterMode = False 
                .CustomViews("Mine").Show 
                .CustomViews("Mine").Delete 
            End With 
            ActiveSheet.Protect Password:="Password" 
            With Application 
                .EnableEvents = True 
                .ScreenUpdating = True 
            End With 
        End If 
    End Sub 
    
    
    Last edited by trunten; March 25th, 2012 at 01:43.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    21st March 2012
    Posts
    5

    Re: code for Auto reapply filter with protect sheet

    Dear trunten..
    First , Thank you very much
    Excuse me , I put This code :

    Sub a() Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If sh.ProtectContents = True Then sh.Protect UserInterfaceOnly:=True Next sh End Sub
    Private Sub Worksheet_Calculate()

    If Me.FilterMode = True Then
    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    With ActiveWorkbook
    .CustomViews.Add ViewName:="Mine", RowColSettings:=True
    Me.AutoFilterMode = False
    .CustomViews("Mine").Show
    .CustomViews("Mine").Delete
    End With


    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With
    End If

    End Sub

    If I don't Protect sheet, an Auto reapply filter can play, But, If I work Protect sheet, an Auto reapply filter can't play

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th July 2011
    Posts
    532

    Re: code for Auto reapply filter with protect sheet

    see my edited post above.
    Last edited by trunten; March 25th, 2012 at 03:28.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    21st March 2012
    Posts
    5

    Re: code for Auto reapply filter with protect sheet

    Dear trunten..
    Thank you very much
    when I I put This code :
    VB:
    Sub a()      Dim sh As Worksheet      For Each sh In ThisWorkbook.Worksheets          If sh.ProtectContents = True Then sh.Protect UserInterfaceOnly:=True      Next sh  End Sub 
        Private Sub Worksheet_Calculate() 
            If Me.FilterMode = True Then          With Application              .EnableEvents = False              .ScreenUpdating = False          End With          ActiveSheet.Unprotect          W 
            ith ActiveWorkbook              .CustomViews.Add ViewName:="Mine", RowColSettings:=True              Me.AutoFilterMode = False              .CustomViews("Mine").Show              .CustomViews("Mine").Delete          End With          ActiveSheet.Protect Password:="Password"          With Application              .EnableEvents = True              .ScreenUpdating = True          End With      End If  End Su 
    
    
    There is a message that appears [ run-time error '1004': Method 'Autofiltermode' of object '_Worksheet'faild ]
    I want to for example worksheet contain 2 sheet :
    1- first sheet : called All customers , I want to Enter the data in it
    2- 2nd sheet : called Customer 1 , I want to this sheet protect and filter from All customers sheet , and I want Reapply filter is auto when I add any data In All customers sheet

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    26th July 2011
    Posts
    532

    Re: code for Auto reapply filter with protect sheet

    First off all, I had assumed that your original code was already working. Apologies for that. Secondly, I'm not really sure what you are asking, Is there any chance you could provide an example workbook showing the desired results and I will try and fulfill those requirements for you.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    21st March 2012
    Posts
    5

    Re: code for Auto reapply filter with protect sheet

    Thank you very much trunten
    When I canceled { Me.AutoFilterMode = False } a code worked successfully

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Protect sheet with VBA Code BUT allow autofilters
    By Darth Dennis in forum EXCEL HELP
    Replies: 6
    Last Post: February 29th, 2012, 15:13
  2. Reapply Auto filter in Excel 2003
    By Shad in forum EXCEL HELP
    Replies: 16
    Last Post: December 22nd, 2011, 15:52
  3. Replies: 8
    Last Post: August 10th, 2010, 11:27
  4. Copy Auto Filter Range To Another Sheet
    By heizeer in forum EXCEL HELP
    Replies: 2
    Last Post: June 9th, 2008, 23:47
  5. Protect Sheet vs. VBA Code
    By steveski in forum EXCEL HELP
    Replies: 1
    Last Post: February 19th, 2004, 11:05

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno