Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Error Handling for Data Validation List

This is a discussion on Error Handling for Data Validation List within the Excel Questions forums, part of the Question Forums category; Help me, good people! I have a excel document that is very heavy with Data Validation List Boxes. Below is ...

  1. #1
    New Member
    Join Date
    Jun 2009
    Posts
    14

    Question Error Handling for Data Validation List

    Help me, good people!

    I have a excel document that is very heavy with Data Validation List Boxes. Below is just one example. Error handling is what I am trying to figure out.
    Description:
    I have 3 List Boxes.
    One list box data is based on data selected in the previous list box.

    1st List Box uses this formula: =Series
    2nd List Box uses this formula: =INDIRECT($C$2)
    3rd List Box uses this formula: =INDIRECT($C$3)

    It all works! HOWEVER....
    If user decides to go back and change selection in first or second list box, and forgets to change the data in the second or third box, then data will be incorrect. How do I solve this problem?
    Is there anyway to write something so it would give them an error message and instructing them to correct the data?

    Thanks

  2. #2
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,833

    Default Re: Error Handling for Data Validation List

    I use worksheet_change macros to force changes. Basically, every time C2 is changed, the dependent boxes clear. If C3 changes, C4 is cleared. Right-click on the sheet tab, choose VIEW CODE and insert this:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        If Not Intersect(Target, Range("C2")) Is Nothing Then
            Application.EnableEvents = False
            Range("C3:C4").ClearContents
            Application.EnableEvents = True
        End If
        
        If Not Intersect(Target, Range("C3")) Is Nothing Then
            Application.EnableEvents = False
            Range("C4").ClearContents
            Application.EnableEvents = True
        End If
        
    End Sub
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  3. #3
    New Member
    Join Date
    Jun 2009
    Posts
    14

    Default Re: Error Handling for Data Validation List

    That would be awesome! However, please help me troubleshoot this error.
    The cells that handle the validation are merged cells. Therefore when I change the data, I get this error:
    Run-time error '1004':
    Cannot change part of merged cell.

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Error Handling for Data Validation List

    Quote Originally Posted by mikeyank View Post
    That would be awesome! However, please help me troubleshoot this error.
    The cells that handle the validation are merged cells. Therefore when I change the data, I get this error:
    Run-time error '1004':
    Cannot change part of merged cell.
    VBA hates merged cells. Try using "Center Across Selection" instead.

    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  5. #5
    New Member
    Join Date
    Jun 2009
    Posts
    14

    Default Re: Error Handling for Data Validation List

    I am not sure if "Center Across Selection" is available in Excel 2002.
    And, I really need them merged for the rest of the form.

  6. #6
    New Member
    Join Date
    Jun 2009
    Posts
    14

    Default Re: Error Handling for Data Validation List

    Another strange thing...
    After I got this error, it does not come up anymore, BUT the code still does not work. NOTHING HAPPENS!
    CONFUSED!

  7. #7
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,833

    Default Re: Error Handling for Data Validation List

    Sorry, that's because the macro disabledevents...then errored out. You need to
    go into the VBEditor, press Crtrl-G to get to the Immediate window, and put in this command to turn it back on:
    Code:
    Application.EnableEvents = True
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  8. #8
    New Member
    Join Date
    Jun 2009
    Posts
    14

    Default Re: Error Handling for Data Validation List

    Oh, great! That worked!
    Do you have any idea how to make it work with mergd cells now!

    BTW, your site is great! Makes me wanna get into your hubby!

  9. #9
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,833

    Default Re: Error Handling for Data Validation List

    Here's a corrected macro that will fix itself after an error:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler
    
        If Not Intersect(Target, Range("C2")) Is Nothing Then
            Application.EnableEvents = False
                Range("C3:C4").ClearContents
        End If
        
        If Not Intersect(Target, Range("C3")) Is Nothing Then
            Application.EnableEvents = False
                Range("C4").ClearContents
        End If
    
    Reset_all:
       Application.ScreenUpdating = True
       Application.EnableEvents = True
       Exit Sub
    
    ErrorHandler:
        MsgBox Err.Number & " - " & Erl & " - " & Err.Description
        Resume Reset_all
    End Sub
    To make it work on merged cells, unmerge them and fall out of love with that feature.
    Last edited by jbeaucaire; Jun 19th, 2009 at 03:35 PM.
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  10. #10
    New Member
    Join Date
    Jun 2009
    Posts
    14

    Default Re: Error Handling for Data Validation List

    Quote Originally Posted by jbeaucaire View Post
    ...To make it work on merged cells, unmerge them and fall out of love with that feature.
    I wish I could . However. it throws all my formatting off.
    I have drop downs all of different sizes. Some rows have 2 long ones.
    Some have 5 short ones.
    Worst of all it was provided by client of ours, and I have to make it work.

Page 1 of 3 123 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com