Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation not working (Excel 97)

    i have set up a spreadsheet with data validation which works fine until i freeze the pane then all all cells that are frozen do not work for data validation. The cells not frozen work fine.

    The Microsoft fix is to "unfreeze the panes" but this is a pain (pardon the pun) as the sheet contains many columns and it is too difficult to keep refering back.

    Does anyone have a work around for this?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    This is a known problem in Excel 97 (see this knowledge base article). A workaround might be to program the data validation in VBA using events. However, I am not sure that this will work when panes are frozen. (did not try it out)

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    You should be able to put VBA code in the worksheet change event routine to do the validation. That works with the panes frozen in XL2K. I don't have XL97 available to try it there, but I think it should work.
    Legare Coleman

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    I have both xl97 and xl2000 installed on my PC but I don't understand the problem just yet. To try out your example I ran xl97 and set a freeze pan at row 5. I then added two data validation cells, one at cell H3 (within the frozen area) and one at cell E6 (outside the frozen area). The validation I set them for worked fine. What are you doing differently than me? I have SR-2 on xl97 if that matters. When I scroll the bottom of the worksheet such that row E is gone the other data validation cell still works ok.

    Deb <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    Ok, I spoke too soon... I read the Knowlede Base article referenced by another poster and see that the problem is only if you're validating against a List. I happened to choose Whole Number and it worked fine. I then re-created the problem in xl97 as you stated and when I set the Data Valiation to select from a list, the cell in the frozen area did not generate the drop down list for the user to choose from. I tried using Custom but wasn't able to come up with the equivalent of the List. Maybe you can come up with some clever alternative to a list within the choices of the Data Validation options.

    I then tried this same thing in xl2000 and it works just fine!!! Excel 2000 allows a Data Validation of 'list' type in a frozen pane. If I can come up with some VBA that'll make it work in xl97, I'll post it. I suspect you'll to name these frozen areas, then detect the selection of that cell that has data validation (does this trigger an event like a normal cell?), then turn off the frozen pane which surrounds this cell and turn it back on when the user moves to a different cell. Yuk! What a pain!!!

    Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    Ok,I wrote VBA code in xl97 that fixes the bug but it ain't too pretty <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>.

    I set a named range to the zone that is frozen. Then I tested if the current cell is within that zone, if yes, then I unfreeze the pane (can only have one frozen pane per sheet anyway). This now causes the drop down list to appear in the data validation cell. After the user is done, they click some other cell to exit and then the code re-does the freeze pane.

    I've attached the workbook, see the code in ThisWorkbook. To improve this code, add logic to handle if user presses ENTER when done with data validation on the cell. If they end up in a cell that is still within the freeze zone, then the code doesn't re-enable the freeze area. I'll let you improve the code to handle this.

    Deb <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    Instead of relying on Excel's validation capability for (which interacts negatively with freeze panes) for those cells, why not check Target against the list contents in your own code, rather than toggling freeze panes?

    That is, in the Change event for the Sheet, check to see if Target is the cell in the frozen area that needs to be validated. If it is, loop through the list range and compare with the Target value. If a match is found, do nothing. If the loop ends without a match, show a warning.

    Not very pretty, but less ugly than toggling freeze panes (in my opinion).

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    Yes, indeed. The poster can just delete or comment out the one line of code in my workbook solution to do exactly what you suggested. I left the freeze panes in there because he wants to use them and for a large workbook, many people like it as they keep a header on the top of the sheet as they're scrolling down which makes the information in the columns easier to identify. Since this is only an xl97 problem, the code is automatically skipped for other Excel versions (at least in the way I wrote it for the sample workbook).

    Deb <img src=/S/flatcat.gif border=0 alt=flatcat width=61 height=21>

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    Deb,

    I'm not sure how your comments relate to my previous post. Anyway, to clarify my post, put the following code into the code page for the worksheet in question. You should not have to mess with unfreezing or re-freezing panes; the user can keep everything frozen as they see fit. The big con to this approach is, you are reinventing a wheel (validation) because of a somewhat specific bump in the road.

    <pre>Option Explicit

    Dim LastContents

    Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
    Select Case .Address
    Case "$A$1"
    If Not IsNumeric(.Value) Then ' Not numeric, complain
    MsgBox "Please enter a whole number from 30 to 60"
    .Value = LastContents
    Else ' Numeric, check range
    If .Value < 30 Or .Value > 60 Then
    MsgBox "Please enter a whole number from 30 to 60"
    .Value = LastContents
    End If ' .Value < 30 Or .Value > 60
    End If ' Not IsNumeric(.Value)
    .Value = Int(.Value) ' Chop off any decimal
    End Select ' Case .Address
    End With ' Target

    End Sub ' Worksheet_Change

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    LastContents = Target.Value

    End Sub ' Worksheet_SelectionChange
    </pre>


    Add other Cases for other cells on the sheet.

  10. #10
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    Yes I agree. Your solution is an acceptable workaround, but since the poster said he wanted to use freeze panes, my solution gave them what they wanted. I too used the Worksheet_Change event without having to re-invent data validation. Your code isn't exactly the same as using a List option but could be good enough for his use if he didn't want a pre-defined set of numbers which List provides (as opposed to your quasi-list of 30-60; I mean what if he only wanted numbers like 32, 39, 40, 45, 55, 57 then he'd need smarter code in the _Change event). At this point, it's up to the original requester to determine whether or not the freeze panes are important enough to keep. Especially since this is only a problem in xl97 (I verified it).

    Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    There is nothing about my offering that prevents freeze-panes from being used, which is why I offered it. Ergo, if the user says, "I want that freeze-panes thing, it makes life a lot easier..." my offering is compatible with it.

    As for supporting a list, this is also easily handled in code, and the drop-down can be handled with an ActiveX control on the sheet, directly off the Controls toolbar.

  12. #12
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    <img src=/S/flee.gif border=0 alt=flee width=25 height=25> Ok, you win, whatever. I still disagree but I'm not wasting any more time on this subject. I'm still waiting for the originator to tell us what he decided.

    Deb

  13. #13
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (Excel 97)

    Not a matter of win or lose. I only wanted to be accurate, that my offering doesn't preclude freeze-panes. Frankly, I think your offering is a much simpler work-around.

Posting Permissions

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