Returning a range from application.inputbox

[ker_01]

From the Excel 2003 helpfile on the Inputbox method:
********************************************
If Type is 8, InputBox returns a Range object. You must use the Set
statement to assign the result to a Range object, as shown in the following
example.

Set myRange = Application.InputBox(prompt := "Sample", type := 8)

If you don't use the Set statement, the variable is set to the value in the
range, rather than the Range object itself.
********************************************

So I create a simple test case:

Sub MyTest
Dim myRange as Range
Set myRange = Application.InputBox(prompt := "Sample", type := 8)
End Sub

When I run it and select a range it gives me a 424 'Object required' error.

I need to capture the range (then verify that the range only contains one
column, and return that column) so that I can use that column in my sub, but
I haven't been able to accurately capture the range.

Can anyone provide any pointers or hints to help me troubleshoot?

Thanks!
Keith

 

[Jim Thomlinson]

this works for me...

Sub MyTest()
Dim myRange As Range
Set myRange = Application.InputBox(prompt:="Sample", Type:=8)
MsgBox myRange.Columns(1).EntireColumn.Address
End Sub

 

[Gord Dibben]

Works for me.

Don't know why you get the error.

Sub MyTest()
Dim myRange As Range
Set myRange = Application.InputBox(prompt:="Sample", Type:=8)
MsgBox myRange.Columns.Count
If myRange.Columns.Count > 1 Then
MsgBox "too many columns"
End If
'doodah doodah
End Sub


Gord Dibben MS Excel MVP

 

[Per Jessen]

Hi

You only get the error message mentioned, if you hit cancel, select a range
and click OK, and everything is fine.

To fix it use an error handler:

Sub MyTest()
Dim myRange As Range

On Error Resume Next
Set myRange = Application.InputBox(prompt:="Sample", Type:=8)
If myRange Is Nothing Then Exit Sub
On Error GoTo 0
If myRange.Columns.Count > 1 Then
Exit Sub
End If
TargetColumn = myRange.Column
MsgBox (TargetColumn)
End Sub

Hopes this helps.
....
Per

 

[ker_01]

Thanks to all who responded- I restarted Excel, and the problem seems to have
disappeared.

Jim, thank you for the extra line of code to select the column (I was
already trying all types of syntax, unsuccessfully). I'm still having
trouble, and hope you can help me with some additional syntax.

I'll be using my code snippet to allow the user to select a column on any of
several worksheets. The same code snippet will be run several times (several
different column selections), then my main code will run against those
columns that were selected.

When I use TempRange.Columns(1).EntireColumn.Address, it gives the column
address (F:F), but seems to lose the page reference (Sheet3!F:F). I've been
playing with the line to find any way to have it return the fully qualified
address, but no luck so far. I was also trying to assign the 'modified' range
(the column) to a new range so I could store it, and was unsuccessful-
possibly for the same reason, if I'm not correctly passing a worksheet
reference along with the range.

Is there a simple way to return a fully qualified (column) range?

Many. many thanks!
Keith

 

[Jim Thomlinson]

The range object is specific to the sheet. It is the specific cells on the
specific sheet. There is really no need to specify a sheet. If you want to
know the sheet you can use...

msgbox TempRange.parent.name

If you want to select the sheet then
application.goto TempRange