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