Secutity on Sheets

[malie22001]

Hi,

I am developing a daily sheet report for my boss. In the sheets, users input their daily as well as the number of items they sell per day.

I want to build the sheets so that once a user enters the data in one sheet and move to another sheet; the previous sheet should be locked and no data can be entered anymore unless the person enters a password.

Also, the same thing should happen if the person clicks on the save button in excel.

Can this be done? Is there a way I can do it in VBA or C#?

In summary, I want the cells to allow entry only before a user moves to another sheet or clicks the save button in excel.

 

[VoG]

Try this: press ALT + F11 to open the Visual Basic Editor, in the Project window double click ThisWorkbook and paste into the right hand pane

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.Protect Password:="abc"
End Sub


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Sh.Protect Password:="abc"
End Sub

Then press ALT + Q to close the code window.

Does this work as expected?

 

[dave3009]

Yes this can be done with the use of VBA, however, if you just blanket choose to lock down to sheet if someone navigates away from/saves it you run the risk of locking it before the user enters the data, you need to basically test if the data is in the cells before the lock down happens. What cells are needing locked?

 

[malie22001]

Thanks guys for your response.

I need the entire worksheet locked once the user has moved to another sheet or saved the file.

Can I also hide all of my formulas and VB code?

Scenario:
i am developing a daily report workbook. Each sheet represents one day in a month and each day has two shifts. The guy working on the second shift is working with the closing data from the guy working on the first shift. So if the guy on the first closes with a number of items; say for instance 151 cigarettes. Then the guy on the second shift has to open with 151 cigarettes. So I have written a formula to transfer the closing data of one sheet to the opening data of the other sheet. but if the guy on the second shift is able to change data from the first shift, he can manipulate the application to work the way he wants.

So, the bottom line is; I don't want the guy in the second shift to be able to change anything in the first shift and on the other hand, I don't want any employee to be able to change any data once they have saved it.

Thanks guys.

I will appreciate it if you guys also explain the code you write. i am very new to VBA, but I am good in C#. Thanks for your help.

 

[VoG]

Before we go any further, did you try the code that I posted and did it work? If it did then we can embellish it.

 

[malie22001]

VoG:

thank you. your code works, but I will need a way to check the cells to make sure there are data in them. Can I do that too?

 

[malie22001]

VoG:

Yes; the code works. it locks the sheet when I click save or go to another sheet. Also can I hide my formulas and VBA code?

 

[VoG]

You can hide formulas like this

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ActiveSheet.UsedRange.Hidden = True ActiveSheet.Protect Password:="abc" End Sub   Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) Sh.UsedRange.Hidden = True Sh.Protect Password:="abc" End Sub

To hide the code, in the VBE, Tools VBE Project Properties, click the Protection tab, enter a password and click OK. Search this site for completing all required fields - there are tons of threads that address this issue.

 

[malie22001]

Thanks.

 

[VoG]

To hiderthe code in the VBE Tools > Vbe Project Properties > Protection tab and enter a password.

Note: this is crackable.