I'm attempting to have my VBA pause for the user to enter a range. I've used
application.inputbox(prompt:="Enter range", type:=8) which works great as
long as the user is planning to select a range in the current workbook.

Does anyone have a suggestion for allowing the user to select a range in a
different workbook?

Thanks in advance,

Christmas May

Couldn't figure that out myself when I had to do it a little while ago, so
I'm also interested in other folks response. I resorted to a two-step
process:

I ended up creating a userform w/a dropdown box listing all of the open
workbooks. When the OK button was clicked the value from the combobox is
saved to a public variable and the userform is unloaded. Then the code
activates the target workbook and the inputbox prompts the user to select the
range. I'm sure there's a better way, but the macro was for my own use and
the destination worksheet and range don't change - so all I really needed was
the target workbook.

Code for the command button and combobox from the userform

Private Sub CommandButton1_Click()
wkbDest = Me.ComboBox1.Value
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim wkbTemp As Workbook
For Each wkbTemp In Workbooks
Me.ComboBox1.AddItem wkbTemp.Name
Next wkbTemp
End Sub


code for the macro:

Public wkbDest As String

Sub test()
Dim rngDest As Range

wkbDest = ""
UserForm1.Show
Workbooks(wkbDest).Activate
Set rngDest = Application.InputBox("Enter Range", Type:=8)
ThisWorkbook.Activate
rngDest.Value = "Testing"
End Sub


I didn't include any error handling, so you may want to look at that.



"Christmas May" wrote:

I'm attempting to have my VBA pause for the user to enter a range. I've used
application.inputbox(prompt:="Enter range", type:=8) which works great as
long as the user is planning to select a range in the current workbook.

Does anyone have a suggestion for allowing the user to select a range in a
different workbook?

Thanks in advance,

Christmas May

You can use parents and grandparents. This little macro allows the user to
select a range in any workbook and worksheet:


Sub rangerover()
Dim r As Range
On Error Resume Next
Set r = Nothing
Set r = Application.InputBox("Select a cell with the mouse", Type:=8)
MsgBox ("Address is: " & r.Address)
MsgBox ("Worksheet is: " & r.Parent.Name)
MsgBox ("Workbook is: " & r.Parent.Parent.Name)
r.Parent.Parent.Activate
r.Parent.Activate
r.Select
End Sub

--
Gary's Student


"Christmas May" wrote:

I'm attempting to have my VBA pause for the user to enter a range. I've used
application.inputbox(prompt:="Enter range", type:=8) which works great as
long as the user is planning to select a range in the current workbook.

Does anyone have a suggestion for allowing the user to select a range in a
different workbook?

Thanks in advance,

Christmas May

Tell the user to go through the Windows dropdown on the worksheet menu bar.

Another option is to use Window|arrange|Tiled (or whatever you like) to arrange
the windows before you do the application.inputbox.



Christmas May wrote:

I'm attempting to have my VBA pause for the user to enter a range. I've used
application.inputbox(prompt:="Enter range", type:=8) which works great as
long as the user is planning to select a range in the current workbook.

Does anyone have a suggestion for allowing the user to select a range in a
different workbook?

Thanks in advance,

Christmas May

--

Dave Peterson

Hi "Gary's Student"

I saw your partent/grandparent post on the pause macro/ application.inputbox
discussion. I am using you code in an application to automate a quotation
from another workbook with several separte worksheets all with different
items and options.
Is it possible to change the selection method from "cell" to selecting
"named ranges" within the workbook?
--
Thanks

Mannie G


"Gary''s Student" wrote:

You can use parents and grandparents. This little macro allows the user to
select a range in any workbook and worksheet:


Sub rangerover()
Dim r As Range
On Error Resume Next
Set r = Nothing
Set r = Application.InputBox("Select a cell with the mouse", Type:=8)
MsgBox ("Address is: " & r.Address)
MsgBox ("Worksheet is: " & r.Parent.Name)
MsgBox ("Workbook is: " & r.Parent.Parent.Name)
r.Parent.Parent.Activate
r.Parent.Activate
r.Select
End Sub

--
Gary's Student


"Christmas May" wrote:

I'm attempting to have my VBA pause for the user to enter a range. I've used
application.inputbox(prompt:="Enter range", type:=8) which works great as
long as the user is planning to select a range in the current workbook.

Does anyone have a suggestion for allowing the user to select a range in a
different workbook?

Thanks in advance,

Christmas May

Very easy since a named range is just a string:

Sub rangerover2()
Dim r As Range
Dim s As String
On Error Resume Next
Set r = Nothing
s = Application.InputBox("Enter the name of a range", Type:=2)
Set r = Range(s)
MsgBox ("Address is: " & r.Address)
MsgBox ("Worksheet is: " & r.Parent.Name)
MsgBox ("Workbook is: " & r.Parent.Parent.Name)
r.Parent.Parent.Activate
r.Parent.Activate
r.Select
End Sub

--
Gary's Student


"Mannie G" wrote:

Hi "Gary's Student"

I saw your partent/grandparent post on the pause macro/ application.inputbox
discussion. I am using you code in an application to automate a quotation
from another workbook with several separte worksheets all with different
items and options.
Is it possible to change the selection method from "cell" to selecting
"named ranges" within the workbook?
--
Thanks

Mannie G


"Gary''s Student" wrote:

You can use parents and grandparents. This little macro allows the user to
select a range in any workbook and worksheet:


Sub rangerover()
Dim r As Range
On Error Resume Next
Set r = Nothing
Set r = Application.InputBox("Select a cell with the mouse", Type:=8)
MsgBox ("Address is: " & r.Address)
MsgBox ("Worksheet is: " & r.Parent.Name)
MsgBox ("Workbook is: " & r.Parent.Parent.Name)
r.Parent.Parent.Activate
r.Parent.Activate
r.Select
End Sub

--
Gary's Student


"Christmas May" wrote:

I'm attempting to have my VBA pause for the user to enter a range. I've used
application.inputbox(prompt:="Enter range", type:=8) which works great as
long as the user is planning to select a range in the current workbook.

Does anyone have a suggestion for allowing the user to select a range in a
different workbook?

Thanks in advance,

Christmas May