Consulting

Results 1 to 2 of 2

Thread: Macro removes editing options selected when re-protecting worksheet after macro runs

  1. #1

    Macro removes editing options selected when re-protecting worksheet after macro runs

    I have a button that performs a macro on the "Cycle Time" sheet that hides rows without data in them in column B. The macro is written to unprotect the sheet, hide the rows, then reprotect the sheet. For some reason though the macro removes my selected editing options that I have selected when re-protecting worksheet after macro runs.

    I am trying to keep the check boxes selected for Select locked cells, Select unlocked cells, Format rows, Insert rows, Delete rows, Sort, Use AutoFilter, Use Pivot Table & Pivot Chart, Edit objects, and Edit Scenarios. I have row 5 selected as my row to be able to filter and sort and Allow Edit Ranges set to allow rows 6-13 to be sorted while the sheet is locked.

    Everytime the macro button is clicked and the macro runs, the only ones left checked are Select locked cells and Select unlocked cells. Any help on how to fix this is greatly appreciated!
    Attached Files Attached Files

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,947
    Location
    Not sure if Button17_Click is supposed to do anything, since there's no Sheet1 in the workbook

    I'd change Button46_Click a little

    
    Sub Button46_Click()
        Dim r1 As Range, r2 As Range, c As Range
        
        Application.ScreenUpdating = False
        
        With Sheet20
            .Unprotect
    
    
            .Rows.Hidden = False
    
    
            Set r1 = .Range("b5")
            Set r2 = .Cells(.Rows.Count, 2).End(xlUp)
            
            For Each c In Range(r1, r2).Cells
                If Len(c.Text) = 0 Then c.EntireRow.Hidden = True
            Next
            
            .Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, _
                AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, _
                AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
        End With
    
    
        Application.ScreenUpdating = True
    
    
    
    
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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