VBA code to affect value of a named range

[Dave O]

I wrote code intended to evaluate and reassign the value of a named
range based on an "on change" worksheet event. The code changes the
named range value when run as a separate routine in a module, but
doesn't run from the worksheet trigger. Can anyone correct this code,
or tell me what I've done wrong? Thanks. Code follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Response As String
If Evaluate(Names("DatesUpdated").Value) = False Then
Application.Undo
Response = MsgBox("Have you updated the dates?", vbYesNo)
If Response = vbYes Then
Names("DatesUpdated").Value = True
End If
End If
End Sub

 

[Guest]

Do you know if the _Change event is occuring? I just went through an
exercise in Excel 2003 and found that the _Change event doesn't even fire if
the change is made in a merged cell group.

Also, I don't know how much there is to be done on this particular sheet,
but if there's much on it to be done by the user, and you do get the code
working on a Change event, they're going to see that prompt quite a bit as
long as the value remains False.

Also, once they've responded Yes to the MsgBox prompt, they'll never see the
question again - the .value will change to true and remain that way. Might
be a better place to put it, such as the workbook's _Save or _Close events?

You can check to see if the Change event is firing and calling the code by
putting breakpoints in the code, or placing a STOP statement within that code
segment - could put one within the first IF...Then block, and another just
before the End Sub statement just for testing.

 

[Dave]

Hi, JL-
Thanks for your response: here are answers to your questions:
~Do you know if the _Change event is occuring?
Yes, the change event is triggering- the code generates an error
message each time. The intent of my post was to determine if I was
using incorrect syntax, and what is the correct syntax in this context.

~they're going to see that prompt quite a bit as long as the value
remains False
That's the idea: to remind the user they need to validate/accept
certain data before making changes and before moving on in the process.

~they'll never see the question again - the .value will change to true
and remain that way
A good catch, and an important one. What I did not mention in the post
is that there is an "on close" event that changes the value to false;
that value is saved with the file and "reset" for the next user.

 

[Guest]

You may need to put some testing code in there to see what you should
actually be looking for.
Try putting code similar to this in that event code to examine the various
values/conditions of the Named Range

MsgBox "RefersToR1C1 is: " & ActiveWorkbook.Names("DatesUpdated").RefersToR1C1
MsgBox "Value is: " & ActiveWorkbook.Names("DatesUpdated").Value
MsgBox "RefersTo: " & ActiveWorkbook.Names("DatesUpdated").RefersTo

I believe you're going to see that what's returned is "=True" or "=False"
and that's what you need to test against, and how you need to set/reset the
value (using the .RefersTo or .RefersToR1C1 property), like
ActiveWorkbook.Names("DatesUpdated").RefersToR1C1 = "=False"
or
ActiveWorkbook.Names("DatesUpdated").RefersToR1C1 = "=True"

 

[Dave O]

That did it: I was looking for a binary TRUE or FALSE value instead of
the literal =TRUE or =FALSE value.

Thanks- enjoy the weekend!

 

[Guest]

Glad that helped. Figured you seeing would be better than me trying to
describe the fine difference between "=True" and boolean True value.