|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 15th, 2009, 02:46 PM
|
|||
|
|||
Flatten password file
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 
|
|
#2
April 15th, 2009, 06:36 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Flatten password file
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).Execut e 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" wrote: 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 -- PSM
|
|
#3
April 16th, 2009, 10:01 AM
|
|||
|
|||
|
Thanks for code Tom. Only problem is the code removes password from original file. Can this be stopped?
Quote:
|
|
#4
April 16th, 2009, 01:57 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Flatten password file
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 "PSM" wrote: Thanks for code Tom. Only problem is the code removes password from original file. Can this be stopped? Tom Hutchins;3289388 Wrote: 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).Execut e 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" wrote: - 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 -- PSM - -- PSM
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
