Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -- PSM |
#2
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Flatten password file
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 |
#4
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Missing Password in DSN File | Excel Discussion (Misc queries) | |||
Flatten a table to a list | Excel Discussion (Misc queries) | |||
Setting a password on a Single File Web Page (mht; mhtml file) | Excel Discussion (Misc queries) | |||
how to automate opening a password protected excel file? e.g. a .xls that has a password set in the security tab. | Excel Worksheet Functions | |||
bypass password when update linking of password protected file | Excel Discussion (Misc queries) |