Macro referencing data validation doesn't work

[Samba1]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Intel

Hi - New to VBA & the forum.

When I create a macro that references a data validation cell, the code does not acknowledge the value selected from the pull down menu. But, if the value is manually typed into the data validation cell, the code works fine. Any ideas? I've had PC users try the code with no problems.

I'm using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing And _
Target = "Complete" Then Range("D" & Target.Row) = Date
End Sub[Code]

Thank you! Kevin

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Intel

Hi - New to VBA & the forum.

When I create a macro that references a data validation cell, the code does
not acknowledge the value selected from the pull down menu. But, if the value
is manually typed into the data validation cell, the code works fine. Any
ideas? I've had PC users try the code with no problems.

I'm using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing And _
Target = "Complete" Then Range("D" & Target.Row) = Date
End Sub[Code]

Thank you! Kevin[/QUOTE]
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.

 

[Samba1]

On 10/29/09 2:07 AM, in article (e-mail address removed)9absDaxw,

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.

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!

 

[Bob Greenblatt]

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.

 

[Samba1]

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.
[/QUOTE]

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.

 

[John McGhie]

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

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:[email protected]

 

[Samba1]

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!
_____________________________________________

 

[John McGhie]

Read a bit further

VBA will be back, better than ever, in Office 2010.

Cheers

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!
_____________________________________________

--

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:[email protected]