Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code to affect value of a named range
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code to affect value of a named range
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 O" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code to affect value of a named range
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code to affect value of a named range
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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code to affect value of a named range
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code to affect value of a named range
Glad that helped. Figured you seeing would be better than me trying to
describe the fine difference between "=True" and boolean True value. "Dave O" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't chart dynamic named range?? | Charts and Charting in Excel | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Need formula to lookup a named range | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions |