Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation Weirdness (Excel 2003/SP2)

    Most of the data entry fields on the first sheet in my workbook template have "text length" data validation. It works fine. However, if the user gets an error message while editing a cell that provides data to a linked cell on another sheet, when he hits "Retry," he is flipped to that other sheet.

    For example, the user will enter project name information. This data is carried over to both Sheets 2 and 3. If the user enters more than the maximum number of characters allowed for project name, they get the appropriate error. But clicking Retry moves them to Sheet 2 instead of back to the cell they need to edit. Besides writing all my own validation routines, is there a way to circumvent this behavior? (All three sheets are protected, by the way, and the linked cells in question on Sheets 2 and 3 are locked.)

    Many thanks!

    --Karyl

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Validation Weirdness (Excel 2003/SP2)

    Could you provide an example workbook which demonstrates the problem?

    I don't see why using datavalidation would move you to another sheet unless you already have some code in the workbook.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Weirdness (Excel 2003/SP2)

    Well, apparently not. I created the sample, but it did not exhibit the same behavior. So I went back to my sheet, and it no longer exhibits the same behavior, either. And least you think I'm nuts, this happened to us on two different copies of the document and on two different computers. So I don't know what's up. But if it happens again, I'll look to my code to see if I can figure it out. I know I have events firing for the worksheet_change event on each sheet, and the selection_change on some of them. So there are many possibilities. Thanks for taking the time to respond.

    --Karyl

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Validation Weirdness (Excel 2003/SP2)

    I would guess that the events are the "problem" and not the validation. If you have events triggering anyway, why not validate in that code instead of using both the validaition and event code?

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Weirdness (Excel 2003/SP2)

    I may have to do it that way. But the form was supposed to be released today (now rescheduled for Friday), and I'm still working out some other problems, so I was hoping to avoid spending the time. There are nearly 50 named fields on the form that have some type of validation, in addition to all the change events already coded. But if I can't figure out what's happening, I may have to. It started flipping to the wrong sheet again after I wrote last, but it is not consistent. I'm going to put break points in all of my events and see if I can figure out what event is being triggered by what actions. All of my code disables events while the current event is running, so nothing should be running unexpectedly. But something is up! If it turns out to be something interesting, I'll write and let you know.

    --Karyl

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Validation Weirdness (Excel 2003/SP2)

    Your code might disable the events, but the validation is not capable of doing this.

    Your problem may be due to a "quirk" in how validation triggers events.

    If a cell with datavalidation is changed it logs (but does not triggers an event). If you cancel /retry multiple time, each of these events get "logged" (but not triggered). After the cell entry is finally "completed" (with all the changes/ manipulation done during the validation) the logged events are each triggered in turn.

    This means that 1 changed cell when a user had "problems" entering a new value, even though the cell is just changed once (by the end), it has triggered mutlitple events. I don't know hat the limit to the number of logged events are.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Weirdness (Excel 2003/SP2)

    That's just the kind of thing I don't work in Excel often enough to know and accounts for a lot of what has been driving me crazy the last two weeks. Thanks for explaining.

    So, if I do move my validation to the change event handlers, is there a recommended best practice for encapsulated the code in a way that makes it act like Excel and not let the user continue without correcting the error? Right now, the validation I have moved follows this pattern:

    <pre>'Check Percentages
    If Not Application.Intersect(Target, Range("InfoOfficePercent")) Is Nothing Then
    If Range("SumOfficePercent").Value > 1 Then
    MsgBox "Percentages cannot total more than 100%.", vbOKOnly, "Allocation Error"
    Target.Select
    End If
    End If</pre>


    It puts the user back in the cell to make a correction, but it doesn't prevent them from moving forward if they choose. I'm thinking of somehow combining the selection_change event with this one to confirm the accuracy of the value entered, but if there is a standard methodology established which you can share, I won't have to spend my time trying to develop one (and can concentrate on moving all my validation instead).

    Also, I was going to move my data validation to code, but using IsDateŠ wasn't working the way I thought it should (and forgive me, but it was last week and I can't remember exactly what it was doing--it was correct, just not what I intended). Is there a code snippet somewhere that does date validation? I just need to check if it is a valid date and probably one that falls within a specified ten-year range.

    Many thanks! I'm so rummy from lack of sleep and undiverted attention to this one project, stuff I could have easily figured out a week ago is confounding me.

    --Karyl

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Validation Weirdness (Excel 2003/SP2)

    The easiest may be to not let people populate the cells directly.

    Create a userform for data entry/editing which populates the required cells. Before the worksheet is updated, you can validate all the entries...

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Weirdness (Excel 2003/SP2)

    That's usually my approach. But for this project, it just isn't an option. If I have time, I'll probably go back and change the validation to be code-based. For now, since the problem is intermittent, we've decided to release it as is and see what happens.

    Another quick question:

    Why does this fail with an error (1004: Unable to set locked property of Range class):

    Range("MyName").Locked = True

    while this works:

    Range("MyName").Select
    Selection.Locked = True

    Qualifying the Range with the worksheet name doesn't help.

    If I do have to select first, is there a way to turn off the screen display, like there is in Word, so the user doesn't see the cursor bopping around?

    --Karyl

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation Weirdness (Excel 2003/SP2)

    Your workbook appears to behave very weirdly. You should be able to lock a range directly instead of selecting it first.

    You can insert

    Application.ScreenUpdating = False

    at the beginning, and

    Application.ScreenUpdating = True

    at the end of a macro.

  11. #11
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Weirdness (Excel 2003/SP2)

    I'm glad to hear someone else say so. I've been saying it all week! There have been all manner of strange things going on, which I'm sure are caused by some combination of things I'm trying to do. But I haven't been able to track it down. I just keep murmuring under my breath, "Some day they'll let me do these forms in InfoPath. Some day I'll be able to use .NET events with my named ranges. Some day Excel won't drive me crazy any more. . ."

    I'm not sure it will ever happen, or be any better when it does, but it keeps me plugging away!

    --Karyl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •