Leaving a Cell Value Unchanged If a Condition Is False

@Dexraj
Sorry for the mess in my previous comment below. That is what happens if you submit a comment that is "suspected as spam" and you click to "have your comment reviewed." Everything becomes compressed into a single paragraph. I'll try again with the following:
Did you use a Check Box from the Form Controls or the ActiveX Controls? Did you link it to cell U5?
See https://excelribbon.tips.net/T008392_Using_Check_Boxes.html
Assuming you used a Check Box from the Form Controls and linked it to cell U5, do this:
1. Press Alt+F11 to open the Visual Basic Editor (VBE).
2. From the VBE menu, pick Insert > Module.
3. Add this VBA to the new module:

Public Sub CheckBoxEvent()
If Range("U5").Value Then Range("D5").Value = Range("S5").Value
End Sub

4. Delete your previous VBA.
5. Right-click the Check Box and pick Assign Macro..., then pick CheckBoxEvent and click OK.
6. Click outside the Check Box to unselect it.

Now when you toggle the Check Box cell U5 from FALSE to TRUE, the value of cell S5 will be copied to cell D5. If you change S5 or toggle the Check Box cell U5 from TRUE to FALSE, there is no change to D5. If you change D5, that change will persist until you toggle the Check Box cell U5 from FALSE to TRUE.

@Dexraj Did you use a Check Box from the Form Controls or the ActiveX Controls? Did you link it to cell U5? See https://excelribbon.tips.net/T008392_Using_Check_Boxes.html Assuming you used a Check Box from the Form Controls and linked it to cell U5, do this: 1. Press Alt+F11 to open the Visual Basic Editor (VBE). 2. From the VBE menu, pick Insert > Module. 3. Add this VBA to the new module: Public Sub CheckBoxEvent() If Range("U5").Value Then Range("D5").Value = Range("S5").Value End Sub 4. Delete your previous VBA. 5. Right-click the Check Box and pick Assign Macro..., then pick CheckBoxEvent and click OK. 6. Click outside the Check Box to unselect it. Now when you toggle the Check Box cell U5 from FALSE to TRUE, the value of cell S5 will be copied to cell D5. If you change S5 or toggle the Check Box cell U5 from TRUE to FALSE, there is no change to D5. If you change D5, that change will persist until you toggle the Check Box cell U5 from FALSE to TRUE.

Thank you for your post. I am trying to update the date in a cell (in cell D5) to the new date (that is in cell S5) when the this year's work is done (that is once a check Box in cell U5 is checked or is TRUE), and tried to use your code that didn't work, then modified code as follows, and still not updating to new date, once check box is checked. The need to be updated once the checkbox is checked, otherwise remains unchanged.
I tried following 3 codes, none is updating the date in column D(cell D5)

'Private Sub Worksheet_SheetChange(ByVal Sh As Object, _
' ByVal Target As Range)
'
'If (U5.Value = True) Then D5.Value = S5.Value Else D5.Value

'
'
' ' See if the change is related to our cell
' If (Application.Intersect(Target, Range("U5")) _
' Is False) Then
' If Range("U5") = True Then
' Range("D5") = Range("S5")
' End If
' End If
'End Sub

Private Sub Sheet1_Change(ByVal Target As Range)

If Sheet1.Range("U5").Value = True Then 'cell U5 is a checkbox
Sheet1.Range("D5").Value = Sheet1.Range("S5").Value ' cell D5 has this years date, and cell S5 has new date a year later

Else
Sheet1.Range("D5").Value
End If

End Sub

Would appreciate if you could please identify the issue, and let me know how to fix it.

Thanks

Nice help. Very good!!

This is so amazing. Updating a cell on a check has been a frustration forever! As a thank you, here is a fun script I wrote to pull the URL of the specific tab you are in. I use it to create a master directory that automatically pulls a URL column to click between tabs.

/*
From Sean Pryor 10/30/2020
This function will return the URL of the current tab.
*/

function Tab() {
var a = SpreadsheetApp.getActiveSpreadsheet();
var b = a.getActiveSheet().getSheetId();
var c = a.getUrl();
var d = c+"#gid="+b;
return d;
}

Now that, sir, is one of the best tips I have ever seen

Allen, you wrote:

There is one "gotcha" that you need to keep in mind with any of the approaches discussed thus far, formula or macro. If the value in cell A1 is (let's say) 15, then cell B1 will contain what was there before, whatever it was. If you change the value in cell A1 to (let's say) 7, then B1 will change to "under ten." That's fine, but from that point on cell B1 will never appear to change. Why? Because if you then change cell A1 to a value greater than 10, cell B1 will contain (as just explained) what was there before. And, as you now understand, the value that was there before is the result of the previous true result, which was "under ten." Thus, true or false, the formula or macro from this point on displays the text "under ten."

I believe there IS a solution that does not require a circular reference OR a macro.

The solution is elegant and simple (IMO!): Using your example, simply designate an intermediate cell in a hidden part of the worksheet to equal A1. Then make references in your formula refer to that hidden cell. In my example below, I have use Cell D1 as the intermediate cell. I can vary the value in A1, and each time, B1 reports properly.
{[fig}]

I have been using a different formula if the result is false. I just substitute the "B1" in this example for "", which returns a blank cell. That eliminates having to turn off circular reference