Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells IF the cell is not empty
Planning to make this excel file in which users should be able to fill in new
data in empty cells but not in cells that have data in them already. I.e. IF Cell is empty, the user is allowed to fill in data. IF Cell is not empty, cell is protected. This way the users can only add new data to the chart but not modify old data. thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells IF the cell is not empty
right click on the sheet tab and put in code like this:
Before doing that, unlock all your cells, then select filled cells and lock those. Then protect the sheet with your password. Just for consideration, if the user makes a typo when they enter data or enter data in the wrong cell, there is no correcting it. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrHandler Me.Unprotect Password:="ABCD" For Each cell In Target If Not IsEmpty(cell) Then cell.Locked = True End If Next ErrHandler: Me.Protect Password:="ABCD:" End Sub -- Regards, Tom Ogilvy "Boka" wrote in message ... Planning to make this excel file in which users should be able to fill in new data in empty cells but not in cells that have data in them already. I.e. IF Cell is empty, the user is allowed to fill in data. IF Cell is not empty, cell is protected. This way the users can only add new data to the chart but not modify old data. thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells IF the cell is not empty
Thanks for the help but unfortunately your solution only worked once. Only
one cell got locked (stayed locked) after typing something into it. The other cells were modifyable over and over again. It's strange since looking at your code it looks as it should work. Any suggestions? "Tom Ogilvy" wrote: right click on the sheet tab and put in code like this: Before doing that, unlock all your cells, then select filled cells and lock those. Then protect the sheet with your password. Just for consideration, if the user makes a typo when they enter data or enter data in the wrong cell, there is no correcting it. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrHandler Me.Unprotect Password:="ABCD" For Each cell In Target If Not IsEmpty(cell) Then cell.Locked = True End If Next ErrHandler: Me.Protect Password:="ABCD:" End Sub -- Regards, Tom Ogilvy "Boka" wrote in message ... Planning to make this excel file in which users should be able to fill in new data in empty cells but not in cells that have data in them already. I.e. IF Cell is empty, the user is allowed to fill in data. IF Cell is not empty, cell is protected. This way the users can only add new data to the chart but not modify old data. thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells IF the cell is not empty
Tom's code (with one modification) worked fine for me:
Me.Protect Password:="ABCD:" became Me.Protect Password:="ABCD" And I made sure the cells that could change were unlocked to start. (I'm guessing that Tom saves lots of stuff to his D: drive (muscle memory strikes again!) <vbg.) Boka wrote: Thanks for the help but unfortunately your solution only worked once. Only one cell got locked (stayed locked) after typing something into it. The other cells were modifyable over and over again. It's strange since looking at your code it looks as it should work. Any suggestions? "Tom Ogilvy" wrote: right click on the sheet tab and put in code like this: Before doing that, unlock all your cells, then select filled cells and lock those. Then protect the sheet with your password. Just for consideration, if the user makes a typo when they enter data or enter data in the wrong cell, there is no correcting it. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrHandler Me.Unprotect Password:="ABCD" For Each cell In Target If Not IsEmpty(cell) Then cell.Locked = True End If Next ErrHandler: Me.Protect Password:="ABCD:" End Sub -- Regards, Tom Ogilvy "Boka" wrote in message ... Planning to make this excel file in which users should be able to fill in new data in empty cells but not in cells that have data in them already. I.e. IF Cell is empty, the user is allowed to fill in data. IF Cell is not empty, cell is protected. This way the users can only add new data to the chart but not modify old data. thanks in advance -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum of cells until reach empty cell | Excel Worksheet Functions | |||
count non empty cells if other cell has certain value | Excel Discussion (Misc queries) | |||
protect cells based on another cell | Excel Worksheet Functions | |||
VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other? | Excel Programming | |||
VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other? | Excel Programming |