No announcement yet.

Range Selection via InputBox: Error 424

  • Filter
  • Time
  • Show
Clear All
new posts

  • Range Selection via InputBox: Error 424

    (previously posted in

    I have run into a problem with a VBA macro using and Inputbox to select a range of cells (using the mouse).
    Under certain circumstances inputbox returns and error. "run time error 424 object required".
    So far I have only been able relate this to conditional formatting in cells near the range selected but cannot understandwhy this is so.

    To reproduce the problem use this macro .(macro simply uses Inputbox to select the selected cells).
    Sub InputBoxTest()
    Dim MySelection As Range
    Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8)
    End Sub

    Enter a date value into cell B2 of a new worksheet.
    Apply conditional formatting to the cell as follows.
    Cell value is less than =TODAY()-6
    Now run the macro and use the MOUSE to select a range of cells
    Depending upon what cells are selected or the way they are selected the macro either works or fails with object required error.
    Run the macro and use mouse to select cell A1 - everything works fine - cell A1 is selected.
    Run again and use mouse to select A1:B2 - object required error.
    Run again and select the same range A1:B2 BUT select from B2 to A1 instead and no error- range A1:B2 is selected!!!
    Various other combinations (not necessarily including the formatted cell) do and dont work.
    (typing the range reference in the input box works fine)

    The problem goes away if I remove the function used in conditional formatting from cell B2
    i.e change "Cell value is less than =TODAY()-6" to "Cell value is less than 6"
    This is strange behaviour.

    I have tried running the macro on a number of different machines and versions of Excel (97, 2000 & 2003) with the same results

    Any clues??
    Last edited by Dave Hawley; May 1st, 2007, 18:20. Reason: add code tags, please use when posting codes

  • #2
    Re: Range selection via InputBox fails - error 424 - same here

    I've been having the same problems. I tried all of the codes below. The first time, the returned range from the inputbox is "Nothing", whether I do select or do not select a range, and whether I press OK or cancel afterwards.

    The weird thing is, The subs and functions do work the second time I run them. Please provide more insight into the problem (Use Excel 2002 SP3).


    Davey Wijngaards

    Subs and Functions Used:

    Sub InputboxDemo()
        Dim myRange As Range
        On Error Resume Next
        Set myRange = Application.InputBox(Prompt:="Please select range", _
            Title:="", Type:=8)
        If (myRange Is Nothing) = False Then
        For Each rngCell In myRange.Cells
            If IsEmpty(rngCell.Value) = False Then
               MsgBox rngCell.Value
            End If
        Next rngCell
        End If
    End Sub
    'This sub calls the function directly below the sub
    Sub foo()
        Dim Rng As Range
        Set Rng = InputRange(Prompt:="Select a range", Title:="foo", Force:=True)
        If Not Rng Is Nothing Then Rng.EntireRow.Select
    End Sub
    Function InputRange(Optional Prompt As String = "", Optional Title As String = "", _
        Optional Force As Boolean = False) As Range
        Dim retry As Boolean
        On Error Resume Next
            Set InputRange = Application.InputBox(Prompt:=Prompt, Title:=Title, Type:=8)
            If InputRange Is Nothing And Force And Not retry Then
                retry = True
                Prompt = "YOU MUST SELECT A RANGE!" & Chr(13) & Prompt
            End If
        Loop While InputRange Is Nothing And Force
        On Error GoTo 0
    End Function
    Sub GetRange()
        Dim Rng As Range
        On Error Resume Next
        Set Rng = Range("A1")
        Set Rng = Application.InputBox(Prompt:="Enter range", Type:=8)
        If Rng Is Nothing Then
            MsgBox "Operation Cancelled"
        End If
    End Sub
    Sub GetUserRange()
        Dim UserRange As Range
        Output = 565
        Prompt = "Select a cell for the output."
        Title = "Select a cell"
    '   Display the Input Box
        On Error Resume Next
        Set UserRange = Application.InputBox( _
            Prompt:=Prompt, _
            Title:=Title, _
            Default:=ActiveCell.Address, _
            Type:=8) 'Range selection
    '   Was the Input Box canceled?
        If UserRange Is Nothing Then
            MsgBox "Canceled."
            UserRange.Range("A1") = Output
        End If
    End Sub
    Sub GetAdres()
        Dim Last As Range, mes As String
        mes = "Select any cell in one of the first 10 rows"
        On Error Resume Next
        Set Last = Application.InputBox(Prompt:=mes, Type:=8)
        If Last Is Nothing Then Exit Sub
        MsgBox Last.Address
    End Sub
    Last edited by royUK; February 15th, 2006, 18:39. Reason: add code tags, please use when posting codes


    • #3
      Re: Range selection via InputBox fails - error 424

      I got some hint: The "InputBox Type 8 issue" generating errors was gone after I removed conditional formatting elsewhere on the sheet. Now that conditional formatting is gone, the inputbox method works fine.




      • #4
        Re: Range selection via InputBox fails - error 424

        As another feat of removing the conditional formatting, the Module runs about 10 times faster than with the conditional formatting in the sheet. Go figure!



        • #5
          Re: Range selection via InputBox fails - error 424

          can you both remember to use Code tags when posting code. Thanks
          Hope that Helps


          New users should read the Forum Rules before posting

          For free Excel tools & articles visit my web site

          RoyUK's Web Site

          royUK's Database Form

          Where to paste code from the Forum

          About me.


          • #6
            Re: Range Selection via InputBox: Error 424

            Hi Wizza,
            I wish you had posted alink to where this was asked in excel Forum, as you mentioned...
            I found this thread while searching on this problem myself.
            Thank you very much to all who have posted, here and elsewhere.
            I think I discovered the answer, so I wish to share....

            It seems that if I am debugging from within VBA I have the same inconsistent results; however, if I test the (same type of) code initiating it from within Excel it works everytime. I suspect this behavior is due to the nature of the Inputbox Method being application specific, i.e., it seems that when you initiate the code from another app, in this case VBA, when selecting the Range input from Excel it does not actually pick up anything, thus the Nothing or Empty results. I think it must be something to do with the Application focus handling in Windows.

            Anyway, I was so glad to figure out that this code actually works (even in Excel97/8). I hope this helps someone else.

            Wizza, if this helps you, would you please share this info in the post you made reference to at excelforums... this was a hard thing to find, and I am sure others will be searching.

            Cheers All,


            • #7
              Re: Range Selection via InputBox: Error 424

              Hello again... I was unable to edit my post, so here's my followup...

              Although my assertion in my last post is essentially accurate, and thus important to note, I find now that it is definitely not all encompassing.

              i.e, I am still observing intermittent error with this method.
              It seems that somehow the information (object value) being entered in the inputbox when it is via 'range-selecting' can end up not being returned.

              It seems if I enter the selection directly from the keyboard I am observing success: but I have not fully tested, so the jury is still out.

              It is a shame this is so flakey. I hope someone comes along with a magic bullet on this one.


              • #8
                Re: Range Selection via InputBox: Error 424

                I am not using ranges, but rather inputing a Row Value, a simple numeric. From the InputBox entry I would then convert (CInt) the strInputEntry to iInputEntry and copy a row (several columns, static length) but I am getting Error 424: Object Required. I would also like to use the 'Left' and 'Top' optional parameters for InputBox but do not understand why the InputBox can't take my numerical input as a string for me. It works elsewhere in my code...
                Where it works:
                Dim strAvgRow As String
                Dim AvgRow As Integer
                strAvgRow = InputBox("Select Steady State (linear) region of chart by entering _
                a single Row Value in Cell A1. See 1st Row for Average Updates", "Select a Row")
                ...series of If Then Statements to check for valid entry
                AvgRow = CInt(strAvgRow)

                Code that generates Object Required Error (424):
                Dim strReducedFile As String
                strReducedFile = InputBox(prompt:="Name File based on: Angle T RPM m_dot ", _
                Title:="File Save As", Default:=RawDataFile.Name _
                Left:=1440, Top:=2880)

                I searched online that the 'twip' units of VBA InputBox placement (Left/xpos and Top/ypos) are about 1440 twips per inch, however, I haven't been able to see the placement of the InputBox work after I enter a numerical (row) value. Thanks in advance!


                • #9

                  Re: Range Selection via InputBox: Error 424


                  Weclome to Ozgrid.

                  Please do not resurrect old threads. Further, do not post your question in the thread someone else has started - this is known as thread hijacking and is against the Forum Rules.

                  Always start your own thread and, if it helps to clarify your needs, provide a link back to other threads.
                  Additionally, any time VBA code is posted it must be wrapped in code tags, which you omitted to use.

                  I suggest you take the time to read the Forum Rules (see link in my signature) so that you can avoid infractions that will impact your posting privilege.

                  Forum Rules | Message to Cross Posters | How to use Tags