Data form not triggering Worksheet_Change event

[Gompies]

I'm trying to input some data in a worksheet with the build in Data
Form. After insertion I want a to trigger the Worksheet_Change event
(to validate the input). But data entry with the Data Form won't fire
this change event.
Is there some workaround?

 

[IanKR]

I'm trying to input some data in a worksheet with the build in Data

Form. After insertion I want a to trigger the Worksheet_Change event
(to validate the input). But data entry with the Data Form won't fire
this change event.
Is there some workaround?

Use the Worksheet_Calculate event instead. The Worksheet_Change event is
fired only when the user changes the worksheet directly, and not via a
userform.

 

[IanKR]

Use the Worksheet_Calculate event instead. The Worksheet_Change event is

fired only when the user changes the worksheet directly, and not via a
userform.

Please ignore my post - it's wrong. I managed to get the Change event to
fire in these circumstances, so I'm afraid I don't know the answer to your
problem. Apologies.

However, I have had problems in these circumstances before. There are
occasions when you'd expect the Change event to fire - but doesn't - and the
Calculate event does fire, which does the trick. Could you post your code?

 

[Jim Rech]

But data entry with the Data Form won't fire this change event.

That does seem to an problem. The calc event idea maybe isn't so good
because there is no way to know which cell changed. You might try this
alternative to the built-in Data form from John Walkenbach. It's a better
answer in any case and (I assume) it doesn't block the change event.


http://spreadsheetpage.com/index.php/dataform/home

 

[Bret Bernever]

so I'm afraid I don't know the answer to your
problem. Apologies.

No problem at all. Appreciate you just gave it a try. Tried the
worksheet_calculate also, but it didn't pull the trick. I made a dummy
field where a value was adjusted after I inserted a new record with the
data form. No success!

Could you post your code?

Well that's not important here. I have a range with data and after
choosing Data - Form... from the menu, excel shows the form. After
inserting a new record the worksheet_change event should trigger and
perform some VLookUp operation like:

******
Private Sub Worksheet_Change(ByVal Target As Range)
strAddress = Application.VLookup(Me.Textbox1.Text, rngCodes, 3, False)
end sub
******

I'm just interested in tackling this problem.

Kind regards
Bert

 

[Bret Bernever]

You might try this
alternative to the built-in Data form from John Walkenbach. It's a better
answer in any case and (I assume) it doesn't block the change event.

Thanks Jim, Ill give John's enhanced from a try. I was just curious
about tackling this specific issue.

 

[Bret Bernever]

PS. Bret and Gompies are the same persons.

Posted my question at work and replied at home ;o))

Kind regards,
Bret