Flatten password file

[PSM]

I have a spreadsheet with that is password protected and has the
following code also included.
I would like to let the user save the tab they are working on
effectively as a flat sheet (copy, paste as values) but the password is
an issue.
How do I overcome this ?


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Welcome Sheet").Visible = xlSheetVisible
Sheets("Short F3").Visible = xlSheetVeryHidden
End Sub

Private Sub Workbook_Open()
Select Case LCase(Environ("username"))
Case Is = "user.1", "user.2", etc etc
Sheets("Short F3").Visible = xlSheetVisible
Sheets("Welcome Sheet").Visible = xlSheetVeryHidden
Sheets("Short F3").Select
Range("A1").Select

Case Else
Application.DisplayAlerts = False
Workbooks("Master File.XLS").Close
End Select

' End If
End Sub

 

[Tom Hutchins]

You could provide a macro to your users (via a button on the sheet, keystroke
combo, etc.) which will handle it. The following macro copies the active
sheet to a new workbook, removes the password, copies & pastes in place as
values, presents a SaveAs dialog, and saves the new workbook. The protection
is never removed from the original sheet.

Sub test()
ActiveSheet.Select
ActiveSheet.Copy
ActiveSheet.Unprotect Password:="xxx"
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
Application.FileDialog(msoFileDialogSaveAs).Show
Application.FileDialog(msoFileDialogSaveAs).Execute
End Sub

Change the password in the above code to your sheet password. You should
also lock the project for viewing in the VBA with a password.

Hope this helps,

Hutch

 

[PSM]

Thanks for code Tom. Only problem is the code removes password from
original file. Can this be stopped?

 

[Tom Hutchins]

I am unable to replicate your results, after testing this macro in Excel 2003
and 2007. It only removes the protection from the copy of the original sheet,
not the original sheet itself. After running the macro, I am left with the
original workbook, protected & unaltered, and a second workbook which has an
unprotected, values-only copy of the sheet that was active in the original
file when I ran the macro. Maybe an MVP will have an insight for us.

Hutch