Re: Macro referencing data validation doesn't work

I was reading some reviews for the "student edition" and it mentioned that the VBA was removed - I wasn't sure if that applied to the professional edition. I can't believe that! I just started learning it and now I feel like I'm wasting my time!

I like the suggestion for the userform, but I typically don't use those because they don't have a "natural" look with the rest of the spreadsheet. Maybe I'll do a custom dialog box that pops up when "complete" is selected.

Thanks for the help John!
_____________________________________________

Excel 2008 has no VBA (and never will have) so whatever happens there is not
going to be useful to you :-)

I think you will need to use a macro that is fired by a userform, not a data
validation pull-down.

The issue is not the macro code, it's the fact that the trigger event does
not fire. If you fire the macro with a userform drop-down list, your code
will run.

So you will need to add the validation to the macro code, instead of leaving
it to Excel's data validation.

If you re-write your code to check the output cell for a date first, then
you can code it to leave the date alone if it already exists. If you need
to store the date every time, but not change it if the user selects
"Complete", then you will need to store the completion date on a hidden
sheet.

Cheers


On 31/10/09 9:09 AM, in article 59b7f8f2.3@xxxxxxxxxxxxxxxxxxxxxxx,
"Samba1@xxxxxxxxxxxxxxxx" wrote:

On 10/29/09 10:31 PM, in article 59b7f8f2.1@xxxxxxxxxxxxxxxxxxxxxxx,
"Samba1@xxxxxxxxxxxxxxxx" wrote:

On 10/29/09 2:07 AM, in article 59b7f8f2.-1@xxxxxxxxxxxxxxxxxxxxxxx,

Yep! That is a long time bug. The worksheet change event does not fire when
a value is selected from a data validation list. You'll just have to live
with it until the next version of Excel.

--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom


Hi Bob - Thanks for the info! Is this problem corrected with the new office?

Also - is there a function that would take care of this? I've tried TODAY()
and NOW() but both of these update the date as each day passes.

Thanks!
I have no idea about the new office. We'll all have to wait to see if it
gets fixed. Yes, today() and Now() get updated with each recalculation. That
is not the issue with data validation. The problem is that selecting an item
from a data validation list does not activate the worksheet changed event,
even though the worksheet does actually change.

--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

I was thinking about Office 2008. No feedback on this one?

Also, my thought was to forget about the macro and just use the function. Is
there a way to keep the date from recalculating? No macro, just a function.


--

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:john@xxxxxxxxxxx



.

Relevant Pages

  • Re: required fields question
    ... are telling me that the macro "Sumbit" has not been executed. ... i.e Validation text box appears, click OK and you are returned ... > Instead of setting the Required property of the field to Yes, ... > Steve Schapel, Microsoft Access MVP ...
    (microsoft.public.access.gettingstarted)
  • Re: Disable Action Failed Macro Message
    ... The error is not caused by the macro, ... I assume that you're using either table-level validation rules and/or ... because it's obvious that you cannot save a record with invalid data. ...
    (microsoft.public.access.macros)
  • Re: Form Fields
    ... ALL FF's need the OnEntry ... > but only those you want validated need the OnExit macro. ... > need is separate validation function. ... > Private Sub Document_New ...
    (microsoft.public.word.vba.general)
  • Re: Form Fields
    ... ALL FF's need the OnEntry ... > but only those you want validated need the OnExit macro. ... > need is separate validation function. ... > Private Sub Document_New ...
    (microsoft.public.word.vba.general)
  • Re: Form Fields
    ... ALL FF's need the OnEntry ... > but only those you want validated need the OnExit macro. ... > need is separate validation function. ... > Private Sub Document_New ...
    (microsoft.public.word.docmanagement)