Overwrite a cell that is calculated?

[bandit_1981]

Ok I do not think this is even possiable but i want to make sure before I tell them no. They want to have cell values calculate but in the case that it calculatues wrong or an exception to a rule occurs they want to be able to overwrite the value. Since the forumla is written in the cell they cant just type it into the cell becuase that would get rid of the formula and it would become data entry from there on. Any Ideas.

 

[bandit_1981]

Sure No problem, When we save the speadsheet we are not saving it as an xls file. I pull out the data I need and save it in to a CSV file. So while saving it out while using the sheet is not a problem loading it back up will be. To help with this i took your idea but used a differnt code method. I might be off but here is what i am using.

</code>
Public OldValue As Variant
Public OldAddr As Variant


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.HasFormula = True Then
OldValue = Target.Formula
Else
OldValue = ""
End If

OldAddr = Target.Address

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

Dim curComment As String
Dim currFormula As String

currFormula = OldValue
If OldAddr <> "" Then
If Target.HasFormula = False And currFormula <> "" And Target.value > 0 Then
Range(OldAddr).AddComment (currFormula)
End If
If Target.HasFormula = False And Range(OldAddr).Comment.Text <> "" And Val(Target.value) = 0 Then
Target.Formula = Range(OldAddr).Comment.Text
Range(OldAddr).AddComment("")
End If
End If

End Sub

</code>

This takes it and stores it in the comments so i can access it when i goto save out. This code saves and it out when the value is changed and puts it back when the value is gone. I am thinking about making some UID and loop through the range and if it has a comment save it out wiht the cell name as the UID.

 

[Joe4]

I think you will lose your code if you save it to a .CSV file, as it then is no longer an Excel file.

I never really work with comments, so I really can't provide any advice on that, but I am pretty sure you will lose those also if saved to a .CSV file.

 

[bandit_1981]

Ok i have it working. When they change a cell that has a formula, that formula gets placed inside the comments. If they delete the value they entered the formula gets placed back into the cell via the comments. When ever a cell has a comment in it I change the background color of that cell so the user knows the overrode the value. When i save my data out to the CSV file i tag on any cell that has comments with it under a UID. This way i can read the comments back in since i cant overwirte the template i am using.

Thanks for all your help you ideas really pointed me in the right direction.

Dan