VBA freeze cells

[McM_]

Hi,
is there a possibility of obtaining a VBA code that will block the rewriting of a cell after it has been completed?
In the attached table, I managed to keep the date and time locked. The text in cell E2 is a drop-down list, and column E will be filled when the cells in column B are filled. But once the cells in E are written, even if the text in E2 will change, what has been written so far should remain unchanged.
I simulated in E3:E7, with the reference "client 1" from E2, and if E2 changes to "client 2", the information from E3:E7 should remain unchanged, i.e. "client 1". It's a lot of text, but I hope I have understood what I need.
Thank you for help!

Private Sub worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Range("C" & Target.Row) = Date
Range("D" & Target.Row) = Time
'Range("E" & Target.Row) = ??
End If
End Sub

freeze cell.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2		write something	block data	block time	client 2				client 1
3		343	27.12.2023	14:33	client 1				client 2
4		556	27.12.2023	14:33	client 1
5		234	27.12.2023	14:33	client 1
6		443	27.12.2023	14:33	client 1
7		334	27.12.2023	14:33	client 1
8		332	27.12.2023	14:33	client 2
9
10
11
12
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$I$2:$I$3

 

[Micron]

Not sure exactly what you are trying to do. You want to be able to over-write an E cell formula with a value yet keep the results of the rest of the E row formulas the same even if they depend on the value that comes before? No idea if possible. One would also have to deal not just with what follows but with what proceeds. In other words, if E8 is overwritten you'd have to deal with what comes before and after it? Your example uses the top most cell in the cascading problem.

If instead you mean if someone edits E5 you want to prevent that and keep the existing formula (thus its result) that is a different matter. In that case, what comes to mind is to use the selection change event to get the cell value before any changes can be made and pass that value to a module level variable. Then in the change event, compare the old and new values and code accordingly. However, will change your formula in the cell to the value it returned. If that is a problem, then instead of getting the old value as a value, get the formula in the cell and use that.

Maybe what you want is neither. I don't use xl2bb so I have to wonder why you'd use it and yet post pics of it. That prevents anyone from knowing what is in the other cells?

 

[McM_]

I give more details.
Cell E2 is a drop-down list of employees who will enter the service. At the beginning of their work they will select their name. When they complete in column B, the VBA code in columns C and D automatically completes but also freezes the date and time. Likewise, I want to fill in the employee's name in the E3:E column, but also to freeze the information. When the next employee changes the name in E2, what is completed should remain unchanged.

freeze cell.xlsm
	A	B	C	D	E	F
1
2	of work is employed	write something	block data	block time	employee name
3	employee 1	54656	27.12.2023	20:08	employee 1
4	employee 2	767567	27.12.2023	20:08	employee 2
5	employee 2	757567	27.12.2023	20:08	employee 2
6	employee 3	4589	27.12.2023	20:08	employee 3
7	employee 1	54565463	27.12.2023	20:08	employee 1
8	employee 1	454565	27.12.2023	20:08	employee 1
9
10
11
12
13
14
15
16
17
Sheet1

 

[kevin9999]

See if this does what you're looking for - try it on a copy of your worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 Then
        Dim OldVal As String, NewVal As String
        Application.EnableEvents = False
            NewVal = Target.Value
            Application.Undo
            OldVal = Target.Value
            If Len(OldVal) > 0 Then
                MsgBox "Previous entries cannot be changed"
                Target = OldVal
            Else
                Target = NewVal
            End If
            If Target.Column = 2 Then
                Target.Offset(, 1) = Date: Target.Offset(, 2) = Time
            End If
        Application.EnableEvents = True
    End If
End Sub

 

[McM_]

kevin9999
Yes, it's what I'm looking for, but I need to be able to make changes in E2. When B9 is filled, E9=E2, then E9 should be frozen.
When B10 is filled, E10=E2 then the cell should be blocked and so on.
E2 will be a drop-down list of employee names.
Thanks, kevin9999

 

[kevin9999]

I think I understand. Try the following variation:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Target.Row > 2 Then
        Dim OldVal As String, NewVal As String
        Application.EnableEvents = False
            NewVal = Target.Value
            Application.Undo
            OldVal = Target.Value
            If Len(OldVal) > 0 Then
                MsgBox "Previous entries cannot be changed"
                Target = OldVal
            Else
                Target = NewVal
            End If
            If Target.Column = 2 Then
                Target.Offset(, 1) = Date: Target.Offset(, 2) = Time: Target.Offset(, 3) = [E2]
            End If
        Application.EnableEvents = True
    End If
End Sub

 

[McM_]

Thanks, kevin9999
This is what I needed.

 

[kevin9999]

Excellent, and thanks for the feedback