Database values Protection

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.

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

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

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

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

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

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

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