Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cascading data validation issue (2007)

    Attached is a spreadsheet that is nearly done except that I have one small annoyance. Please look at the attached SS which is used to manage space (room) requests in a facility. It uses cascading data validation such that the values present in column D are dependent on the value selected in column B. Problem is, if I change a value in column B, then columns E, F, & G show a lookup error until I make the appropriate selection in column D.

    What I would like to happen is that when column B is changed, for columns C - I to blank until column D is Selected. Any ideas on how to make this happen?
    Attached Files Attached Files

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

    Re: Cascading data validation issue (2007)

    If it is acceptable to use VBA, you could create a Worksheet_Change event procedure in the worksheet module for the Rooms sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Range("B2:B16"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("B2:B16"), Target).Cells
    oCell.Offset(0, 2).ClearContents
    Next oCell
    End If
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading data validation issue (2007)

    VBA is fine although I wonder if you could take a moment and explain your steps. Also, I copied and pasted it in the VBA editor as a module and it does not work. Should I have entered the code in another fashion?

    Do you know how to enable VBA & macros in Excel 2007?

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

    Re: Cascading data validation issue (2007)

    The code should be in the worksheet module, not in a standard module. Right-click the sheet tab and select View Code to open the sheet tab.

    You must save the workbook as a macro-enabled workbook (.xlsm) to enable saving the VBA code.

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

    Re: Cascading data validation issue (2007)

    The Worksheet_Change event procedure has an argument Target of type Range. It is the collection of cells that has been changed by the user.
    The code checks whether the target intersects the range B2:B16, i.e. whether any of the cells in the range B2:B16 has changed.
    If so, the code loops through the changed cells in B2:B16, and clears the corresponding cell two columns to the right, i.e. in column D.

    See the attached workbook, with extension renamed from .xlsm to .zip. Download, then change the extension to .xlsm again.
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading data validation issue (2007)

    Thank you for the explanation. It is embarrassing to admit that I do understand your explanation in principle, but not the specifics. Most of the time, when you offer code, i try to research and understand it. This time, I cannot. Attached is the methodology I use to study your code and it contains my understanding by line. I don't know how to insert an image AND attach a file to this post so I will attach the file then post again with my understanding. I would appreciate any guidance you can provide.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading data validation issue (2007)

    Okay, saving it as XLSM worked. However, if I want to share this with users who are using Excel 97, 2000, XP, & 2003, how can I do that. I cannot reasonably expect them to have converters and I seem unable to save the workbook in a previous version without losing the VBA code. Is there a solution?

  8. #8
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading data validation issue (2007)

    Picture shrunk by HansV - PLEASE don't post such large images (max 640x480)

    Here is the snapshot of my table of understanding. I think my confusion begins with line 2.
    Attached Images Attached Images
    • File Type: gif x.gif (31.3 KB, 0 views)

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

    Re: Cascading data validation issue (2007)

    I would have thought that saving an Excel 2007 macro-enabled workbook (.xlsm) in Excel 97-2003 format (.xls) would preserve the VBA code, but I don't have Excel 2007, so I cannot test that. Hopefully someone who has Excel 2007 can help.

  10. #10
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cascading data validation issue (2007)

    Sorry, let me correct myself. It does, in fact, save correctly to an XLS file. However, during the same Process I receive an error message that is unwarranted. The error is that it calims the spreadsheet exceeds the old format and some data may be lost. However, my spreadsheet does not contain any data beyond the old limits. See the error message below.
    Attached Images Attached Images

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

    Re: Cascading data validation issue (2007)

    The Worksheet_Change procedure is not just a macro (in fact, it is not a macro in the technical sense at all, a macro is a procedure in a standard module without arguments). It is the event procedure for the Change event of the Worksheet object. Its declaration line

    Private Sub Worksheet_Change(ByVal Target As Range)

    is fixed: both the name and the argument *must* be exactly like that. You don't call or run this procedure yourself. Excel will run it automatically each time the user changes the value of one or more cells; the event procedure allows you as programmer to react to this change.

    Look up the Worksheet object in the Excel VBA help and click Events - you'll see a list of events associated with the Worksheet object to which you can react by creating an event procedure. This help is also available on MSDN: Worksheet Object Events [Excel 2007 Developer Reference].

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

    Re: Cascading data validation issue (2007)

    When I open your workbook in Excel 2002, the defined names fruit, produce and veggie are listed as referring to #REF, perhaps that causes the error message. What happens if you click Find?

    You can probably ignore this error by clicking Continue.

Posting Permissions

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