0

When using an onchange event on some worksheet, I wish to change the Target cell's value inside the onchange script.

I haven't found a way to do this. I've tried:

Target = "some value"
Range(Target.Address) = "some value"
Target.Value = "some value"

But to no avail.

I'm aware that this may trigger again the onchange event, but the script is prepared for that. And in case it is not supposed to be triggered, I may use

Application.EnableEvents = False

before any update and

Application.EnableEvents = True

when done...

Any ideas?

Community
  • 1
  • 1
Javier Novoa C.
  • 11,257
  • 13
  • 57
  • 75
  • 1
    Just one suggestion. Do read this http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640 I recommend this because you are working with `Worksheet_Change` event... – Siddharth Rout Jan 08 '13 at 05:04

1 Answers1

3

I know this isn't much of an answer but I need to be able to post code.

The following works absolutely fine for me when entered in the worksheet code section:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Target.Value = "some value"
    Application.EnableEvents = True
End Sub

I select a cell, type "a" and press enter; it's replaced with the text "some value". If you want cell contents to be replaced as you type, you're out of luck; as far as I know the event will only fire after the cell content change.

mkingston
  • 2,678
  • 16
  • 26
  • nope, I'm not asking for code to be replaced as I type, I'm looking for just what you posted. The thing is it doesn't works for me :-( What about making Target.Value = "" ? does it still works? – Javier Novoa C. Jan 08 '13 at 03:02
  • 1
    oh, ok! it worked now, guess I was doing something wrong, I'll have to review my code to see where I was doing it wrong... THanks! – Javier Novoa C. Jan 08 '13 at 03:52
  • Sorry about the slightly late reply; you didn't have application.enableevents set to false, did you? – mkingston Jan 09 '13 at 01:24
  • no, that was not my mistake.... I was updating two cells on two sheets at a time. I updated correctly one but didn't noticed. The other one, I just forgot to code it. Shame on me! :P – Javier Novoa C. Jan 15 '13 at 19:47