InputBox object required error

**Wizza2003** · 02-01-2005, 09:51 PM

I have run into a problem with a VBA macro using 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??

**anilsolipuram** · 02-01-2005, 10:52 PM

let me know what message is popup each time

Sub InputBoxTest()
Dim MySelection As Range
Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8)
msgbox MySelection.address
MySelection.Select
End Sub

**Wizza2003** · 02-01-2005, 11:13 PM

The message box shows the selected address but only when the macro runs correctly.
If the inputbox goes to error the message box does not execute.

did you try reproducing problem on your machine?

**anilsolipuram** · 02-01-2005, 11:16 PM

Strange thing I am not getting that error . I am using windows 98/NT/2000

try using this macro

Sub InputBoxTest()

MySelection = Application.InputBox(prompt:="Select a range of cells")
range(MySelection).select
End Sub

**Wizza2003** · 02-01-2005, 11:41 PM

tried this but macro fails on "Range(MySelection).Select" as "myselection" is not set by inputbox.

When you say it works fine for you have you added conditional formatting to worksheet cell B1 as per description of problem? Also the conditional formatiing must contain a function e.g. "cell value is less than =today()-6"