Forum Discussion
replacing text from a formula in another cell
I seek to interrogate the value in a cell (integer 0 or 1), and then arbitrarily force a text value into another free text entry cell. Example. A1 has an integer value that is unknown (and not rele...
This requires VBA code (so it'll work only in the desktop version of Excel for Windows and Mac).
In your example:
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B1").Value, Target) Is Nothing Then
If Range("B1").Value = 1 Then
Application.EnableEvents = False
Range("A1").Value = 0
Application.EnableEvents = True
End If
End If
End SubP.S. This assumes that the user enters 0 or 1 in cell B1. If B1 contains a formula that returns 0 or 1, use
Private Sub Worksheet_Calculate()
If Range("B1").Value = 1 Then
Application.EnableEvents = False
Range("A1").Value = 0
Application.EnableEvents = True
End If
End Sub
Awesome. I didnt expect this to be a sheet specific thing, was hoping it could be cell specific because I Want to do this one cell update, to force values into a number of cells. Could I repeat line 5 above for each of the cells I want to reset?
- This actually generates a "Run-time error '424'...the explanation of which might as well be written in Mandarin for all the sense it makes. Error 424 says "object required" and the error line in debug is line 2.. "If Not Intersect(Range("E2").Value, Target) Is Nothing Then" (I am actually using E2 as the trigger and D2 as the data cell to change)
Note this error 424 comes up no matter what value I put in E2 (0 or 1). Correction. The error comes up if I change the value in ANY cell.
My apologies, it was air code. It should have been
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("E2"), Target) Is Nothing Then If Range("E2").Value = 1 Then Application.EnableEvents = False Range("D2").Value = 0 Application.EnableEvents = True End If End If End Sub