Need to be able to enter data in a blank cell but not allow anyone to delete the data once entered

[phimaya]

I am fairly new to Excel. I need to create cells where anyone can enter data into a blank cell but not be able to delete the data once entered. Ideally the only function I would like is to allow the user to be able to perform a strike through once data is entered.

 

[mumps]

Which blank cells?

 

[phimaya]

Which blank cells?

I would make a spreadsheet with the the days of the month vertically. I want to be able to allow anyone to enter their name in a cell on any given day. But once it is entered - it cannot be deleted. I would like to allow people to Strike through their name once it is entered but not to be able to delete their name.

Thanks
Paul

 

[mumps]

I need to know the addresses of the blank cells that you want to protect. For example, all of column B or B2 to B100.

 

[phimaya]

I need to know the addresses of the blank cells that you want to protect. For example, all of column B or B2 to B100.

All cells in the spreadsheet.

 

[phimaya]

All cells in the spreadsheet.

The spreadsheet with only have one row for each day of the month therefore it will have 28,29,30 or 31 rows depending on the month. The columns would be limitless but would probably never need to go past 35 columns.

 

[mumps]

I'm assuming that your dates are in column A. Start by unprotecting all the cells starting with column B until the last used column to the right. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. The sheet will be protected with a password. Change the password (in red) in the code to a password of your choosing. Close the code window to return to your sheet. Enter data in any blank cell and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="MyPassword"
    If Target.Column <> 1 Then
        With Target
            .Font.Strikethrough = True
            .Locked = True
        End With
    End If
    With ActiveSheet
        .Protect Password:="MyPassword"
        .EnableSelection = xlUnlockedCells
    End With
    Application.ScreenUpdating = True
End Sub

 

[phimaya]

I'm assuming that your dates are in column A. Start by unprotecting all the cells starting with column B until the last used column to the right. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. The sheet will be protected with a password. Change the password (in red) in the code to a password of your choosing. Close the code window to return to your sheet. Enter data in any blank cell and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="MyPassword"
    If Target.Column <> 1 Then
        With Target
            .Font.Strikethrough = True
            .Locked = True
        End With
    End If
    With ActiveSheet
        .Protect Password:="MyPassword"
        .EnableSelection = xlUnlockedCells
    End With
    Application.ScreenUpdating = True
End Sub

Thank you. I will try this and let you know. I appreciate your time.

 

[phimaya]

I tried the code you wrote for me. Thank you for that code.

Once I enter Data into the cell and hit Return, the name that I just typed gets crossed out automatically.

What I actually need is for people to be able to enter their names in the cells without automatically striking through the name. However, if someone wants to come back to the sheet and cross their name off at a later date then they can do that at anytime. The default shouldn't be strikethrough. I don't want people to be able to delete their names.

Also, the entire sheet becomes protected after entering data into any cell. I want the sheet to stay unprotected.

Is this possible?

Thanks,
Paul

 

[mumps]

The sheet must be protected if you want to protect the cells from being edited. Give this macro a try. It will lock and protect the sheet but will allow you to go back and format the cell with the strikethrough but not allow you to delete it.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="MyPassword"
    If Target.Column <> 1 Then
        Target.Locked = True
    End If
    With ActiveSheet
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True
        .EnableSelection = xlNoRestrictions
    End With
    Application.ScreenUpdating = True
End Sub