Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

code for Auto reapply filter with protect sheet

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

    Code:
    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, 22:10.

  • #2
    Re: code for Auto reapply filter with protect sheet

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

    Comment


    • #3
      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

      Comment


      • #4
        Re: code for Auto reapply filter with protect sheet

        run this macro first (eg when the workbook opens)
        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
        edit: if you get no joy with that you could always just do this:
        Code:
        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, 01:43.

        Comment


        • #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

          Comment


          • #6
            Re: code for Auto reapply filter with protect sheet

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

            Comment


            • #7
              Re: code for Auto reapply filter with protect sheet

              Dear trunten..
              Thank you very much
              when I I put This code :
              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          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

              Comment


              • #8
                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.

                Comment


                • #9
                  Re: code for Auto reapply filter with protect sheet

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

                  Comment

                  Trending

                  Collapse

                  There are no results that meet this criteria.

                  Working...
                  X