Solved: Inputbox method to pick a range in another workbook

[Digita]

All,

I have the following code to prompt user to select a range in a different workbook:

[VBA]Sub Test()
Dim ThisArea As Range
Again:
On Error GoTo Again
Set ThisArea = Application.InputBox(prompt:="Select a range...", _
Title:="Select a range in a different workbook", Default:=Selection.Address(0, 0), Type:=8)
If ThisArea Is Nothing Then GoTo Again:
ThisArea.Select
End Sub[/VBA]

After a range has been selected, the cellpointer jumps back to the original workbook and asks for a range to be selected again. My understanding is the problem stems from the fact that the range variable is a subset of a sheet which in turn a subset of a workbook (ie the specifics such as: workbook and sheet names are not captured in the variable). Would you have any quick fix for this please?

Thanks in advance

Regards


kp

[Bob Phillips]

You cannot just select a range in another worksheet, never mind another workbook, so your code errors and jumps back to the start (you should rethink your error handling, but that is another matter). Instead, you need to walk the hierarchy

[vba]

Dim ThisArea As Range
Again:
On Error GoTo Again
Set ThisArea = Application.InputBox(prompt:="Select a range...", _
Title:="Select a range in a different workbook", Default:=Selection.Address(0, 0), Type:=8)
If ThisArea Is Nothing Then GoTo Again:
ThisArea.Parent.Parent.Activate
ThisArea.Parent.Activate
ThisArea.Select
[/vba]

[Digita]

Brilliant. Thanks Bob. Have a great day.

Regards


kp