disallow edit in a cell with data

P

prkhan56

Hello All,
I use Office 2003.
I have a worksheet where I want to prevent users from changing data
once it is
entered in a blank cell.
Is it possible with a macro where it should
1) save the worksheet once data is entered
2) ask for a password if someone tries to edit a cell with an entry in
it.

Thanks in advance.

Rashid Khan
 
P

Peter T

First "Unlock" all cells on the sheet
Select all cells, the little square that intersects row/col headers
Format, Cells, Protection, Locked - uncheck

Rt-Click the sheet tab, view-code to open the worksheet module and paste in
the following

Private Sub Worksheet_Change(ByVal Target As Range)

Me.Unprotect "abc"
Target.Locked = True
Me.Protect Password:="abc", Contents:=True

End Sub

You could ignore the password entirely, depending on what user is allowed to
access to.

So what happens if user makes a mistake when entering the data ???

Regards,
Peter T
 
P

prkhan56

First "Unlock" all cells on the sheet
Select all cells, the little square that intersects row/col headers
Format, Cells, Protection, Locked - uncheck

Rt-Click the sheet tab, view-code to open the worksheet module and paste in
the following

Private Sub Worksheet_Change(ByVal Target As Range)

    Me.Unprotect "abc"
    Target.Locked = True
    Me.Protect Password:="abc", Contents:=True

End Sub

You could ignore the password entirely, depending on what user is allowedto
access to.

So what happens if user makes a mistake when entering the data ???

Regards,
Peter T








- Show quoted text -
Click to expand...

Thanks for your help. Editing is not allowed if someone makes a
mistake, he/she should contact the admin for help.

Can it prop up a message box saying to contact the admin for the
password/help?

Regards
Rashid Khan
 
P

Peter T

Thanks for your help. Editing is not allowed if someone makes
a mistake, he/she should contact the admin for help.
Click to expand...

OK, so the code I suggested should work for your purposes. Maybe start by
only "unlocking" cells user can enter data once only. Did you try the
sugegstion.
Can it prop up a message box saying to contact the
admin for the password/help?
Click to expand...

Probably, but in what scenario, your question is not clear.

Regards
Rashid Khan
 
P

prkhan56

OK, so the code I suggested should work for your purposes. Maybe start by
only "unlocking" cells user can enter data once only. Did you try the
sugegstion.


Probably, but in what scenario, your question is not clear.

RegardsRashidKhan
Click to expand...

Sorry my system was down.
Actually the solution you provided works but mine is a shared workbook
and it does not work with shared workbook
However, if a msg box can prop up when the user tries to edit the
cell.

Thanks

Rashid Khan
 
P

Peter T

I don't have experience of shared workbooks and cannot test what works or
not. Maybe if there is someway to update changes in both users the original
suggestion will work.

Regards,
Peter T
 
Top