Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Range Selection via InputBox: Error 424

  1. #1
    Join Date
    3rd February 2005
    Posts
    1

    Range Selection via InputBox: Error 424

    (previously posted in http://www.excelforum.com/)

    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).
    VB:
    Sub InputBoxTest() 
        Dim MySelection As Range 
        Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8) 
        MySelection.Select 
    End Sub 
    
    
    THEN

    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.
    e.g.
    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 at 19:20. Reason: add code tags, please use when posting codes

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th February 2006
    Posts
    3

    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).

    Regards,

    Davey Wijngaards

    Subs and Functions Used:

    '
    VB:
    Sub InputboxDemo() 
        Dim myRange As Range 
        On Error Resume Next 
         
        Set myRange = Application.InputBox(Prompt:="Please select range", _ 
        Title:="Mahipalreddy.com-VBAhelp", 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 
        Do 
            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" 
        Else 
            Rng.Select 
        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." 
        Else 
            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 at 18:39. Reason: add code tags, please use when posting codes

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th February 2006
    Posts
    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.

    Cheers,

    Davey

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th February 2006
    Posts
    3

    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!

    Davey

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,508

    Re: Range selection via InputBox fails - error 424

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

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  6. #6
    Join Date
    25th January 2007
    Posts
    2

    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...
    ..anymonday...
    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,
    TwoHawks

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th January 2007
    Posts
    2

    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.
    Cheers,
    TwoHawks

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    20th July 2010
    Posts
    2

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Range Selection via InputBox: Error 424

    iheartsublime8,

    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.
    AAE
    ----------------------------------------------------

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. InputBox Type 8 (Range Selection)
    By DROM in forum EXCEL HELP
    Replies: 7
    Last Post: October 25th, 2005, 09:11
  2. Inputbox Error when user clicks on cancel Button
    By creativeart2001 in forum EXCEL HELP
    Replies: 4
    Last Post: August 5th, 2005, 07:51
  3. InputBox to reference a range
    By Muskyking in forum EXCEL HELP
    Replies: 8
    Last Post: May 21st, 2005, 03:23
  4. Range Inputbox Address
    By xlite in forum EXCEL HELP
    Replies: 5
    Last Post: January 3rd, 2005, 08:36
  5. Input via XL InputBox method and error box
    By Malciberg in forum EXCEL HELP
    Replies: 8
    Last Post: October 28th, 2004, 18:53

Bookmarks

Posting Permissions

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