PDA

View Full Version : Solved: Inputbox method to pick a range in another workbook



Digita
07-24-2008, 11:28 PM
All,

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

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

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
07-25-2008, 03:06 AM
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



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

Digita
07-28-2008, 04:26 AM
Brilliant. Thanks Bob. Have a great day.

Regards


kp