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.

 

[malie22001]

VoG,

I am having a little bit of problem with the second code. When I try to run it, run time gives me an error that reads:

"unable to set the hidden property of the Range class".

How can I solve that?

 

[VoG]

Perhaps

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

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

 

[malie22001]

I am so sorry for not getting this, but the code is still giving the same error message.

 

[VoG]

Sorry, it should be

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

 

[malie22001]

Ok VoG,

You have been patient and nice to me. The truth is this new code works, but its not quite doing what I want. How about if we do something like this:

1. Put all formulas in a range and name the range. Then we protect the range so that no one can edit the formulas.

2. When a user attempts to save the sheet, a message box should pop up warning the user "Are you sure you want to save this file? After saving this worksheet, you can no longer edit it." If the user clicks Yes, the worksheet saves and locks; if the user clicks No, the worksheet doesn't save and so it doesn't lock.

3. Similar concept is applied when the user attempts to go to the next sheet.

In this way, I think users will not be able to mistakenly save the sheet or change to another sheet thereby mistakenly locking he sheets.


Overall; the same idea in the code right now stays, but just that the formulas are protected and then users are warned to ensure they do not lock an empty sheet.

What do you think? Are these ideas possible? I am really good in C#, but awful in VB and excel. So, can it work like this?

 

[VoG]

OK, see if you can work with this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If MsgBox("Are you sure you want to save this file?" & vbCrLf & _
"After saving this worksheet, you can no longer edit it.", vbYesNo + vbQuestion) = vbYes Then
    ActiveSheet.Unprotect Password:="abc"
    ActiveSheet.UsedRange.Hidden = True
    ActiveSheet.Protect Password:="abc"
Else
    Cancel = True
End If
End Sub

 

[malie22001]

Great!!!!!!!

This works just the way I want it to work for the restriction on changes. However, I want the cells that have formulas to be locked as soon as the sheet opens and should remain locked.

Those cells have been done in a way that they do not require any user input. I also believe that if they are left unprotected users can deliberately or mistakenly add data to them and doing so will jeopardize the rest of the program.

I just want all the cells that have formulas to be locked throughout the life of the application. The only time they should be opened is when I want to make changes to them and during then, I can use a password to open them and when I lock them again, they should remain locked.

We are very close to the end now. Thanks for your help and patience.

 

[malie22001]

Hi Guys,

I am still having hard time with my work. I really need your help. Here is the situation.

Can I apply two level protection on my sheet? That is, Can I create two ranges and then protect each range separately from the other?

Like this:

Create two ranges; one named editCells and the other formulaCells. When the workbook is opened, users can only enter data in the editCells range, but when the user clicks the save button, both the editCells and formulaCells are locked.

Can someone help me with this pleasssssssssssssssssssssseeeeeeee?

Thanks guys.