Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove content when saving / loading spreadsheet
In Excel 2003, I need to simulate a "login/logout" function within Excel
document, without requiring user intervention, preferably through Login/password pop-up screen. Based on the login information (match of user/password with predefined values within the workbook), content of specific cells / sheets may / may not be displayed. One feature of this login/logout function will be also to remove the content of specific cells (where password was stored). User cannot be relied upon to 1) authorize macros to be run and 2) to execute macro manually. Document will be used internally so security needs are medium. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove content when saving / loading spreadsheet
This can only be done using VB code, ie Macro's. It's easily possible to
force the user to enable Macro's on opening by making the file useless if it's opened with Macro's disabled. This can be done by hiding all worksheets except one with a title or warning message. Other hidden sheets can be displayed (or not according to the entry level you chose) by unhiding them with code if the correct password is entered. You should note though that Excel protection is weak and any password including the password to prevent the viewing of VB code can be easily broken by anyone with a little knowledge and the will to do so. It is therefore only suitable in a security sense for casual users and definitely not for sensitive data. Regards, Alan. "EZdoesIT" wrote in message ... In Excel 2003, I need to simulate a "login/logout" function within Excel document, without requiring user intervention, preferably through Login/password pop-up screen. Based on the login information (match of user/password with predefined values within the workbook), content of specific cells / sheets may / may not be displayed. One feature of this login/logout function will be also to remove the content of specific cells (where password was stored). User cannot be relied upon to 1) authorize macros to be run and 2) to execute macro manually. Document will be used internally so security needs are medium. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove content when saving / loading spreadsheet
Thanks Alan. That's pretty much what I gathered from other postings. However,
as I am a newbie to VBA, do you know of any sample code you could point me to to achieve this (in particular the steps to be performed before the file is opened: remove content, hide sheet)? Any help would be much appreciated. Thanks "Alan" wrote: This can only be done using VB code, ie Macro's. It's easily possible to force the user to enable Macro's on opening by making the file useless if it's opened with Macro's disabled. This can be done by hiding all worksheets except one with a title or warning message. Other hidden sheets can be displayed (or not according to the entry level you chose) by unhiding them with code if the correct password is entered. You should note though that Excel protection is weak and any password including the password to prevent the viewing of VB code can be easily broken by anyone with a little knowledge and the will to do so. It is therefore only suitable in a security sense for casual users and definitely not for sensitive data. Regards, Alan. "EZdoesIT" wrote in message ... In Excel 2003, I need to simulate a "login/logout" function within Excel document, without requiring user intervention, preferably through Login/password pop-up screen. Based on the login information (match of user/password with predefined values within the workbook), content of specific cells / sheets may / may not be displayed. One feature of this login/logout function will be also to remove the content of specific cells (where password was stored). User cannot be relied upon to 1) authorize macros to be run and 2) to execute macro manually. Document will be used internally so security needs are medium. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove content when saving / loading spreadsheet
This is the principal, on opening a pop up asks for a password, if the
password is (in this case '1234') sheets 2 and 3 are unhidden, if not they stay hidden and an error message appears. On saving the file sheets two and three will be hidden. After you enter the code save the file, come out of it and reopen it. Put the code below in 'This Workbook' To do this press Alt and F11 to open the VB editor, on the top left you'll see 'This Workbook', double click that and copy and paste the code in. Watch out for text wrap in the e-mail, the 'BeforeSave' line four from the bottom will probably appear as two lines, it should all be on one line, Private Sub Workbook_Open() Dim Password Password = Application.InputBox("Enter Password") If Password = "1234" Then Sheet2.Visible = xlSheetVisible Sheet3.Visible = xlSheetVisible Else MsgBox "Password Invalid" End If End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Sheet2.Visible = xlSheetVeryHidden Sheet3.Visible = xlSheetVeryHidden End Sub Regards, Alan. "EZdoesIT" wrote in message ... Thanks Alan. That's pretty much what I gathered from other postings. However, as I am a newbie to VBA, do you know of any sample code you could point me to to achieve this (in particular the steps to be performed before the file is opened: remove content, hide sheet)? Any help would be much appreciated. Thanks "Alan" wrote: This can only be done using VB code, ie Macro's. It's easily possible to force the user to enable Macro's on opening by making the file useless if it's opened with Macro's disabled. This can be done by hiding all worksheets except one with a title or warning message. Other hidden sheets can be displayed (or not according to the entry level you chose) by unhiding them with code if the correct password is entered. You should note though that Excel protection is weak and any password including the password to prevent the viewing of VB code can be easily broken by anyone with a little knowledge and the will to do so. It is therefore only suitable in a security sense for casual users and definitely not for sensitive data. Regards, Alan. "EZdoesIT" wrote in message ... In Excel 2003, I need to simulate a "login/logout" function within Excel document, without requiring user intervention, preferably through Login/password pop-up screen. Based on the login information (match of user/password with predefined values within the workbook), content of specific cells / sheets may / may not be displayed. One feature of this login/logout function will be also to remove the content of specific cells (where password was stored). User cannot be relied upon to 1) authorize macros to be run and 2) to execute macro manually. Document will be used internally so security needs are medium. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove content when saving / loading spreadsheet
Thanks Alan. Very much appreciated.
Best Regards, "Alan" wrote: This is the principal, on opening a pop up asks for a password, if the password is (in this case '1234') sheets 2 and 3 are unhidden, if not they stay hidden and an error message appears. On saving the file sheets two and three will be hidden. After you enter the code save the file, come out of it and reopen it. Put the code below in 'This Workbook' To do this press Alt and F11 to open the VB editor, on the top left you'll see 'This Workbook', double click that and copy and paste the code in. Watch out for text wrap in the e-mail, the 'BeforeSave' line four from the bottom will probably appear as two lines, it should all be on one line, Private Sub Workbook_Open() Dim Password Password = Application.InputBox("Enter Password") If Password = "1234" Then Sheet2.Visible = xlSheetVisible Sheet3.Visible = xlSheetVisible Else MsgBox "Password Invalid" End If End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Sheet2.Visible = xlSheetVeryHidden Sheet3.Visible = xlSheetVeryHidden End Sub Regards, Alan. "EZdoesIT" wrote in message ... Thanks Alan. That's pretty much what I gathered from other postings. However, as I am a newbie to VBA, do you know of any sample code you could point me to to achieve this (in particular the steps to be performed before the file is opened: remove content, hide sheet)? Any help would be much appreciated. Thanks "Alan" wrote: This can only be done using VB code, ie Macro's. It's easily possible to force the user to enable Macro's on opening by making the file useless if it's opened with Macro's disabled. This can be done by hiding all worksheets except one with a title or warning message. Other hidden sheets can be displayed (or not according to the entry level you chose) by unhiding them with code if the correct password is entered. You should note though that Excel protection is weak and any password including the password to prevent the viewing of VB code can be easily broken by anyone with a little knowledge and the will to do so. It is therefore only suitable in a security sense for casual users and definitely not for sensitive data. Regards, Alan. "EZdoesIT" wrote in message ... In Excel 2003, I need to simulate a "login/logout" function within Excel document, without requiring user intervention, preferably through Login/password pop-up screen. Based on the login information (match of user/password with predefined values within the workbook), content of specific cells / sheets may / may not be displayed. One feature of this login/logout function will be also to remove the content of specific cells (where password was stored). User cannot be relied upon to 1) authorize macros to be run and 2) to execute macro manually. Document will be used internally so security needs are medium. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet name = cell content? | Excel Worksheet Functions | |||
How to alter content in a closed spreadsheet without opening it! | Excel Discussion (Misc queries) | |||
How do I preserve cell content when saving an excel file in csv | Excel Discussion (Misc queries) | |||
Remove content of last visited pages in "Insert Hyperlink" dialog | Excel Worksheet Functions | |||
How do I display textboxes without any content in a spreadsheet? | Excel Discussion (Misc queries) |