InputBox that obtains Workbook, Worksheet, and Range

[Guest]

I have a macro that takes a range of cells as input. I am trying to use an
InputBox or something similiar to let the user select the range they need.
First how would I let the user select their range in the format :
'[Book1.xls]Sheet2'!$F$6:$F$10, and second how would I transform that into a
variable that's similiar to Workbooks().WorkSheets().Range()?

 

[Norman Jones]

Hi J,

Try something like:

'=============>>
Public Sub Tester()
Dim Rng As Range
Dim sStr As String

On Error Resume Next
Set Rng = Application.InputBox( _
Prompt:="Select range", _
Type:=8)
On Error GoTo 0

If Not Rng Is Nothing Then
sStr = Rng.Address(external:=True)
MsgBox sStr
End If

End Sub
'<<=============

 

[Guest]

The .address function returns the full path in a string, but how would I use
that string as a range variable that incorporates the Workbook and Worksheet?

Also, the prompt box doesnt allow me to select a different workbook while
its open. Is there a way around it?

Thanks.

Hi J,

Try something like:

'=============>>
Public Sub Tester()
Dim Rng As Range
Dim sStr As String

On Error Resume Next
Set Rng = Application.InputBox( _
Prompt:="Select range", _
Type:=8)
On Error GoTo 0

If Not Rng Is Nothing Then
sStr = Rng.Address(external:=True)
MsgBox sStr
End If

End Sub
'<<=============


---
Regards,
Norman

I have a macro that takes a range of cells as input. I am trying to use an
InputBox or something similiar to let the user select the range they need.
First how would I let the user select their range in the format :
'[Book1.xls]Sheet2'!$F$6:$F$10, and second how would I transform that into
a
variable that's similiar to Workbooks().WorkSheets().Range()?

 

[Norman Jones]

Hi Jay,

The .address function returns the full path in a string, but how would I
use
that string as a range variable that incorporates the Workbook and
Worksheet?

You already have the range variable, namely Rng. The sStr
variable was only used to demonstrate the range address.

Also, the prompt box doesnt allow me to select a different workbook while
its open. Is there a way around it?

The inputBox does allow the selection of another workbook; try
clicking on 'Window' in the menu bar.


---
Regards,
Norman

The .address function returns the full path in a string, but how would I
use
that string as a range variable that incorporates the Workbook and
Worksheet?

Also, the prompt box doesnt allow me to select a different workbook while
its open. Is there a way around it?

Thanks.

Hi J,

Try something like:

'=============>>
Public Sub Tester()
Dim Rng As Range
Dim sStr As String

On Error Resume Next
Set Rng = Application.InputBox( _
Prompt:="Select range", _
Type:=8)
On Error GoTo 0

If Not Rng Is Nothing Then
sStr = Rng.Address(external:=True)
MsgBox sStr
End If

End Sub
'<<=============


---
Regards,
Norman

I have a macro that takes a range of cells as input. I am trying to use
an
InputBox or something similiar to let the user select the range they
need.
First how would I let the user select their range in the format :
'[Book1.xls]Sheet2'!$F$6:$F$10, and second how would I transform that
into
a
variable that's similiar to Workbooks().WorkSheets().Range()?

 

[Guest]

Ah I didnt realize the range variable incorporates the full path as is.
Thanks very much Norman.

Hi Jay,

The .address function returns the full path in a string, but how would I
use
that string as a range variable that incorporates the Workbook and
Worksheet?

You already have the range variable, namely Rng. The sStr
variable was only used to demonstrate the range address.

Also, the prompt box doesnt allow me to select a different workbook while
its open. Is there a way around it?

The inputBox does allow the selection of another workbook; try
clicking on 'Window' in the menu bar.


---
Regards,
Norman

The .address function returns the full path in a string, but how would I
use
that string as a range variable that incorporates the Workbook and
Worksheet?

Also, the prompt box doesnt allow me to select a different workbook while
its open. Is there a way around it?

Thanks.

Hi J,

Try something like:

'=============>>
Public Sub Tester()
Dim Rng As Range
Dim sStr As String

On Error Resume Next
Set Rng = Application.InputBox( _
Prompt:="Select range", _
Type:=8)
On Error GoTo 0

If Not Rng Is Nothing Then
sStr = Rng.Address(external:=True)
MsgBox sStr
End If

End Sub
'<<=============


---
Regards,
Norman



I have a macro that takes a range of cells as input. I am trying to use
an
InputBox or something similiar to let the user select the range they
need.
First how would I let the user select their range in the format :
'[Book1.xls]Sheet2'!$F$6:$F$10, and second how would I transform that
into
a
variable that's similiar to Workbooks().WorkSheets().Range()?