Range Selection via InputBox: Error 424

[wizza2003]

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

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??

[D_Wijngaards]

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:

'

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 

[D_Wijngaards]

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

[D_Wijngaards]

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

[royUK]

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

[Twohawks]

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

[Twohawks]

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

[iheartsublime8]

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!

[AAE]

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.