Results 1 to 8 of 8

Thread: Unprotect sheet when Saving As

  1. #1

    Unprotect sheet when Saving As



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi and thanks in advance,

    I have a workbook with a protected sheet. I run some code that Saves As into a new workbook removing all formulas but leaving the values and formats in tact. I would like to leave the sheet in the original wkb protected but remove the protection from the sheet in the Saved As wkb? Not sure how or where to slot in the unprotect code?

    Here is my code thus far:


    Code:
    Sub ExportWorkbook()
       Dim varFileName As Variant
       Dim strRestrictedName As String
    
       On Error GoTo Err_Handler
    
       strRestrictedName = ActiveWorkbook.Name
    
       Application.EnableEvents = False
       varFileName = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.Path & "\", fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
       varFileName = Mid$(varFileName, InStrRev(varFileName, "\") + 1)
    
       If varFileName <> False Then
          If UCase$(varFileName) <> UCase$(strRestrictedName) Then
              ActiveWorkbook.SaveAs varFileName
              Application.EnableEvents = True
              FormulasToValues (varFileName)
              ActiveWorkbook.Save
              MsgBox "Done"
          Else
              MsgBox "Invalid File Name", vbCritical, "Stop"
          End If
       Else
           ' Cancelled Save As dialog
       End If
       Application.EnableEvents = True
    
    Err_Exit:
       Application.EnableEvents = True
       Exit Sub
    Err_Handler:
       Select Case Err
           Case 1004 ' Cancelled overwrite of existing file in Save As msgbox
               ' do nothing
           Case Else
               MsgBox Err & " " & Err.Description
       End Select
       GoTo Err_Exit
    End Sub
    
    Sub FormulasToValues(WkbName As String)
       Dim ws As Worksheet
       Dim wkb As Workbook
    
       Application.ScreenUpdating = False
       Application.Calculation = xlCalculationManual
    
       Set wkb = Application.Workbooks(WkbName)
     For Each ws In wkb.Worksheets
           With ws
               .Activate
               On Error Resume Next
               .ShowAllData
               .AutoFilterMode = False
               Worksheets(ws).ShowAllData = True
               On Error GoTo 0
               .Cells.Select
                Selection.Copy
                Selection.PasteSpecial xlPasteValuesAndNumberFormats
                Selection.PasteSpecial xlFormats
                Selection.PasteSpecial xlPasteColumnWidths
           End With
           ws.Range("A1").Select
           Application.CutCopyMode = False
       Next
       Sheets(1).Activate
    
       Application.Calculation = xlCalculationAutomatic
       Application.ScreenUpdating = True
    End Sub

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    In your FormulasToValues routine, add the following just below the .Activate line:
    Code:
    .Unprotect "Password"
    Where the word password is replaced with the actual password you use.

    HTH,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Thanks Ken,

    That removed the sheet protection as required.

    One other thing though ... after calling sub FormulaToValues, I have the line ... ActiveWorkbook.Save ... which is supposed to save the changes, including the removed sheet protection. But if after running the code I then close the Saved As wkb manually, I am prompted via a message box to Save any changes and if I chose No and open the wkb again the sheet is protected as before. Is there some way to save the workbook via VBA after unprotecting it so that if the user closes the wkb and selects No when prompted then the changes to the protection will still be saved? I also tried putting the line ... wkb.Save ... as the very last line in sub FormulaToValues but that also did not work.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Iirc, the issue is that you've got msgbox lines that are being called after the save and that marks the workbook as "Dirty" (changed). Try inserting the following right before the Err_Exit line of your Export Workbook routine:
    Code:
    ActiveWorkbook.Saved = True
    Exit Sub
    I think that should take care of it, and also ensures that enabling events won't also trigger it as dirty again. (Can't remember if this does or not.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Did as you suggested Ken but although I am not prompted to save changes when I close the wkb manually, the protection is still there when I re-open the wkb.

    The only other code I run when the wkb shuts down is to hide/delete the custom menu bar ... so I don't know if that is the problem or something else entirely ...

    Code:
    Private Sub Workbook_Deactivate()
       Run "HideMyToolbar"
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim ans As Integer
        Dim msg As String
        
        If Not Me.Saved Then
            msg = "Do you want to save the changes you made to "
            msg = msg & Me.Name & "?"
            ans = MsgBox(msg, vbQuestion + vbYesNoCancel)
            Select Case ans
                Case vbYes
                    Me.Save
                Case vbNo
                    Me.Saved = True
                Case vbCancel
                    Cancel = True
                    Exit Sub
            End Select
        End If
        On Error Resume Next
        Application.CommandBars("Profile Testing WIN").Delete
    End Sub

  6. #6
    Hi Ken,

    I commented out the hide/delete custom menu bar code and tried to export but got the same result so it must be something else.

  7. #7
    Thanks for you help Ken. As it turns out, I'm an idiot who forgot that I had code in the wkb open event that protects the wkb to set UserInterfaceOnly:=True

    So thanks again for your professional advice and I hope I didn't waste too much of your time.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    ROFL!

    Been there.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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