application.inputbox

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Dave Peterson]

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.

 

[Guest]

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?

 

[Guest]

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