[SOLVED] Excell 2003 - need help on an issue VBA- locking fields after edit

mikibanana · 10-26-2009, 03:00 AM

Hi,
i need help on setting up an excel worksheet where users can edit fields but after editing, these fields need to be locked. Right now i have set it up that my whole column A is locked and range A1:M4 (this is where the heading is) is also locked. I have locked these cells and protected the worksheet. Now i need employees to open the excell worksheet and be able to type in data in empty cells in range B5:M49 and for this data to be locked and cant be tampered with after input. Some of the code present on the web for solving my problem is
###############
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.Protect Contents:=False
For Each cell In Range("A1:A5")
If cell <> "" Then cell.Locked = True
Next
ActiveSheet.Protect Contents:=True
End Sub
##################
but for some reason it does not work. I have set up security in excel to low. If someone has some code that helps me please reply.

mikibanana · 10-26-2009, 08:11 AM

finally found something that works, rewrote it a little bit and here it is.

#####

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = True
ActiveSheet.Unprotect Password:="test"
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("a65536").End(xlUp).Offset(2, 0).Select

ActiveSheet.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=False, AllowFormattingColumns:=False, _
AllowFormattingRows:=False, AllowInsertingHyperlinks:=True
ActiveWorkbook.Save

End Sub

#############

this made it possible for workers to input data, click save after they are finished and then could not touch the data again. the worksheet is password protected and the code is password protected in the visual basic editor so no one can see the password or edit the code.
steps:
1. right click "Sheet1"--"view code" -- in the "Project - VBAProject" double click ThisWorkbook and paste the code.

hope this helps someone :)

10-26-2009, 03:00 AM	#1 (permalink)
mikibanana Registered User Join Date: Oct 2009 Posts: 2 OS: xp	[SOLVED] Excell 2003 - need help on an issue VBA- locking fields after edit Hi, i need help on setting up an excel worksheet where users can edit fields but after editing, these fields need to be locked. Right now i have set it up that my whole column A is locked and range A1:M4 (this is where the heading is) is also locked. I have locked these cells and protected the worksheet. Now i need employees to open the excell worksheet and be able to type in data in empty cells in range B5:M49 and for this data to be locked and cant be tampered with after input. Some of the code present on the web for solving my problem is ############### Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ActiveSheet.Protect Contents:=False For Each cell In Range("A1:A5") If cell <> "" Then cell.Locked = True Next ActiveSheet.Protect Contents:=True End Sub ################## but for some reason it does not work. I have set up security in excel to low. If someone has some code that helps me please reply.

10-26-2009, 08:11 AM	#2 (permalink)
mikibanana Registered User Join Date: Oct 2009 Posts: 2 OS: xp	Re: Excell 2003 - need help on an issue VBA- locking fields after edit finally found something that works, rewrote it a little bit and here it is. ##### Code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.ScreenUpdating = True ActiveSheet.Unprotect Password:="test" Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.Locked = True Selection.FormulaHidden = False Range("a65536").End(xlUp).Offset(2, 0).Select ActiveSheet.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=False, AllowFormattingColumns:=False, _ AllowFormattingRows:=False, AllowInsertingHyperlinks:=True ActiveWorkbook.Save End Sub ############# this made it possible for workers to input data, click save after they are finished and then could not touch the data again. the worksheet is password protected and the code is password protected in the visual basic editor so no one can see the password or edit the code. steps: 1. right click "Sheet1"--"view code" -- in the "Project - VBAProject" double click ThisWorkbook and paste the code. hope this helps someone :)