Results 1 to 15 of 15
  1. #1
    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

    Type 8 InputBox Error 424 (All?)

    The DailyExcelTips Message board got a question related to receiving intermittent errors ("Run time error 824: Object required") when using conditional formatting.

    After some investigation, I discovered that the problem seems to be when the conditional formatting contains a lookup (Vlookup, Hlookup, Match) AND at least one of the columns are hidden.

    I recommended a workaround by not placing the lookup in the cond formatting, but calculating it in an intermediate column and then have the cond format use the intermediate column.

    [see the dialogs at Thread1 and Thread2 if interested.]

    I have attached an example book with 3 sheets that demos the problem. Cell A1 has conditional formatting in all 3 sheets. It is identical in Sheet1 ("Error-Match-Hidden") ands sheet2 ("No Error-Match")
    <pre>=MATCH(A1,MyList,0)</pre>


    Sheet 1 has col D hidden. When the button is pressed, the code asks for a range to be selected and to display the address.
    <pre>Option Explicit
    Sub Test()
    Dim rng As Range
    Set rng = Application.InputBox(Prompt:="Select Range", Type:=8)
    MsgBox rng.Address & " Selected"
    End Sub</pre>


    With sheet1 you get a "424 error" without the hidden column, there is no error.

    In Sheet3 ("No Error Hidden) is the workaround. In Cell B1 is the lookup:
    <pre>=MATCH(A1,MyList,0)</pre>


    And the conditional formatting in A1 then uses this cell:
    <pre>=B1</pre>


    instead of using the Vlookup Formula directly. This works even with a hidden column.

    Is this a known "bug" in excel? Does anyone understand some logic of Why this should even pose a problem to the program...]

    Steve

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

    Re: Type 8 InputBox Error 424 (All?)

    I can't seem to raise an error in Excel 2002 SP-3. When exactly am I supposed to get an error?

  3. #3
    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: Type 8 InputBox Error 424 (All?)

    Press "press me" while "Error-Match-Hidden" is active

    you will be prompted to select a range.
    Choose a range and press <ok>

    The other sheets yield no error. the error is in the "set rng" line.

    [I get the error in XL2002 SP3 with XP Pro]

    Steve

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

    Re: Type 8 InputBox Error 424 (All?)

    I only get an error message when I press Cancel, which seems reasonable.

  5. #5
    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: Type 8 InputBox Error 424 (All?)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> yes I expect the error with cancel.

    On my system, I get that identical error I would with "Cancel" by selecting a range on this sheet (with match in cond formatting and a hidden column) and it was similar to what the original poster had seen.

    There must be some subtlety with how the systems are setup...

    Thanks for checking it out. I don't plan on spending too much more time on it, since as I mentioned, I found a "relatively simple" workaround. I guess I will have to chalk it up to being in "The Twilight Zone" (Doo-doo-DOO-doo! Doo-doo-DOO-doo!) as I still get the error with the file I posted, even after a reboot).

    Steve

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

    Re: Type 8 InputBox Error 424 (All?)

    Uhm, I can reproduce the error as follows:

    1) If I move the mouse very wildly when selecting the range. The input box displays the selected range correctly, but apparently Excel has lost track.

    2) If I draw a rectangle from lower right to upper left, with the end point in the range A1:C4. The error doesn't occur if I draw the same rectangle from upper left to lower right.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Type 8 InputBox Error 424 (All?)

    No error with XL 2003 (11.6355.6360) SP1
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  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: Type 8 InputBox Error 424 (All?)

    I get the error even without using the mouse...

    If I assign ctrl-t to run the macro and enter
    ctlr-t
    Then enter in the dialog box
    A1<enter>

    I get the same error.

    For some reason, on this system, with a lookup in the cond formatting and a hidden column, the inputbox is not "transferred" and it is like pressing <Cancel>

    Steve

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

    Re: Type 8 InputBox Error 424 (All?)

    Strange, I cannot reproduce the error at all using the keyboard. I wonder what the difference between our systems is.

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

    Re: Type 8 InputBox Error 424 (All?)

    Welcome to Woody's Lounge. As I noted higher up in this thread, I'm using Excel 2002 SP-3.

    In the workbook you attached, I get errors when I select A30:A100 in the inputbox on the first and second worksheet, but I don't get an error on the third worksheet whatever I select.

    So the workaround used in the third sheet seems to work under all circumstances.

  11. #11
    New Lounger
    Join Date
    Nov 2005
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Type 8 InputBox Error 424 (All?)

    Steve,

    I downloaded your example workbook from www.wopr.com and I now see why you are not getting the same error 424 without any hidden columns. You only had one cell with conditional formatting per worksheet. I usually have hundreds. But error 424 appears when only 10 cells contain conditional formatting. On my system, I can not reproduce error 424 without using the mouse unless I scrool using the shift key and arrow. I have to scrool with either the mouse or the shift key and arrow. to get error 424. If I just type in A30:A100 and the Enter key, without any scrooling, I will not get an error.

    For hidden columns, I tried several times on you third worksheet and got no error messages on my system XL2K , even when moving the mouse wildly or selecting a hundred thousand plus cells.

    Do this:
    Copy A1 down through A10 on all three of your worksheets. Then, when prompted, select Range A30:A100 and on the first two sheets you should get error 424. I just did this as described here and got the results I described here.

    P.S. I thank Steve for encouraging me to join Woody's Lounge by speaking so highly of it so often in the past months. Steve is my mentor so if I understand VBE, Steve is a great teacher. Otherwise ... <grin>.

    John Gregory

  12. #12
    New Lounger
    Join Date
    Nov 2005
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Type 8 InputBox Error 424 (All?)

    Hans,

    The main concept I am testing is that using either the Match function or the Vlookup function for Conditional Formatting and using the InputBox method on the same worksheet to set a range will return Error 424 which means the range is not set or there is no object, that is, if you scrool when setting the range, but if you do not scrool, you can set the range. So, the overriding concept described holds so far. I will simply avoid using those two function in conditional formatting.

    The third worksheet was intended to test the effect of hidden columns, not the Match Function, when using the InputBox method to set a range, and this third worksheet does not contain the Match function whereas the first two worksheets do contain the Match Function. On my three systems, XL2K, XL97, and XL2002 SP3, the hidden columns make no difference.

    Thanks for your input.

    John Gregory

  13. #13
    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: Type 8 InputBox Error 424 (All?)

    For me:
    XL2002 SP3 Win XP Pro
    My file with just cond formatting in A1:
    Hidden column and match in cond formatting yields an error
    Match in cond formatting gave no error
    Hidden column sheet gave no error

    John's file with cond formatting in A1:A10:
    Hidden column and match in cond formatting yields an error
    Match in cond formatting yields an error
    Hidden column sheet gave no error

    XL97 Win XP Home
    Both My file with just cond formatting in A1 and John's file with cond formatting in A1:A10:
    Hidden column and match in cond formatting gave no error
    Match in cond formatting gave no error
    Hidden column sheet gave no error

    I am a little <img src=/S/confused.gif border=0 alt=confused width=15 height=20> about the "logic" (as mentioned earlier) of why any get errors, and am a very <img src=/S/confused.gif border=0 alt=confused width=15 height=20> as to why the differences in different systems with the same XL version...

    The workaround is "relatively simple" and seems to solve it on all systems, and that is still my recommendation.

    Steve

  14. #14
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Type 8 InputBox Error 424 (All?)

    When I copy A1 down to A10, I get the error on the first two sheets, but not on the third. I also copied down to A250 & selected A260:A300 with no error on the third sheet. I'm running 2003 XP1 on XP/Pro SP2.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Type 8 InputBox Error 424 (All?)

    Doesn't fail on my XL2000. But I have multiple Office vesions on the same system, so I guess that doesn't really count.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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