Need to have result from a formula in one cell appear in another cell...

[danielneedssomehelp]

I have a formula in A1 (it references about 100 different cells), and I'd like the result of A1 to appear in A2 without A2 containing a formula.

There might be a REALLY simple solution to this though without reading the stuff below. I'm not opposed to locking A2, but when I do that, somehow the ability to copy the contents in A2 doesn't work. Maybe it's because I have checkboxes on my spreadsheet or there's a macro... I have no idea. But if there were a way to copy the results from A2 while that cell is locked, then that is all I would need. I could unlock that cell, but then there's the problem of the formula appearing if you double click the cell, and then all the stuff below...

If I can't copy the contents of a locked then, then I could unlock the cell and use the following macro, but then that creates other problems....

Here's the macro:

Private Sub Worksheet_Change (ByVal Target As Range)
Range("CellWithValue").Value = Range("CellWithFormula").Value
End Sub

In theory, this code works great because any time there is a change to the worksheet, the output in A2 always updates. Here's is my problem... I have a command button macro which deletes about 200 fields on the worksheet when clicked, but when I use the code above, it takes a while for all the fields to be cleared because every time a field is cleared it counts as a change and then the code above runs 200 times, which in turn increases the time it takes for the command button to run and clear all the fields.

So I need a macro or something that hardcodes the results in A1 to A2, and it must be dynamic so that if there is a change on the worksheet, A2 is updated, BUT I want to keep my "clear all fields" command button without it running so slowly.

Here's my idea: if you click on the "clear all" command button, it clears all fields, but only the last field cleared is identified by the code above as a change. So the "clear all" button clears all fields, and isn't slowed down by the code above.

Any idea on how to do that?

[stnkynts]

You could use Application.EnableEvents = False before the code runs and Application.EnableEvents = True after the code runs where you want to trigger a change.

[mehmetcik]

Use Application.Enable Events = False as described above.

It will stop a change triggering another loop.

If that doesn't work for you, [ Sometimes I only want to stop specific events ] then declare a global variable and use that to control your macro.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[danielneedssomehelp]

Thank you--- I think I'm getting close, just need a little code placement help.

My original code that erased all my checkboxes and desired cells is this:

Private Sub CommandButton1_Click()

ActiveSheet.Shapes(8).ControlFormat.Value = False
ActiveSheet.Shapes(9).ControlFormat.Value = False
ActiveSheet.Shapes(10).ControlFormat.Value = False
ActiveSheet.Shapes(11).ControlFormat.Value = False
ActiveSheet.Shapes(12).ControlFormat.Value = False
ActiveSheet.Shapes(13).ControlFormat.Value = False
ActiveSheet.Shapes(14).ControlFormat.Value = False
ActiveSheet.Shapes(15).ControlFormat.Value = False
ActiveSheet.Shapes(16).ControlFormat.Value = False
ActiveSheet.Shapes(17).ControlFormat.Value = False
ActiveSheet.Shapes(18).ControlFormat.Value = False
ActiveSheet.Shapes(20).ControlFormat.Value = False
Range("Q7:Q42").Select
Selection.ClearContents
Range("O7:O42").Select
Selection.ClearContents
Range("J7:J42").Select
Selection.ClearContents
Range("H7:H42").Select
Selection.ClearContents
Range("C12:C13").Select
Selection.ClearContents
Range("C10").Select
Selection.ClearContents
End Sub

Directly after that, I added:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("$A$2").Value = Range("$A$1").Value
End Sub

Where should I put the Application.EnableEvents = False and Application.EnableEvents = True?

Also, if that doesn't work, where should I put the Public Changeflag as Integer and

Private Sub Worksheet_Change (ByVal Target As Range)
If Changeflag = 1 then exit sub
Changeflag =1
Range("CellWithValue").Value = Range("CellWithFormula").Value
Changeflag = 0
End Sub

And if I add the Public Changeflag as Integer code, would I not need the Application.Enable Events = False code?

Thank you!

[stnkynts]

Please use code tags. Here is your answer:

Please Login or Register  to view this content.

With that being said. Why don't you just combine the 2 codes so that after Range("C10") is cleared you then set Range("A2") = Range("A1") ?

[danielneedssomehelp]

Well, I wanted the content in A2 or A1 to be updated as users input data. When I combine the two codes, that doesn't happen. When I leave the codes separate, it works, but after adding some data into the fields referred to in the code above, Excel freezers up and crashes. You know, I could probably avoid all of this if I could just copy the text from A2 when that cell is locked, but for some reason, I cannot copy any locked cell on this spreadsheet and I think it has to do with the checkboxes I added.