MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Database values Protection


Posted by rachel on October 29, 2001 11:27 AM

How can I protect Excel DataBase values from being
changed and geting an empty cell as a result of
accidental delet?
Thanks.


Posted by Leroy on October 30, 2001 6:05 AM

Rachel,

A couple of suggestions:

1. If you are using a form to enter the values into the excel db, use this simple macro on the Worksheet Change event to stop people changing values. The reason this works is because the excel data form does not fire the worksheet change macro. It will effect the entry of all data on the worksheet. However you should have your database in a separate sheet anyway.


'-------------------------------
Public UndoVal As Variant


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
For Each i In Target

' User should not be editing information
' Note: When Application.Undo is called it will fire this
' subroutine again. The global variable UndoVal is set to UNDONE to
' stop the potential infinite loop


If (UndoVal <> "UNDONE") Then

If i.Locked = True Then

MsgBox "Attention - This cell is locked and unable to be changed", vbExclamation, "Attention"
UndoVal = "UNDONE"
Application.Undo

End If

End If

UndoVal = ""
Exit For

Next

End Sub
'-----------------

2. A more simple solution is to hide the rows that make up your database. This could deter "accidental" deletions.

Hope some of this is vaguely helpful.

L.

Posted by Rachel on November 03, 2001 6:44 AM

Leroy,
Thank you for your suggestions, but how come the
form did fire the worksheet change macro, and
prevent data entry from the forms ??
thanks again,
Rachel.

If (UndoVal <> "UNDONE") Then If i.Locked = True Then

Posted by Leroy on November 05, 2001 12:22 AM

Rachel,

Are you using the standard excel form ie: from the main menu - Data -> Form..?

If so, when you enter a record by typing values and clicking on the "new" button, the values are inserted into the next blank row of your database defined area within the worksheet. The Worksheet Change macro is not fired at this point.

If you are using another method of data entry, let me know what this is and we can have a look at a better solution.

Rgds,
Leroy. Leroy,

If (UndoVal <> "UNDONE") Then If i.Locked = True Then

Posted by Rachel on November 05, 2001 2:31 PM

Leroy,

I added a VBA UserForm object to a project, displaying the form by the Show method:
(Mainfrm.show) to the worksheet_open event.
The values are inserted into the a new row at the begining of the database (row 2).
Does it make any difference?

Thank you,
Rachel.

If (UndoVal <> "UNDONE") Then If i.Locked = True Then

Posted by Leroy on November 06, 2001 10:06 AM

Hi Rachel,

I would suggest that you protect the worksheet using the menu and resave workbook.

Then in the commandbutton_click event of your userform unprotect the sheet and then reprotect it once you have inserted form values into the worksheet.

eg:
Private Sub CommandButton1_Click()
Worksheets("Sheet1").unprotect
' move your values from textboxes to cells
Worksheets("Sheet1").protect
End Sub

It is hard to know exact syntax without seeing your code, however this would make sure the worksheet was always protected. You can also put a password in the Protect method (see help)

Hope this is of some help.
L.
If (UndoVal <> "UNDONE") Then If i.Locked = True Then

Posted by Rachel on November 06, 2001 11:17 AM

Hi Leroy,

Wonderful solution, embarressedly Simple.
Thanks for your help and the time.

Rgds,
Rachel.

Hi Rachel, I would suggest that you protect the worksheet using the menu and resave workbook. Then in the commandbutton_click event of your userform unprotect the sheet and then reprotect it once you have inserted form values into the worksheet. eg: If (UndoVal <> "UNDONE") Then If i.Locked = True Then

Posted by Leroy on November 07, 2001 12:03 AM

More than welcome!

Wonderful solution, embarressedly Simple. Thanks for your help and the time. Rgds, Rachel.

If (UndoVal <> "UNDONE") Then If i.Locked = True Then

Posted by Rachel on November 07, 2001 8:52 PM

One more question about Replacement

Hi leroy,

I wanted to release the sheet protection again
in order to enable correction on the worksheet,
using the replacement dialog box,like this:
Application.Dialogs(xlDialogFormulaReplace).Show,
but the dialog box could find the values I asked
for whice were on the worksheet.
Why?
thanks again.

Rgds
Rachel

Wonderful solution, embarressedly Simple. Thanks for your help and the time. : Rgds, Rachel. If (UndoVal <> "UNDONE") Then If i.Locked = True Then