Macro To Prevent Data Entry?

Microsoft Excel 2003 (full product)
June 7, 2010 at 10:42:56
Specs: Windows 7
Hello Tom!

I would like to know if there is a macro for preventing employees from retroactively feeding data into cells, by mistake or intentionally, if so- how do I make it Happen?

Your's Truely,

See More: Macro To Prevent Data Entry?

Report •

June 7, 2010 at 13:20:12
This process will allow the user to enter data in Column A just once:

1 - Select Column A
2 - Click Format...Cells...Protection Tab
3 - Uncheck the Locked option
4 - Click Tools...Protection...Protect Sheet
5 - Set Password to the word secret

6 - Click the Sheet tab for the sheet and paste this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was to Column A
 If Target.Column = 1 Then
'If yes, Unprotect sheet
   Sheets("Sheet1").Unprotect Password:="secret"
'Locked changed cell
    Target.Locked = True
'Protect Sheet
   Sheets("Sheet1").Protect Password:="secret"
 End If
End Sub

When the user enters data in Column A, the code will Unprotect the sheet, Lock the cell that was changed and Protect the sheet again.

You should also Hide and Password Protect the code so that the user can't see/changed it.

If you need to know how to do that, let us know.

Report •

June 7, 2010 at 14:59:12
Hello there!

thanks for the reply :)

I'm afraid I havent explained myself that well..

in each worksheet I have the dates in column A
the dates themselves begin from row A3 and on
I want to somehow bind the today function to prevent
employees from editing that specific row once the date is no
longer valid..

Can you help me figure that out?
Thank you again and sorry for the trouble,

Report •

June 7, 2010 at 15:00:11
Also, I've already used protection on some of the rows and
columns :) so if that interrupts with the macro please tell me

Report •

Related Solutions

June 7, 2010 at 15:37:00
re: once the date is no longer valid..

Do you mean once the date has past? (Only you know what "no longer valid" means.)

You need to explain what you are trying to do in more detail. In order to write any macro to lock/protect specific ranges based on specific circumstances requires a pretty specific explanation.

Since we can't see your spreadsheet from where we're sitting, it's difficult to offer a solution that will fit your exact needs without some more details regarding the layout and use of your spreadsheet.

Report •

June 8, 2010 at 00:08:23
Yes I mean once the date has passed
So The Date is in Column A(3:43) then again A(56:94) and once
more A(107:144)- each section is one month
Columns A,B,C are Frozen and protected, Also frozen and
Protected are lines One and Two. Thank you for your patience
and will to help!


Report •

Ask Question