Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inputbox to return range from another workbook
hi,
i want the user to open an excel workbook of his choice and then choose a range from that excel workbook. this, of course, has to happen during the macro run. and the macro needs to capture the name of the new workbook, the sheet name and range chosen. when using inputbox, it works when i choose a range within the same workbook as is the macro. but when i try it with another workbook - nothing is returned. any ideas? I am using the code that I found on one of the pages on this very forum... Sub tester1() On Error Resume Next Set rng = Nothing Set rng = Application.InputBox("Select a cell with the mouse", Type:=8) MsgBox rng.Address rng.Parent.Activate If Not rng Is Nothing Then rng(1).Select Else MsgBox "You didn't select" End If End Sub Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inputbox to return range from another workbook
Try this:
Sub test() Dim fName As String, wb As Workbook Dim rng As Range fName = Application.GetOpenFilename() If fName < "False" Then Set wb = Workbooks.Open(fName) Else Exit Sub End If Set rng = Application.InputBox("Select a cell", Type:=8) MsgBox "Range: " & rng.Address & vbLf & _ "Filename: " & wb.Name & vbLf & _ "Sheet: " & ActiveSheet.Name wb.Close SaveChanges:=False End Sub -- Dan On Dec 18, 9:27 am, wrote: hi, i want the user to open an excel workbook of his choice and then choose a range from that excel workbook. this, of course, has to happen during the macro run. and the macro needs to capture the name of the new workbook, the sheet name and range chosen. when using inputbox, it works when i choose a range within the same workbook as is the macro. but when i try it with another workbook - nothing is returned. any ideas? I am using the code that I found on one of the pages on this very forum... Sub tester1() On Error Resume Next Set rng = Nothing Set rng = Application.InputBox("Select a cell with the mouse", Type:=8) MsgBox rng.Address rng.Parent.Activate If Not rng Is Nothing Then rng(1).Select Else MsgBox "You didn't select" End If End Sub Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inputbox to return range from another workbook
Forgot a couple things... try this one:
Sub test() Dim fName As String, wb As Workbook Dim rng As Range fName = Application.GetOpenFilename() If fName < "False" Then Set wb = Workbooks.Open(fName) Else Exit Sub End If Set rng = Application.InputBox("Select a cell", Type:=8) If Not rng Is Nothing Then rng.Parent.Activate MsgBox "Range: " & rng.Address & vbLf & _ "Filename: " & wb.Name & vbLf & _ "Sheet: " & ActiveSheet.Name Else MsgBox "Nothing selected!" End If wb.Close SaveChanges:=False End Sub -- Dan On Dec 18, 9:27 am, wrote: hi, i want the user to open an excel workbook of his choice and then choose a range from that excel workbook. this, of course, has to happen during the macro run. and the macro needs to capture the name of the new workbook, the sheet name and range chosen. when using inputbox, it works when i choose a range within the same workbook as is the macro. but when i try it with another workbook - nothing is returned. any ideas? I am using the code that I found on one of the pages on this very forum... Sub tester1() On Error Resume Next Set rng = Nothing Set rng = Application.InputBox("Select a cell with the mouse", Type:=8) MsgBox rng.Address rng.Parent.Activate If Not rng Is Nothing Then rng(1).Select Else MsgBox "You didn't select" End If End Sub Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inputbox to return range from another workbook
Hi Dan,
many thx. Your code works great. There was just one complication. For a very specific Excel file - it gives me the error "Run-time error '13' Type mismatch" It halts at "Set wb = Workbooks.Open(fName)" The code works with all other excel files. Could there be any reasons that you could think of - as to why this specific excel file gives such an error? Thanks anyways Saju On Dec 18, 5:06 pm, "Dan R." wrote: Forgot a couple things... try this one: Sub test() Dim fName As String, wb As Workbook Dim rng As Range fName = Application.GetOpenFilename() If fName < "False" Then Set wb = Workbooks.Open(fName) Else Exit Sub End If Set rng = Application.InputBox("Select a cell", Type:=8) If Not rng Is Nothing Then rng.Parent.Activate MsgBox "Range: " & rng.Address & vbLf & _ "Filename: " & wb.Name & vbLf & _ "Sheet: " & ActiveSheet.Name Else MsgBox "Nothing selected!" End If wb.Close SaveChanges:=False End Sub -- Dan On Dec 18, 9:27 am, wrote: hi, i want the user to open an excel workbook of his choice and then choose a range from that excel workbook. this, of course, has to happen during the macro run. and the macro needs to capture the name of the new workbook, the sheet name and range chosen. when using inputbox, it works when i choose a range within the same workbook as is the macro. but when i try it with another workbook - nothing is returned. any ideas? I am using the code that I found on one of the pages on this very forum... Sub tester1() On Error Resume Next Set rng = Nothing Set rng = Application.InputBox("Select a cell with the mouse", Type:=8) MsgBox rng.Address rng.Parent.Activate If Not rng Is Nothing Then rng(1).Select Else MsgBox "You didn't select" End If End Sub Thanks- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inputbox to return range from another workbook
Saju,
Just add 'On Error Resume Next' to the top of your code under the dim statements and that should clear it up. -- Dan On Dec 19, 5:48 am, wrote: Hi Dan, many thx. Your code works great. There was just one complication. For a very specific Excel file - it gives me the error "Run-time error '13' Type mismatch" It halts at "Set wb = Workbooks.Open(fName)" The code works with all other excel files. Could there be any reasons that you could think of - as to why this specific excel file gives such an error? Thanks anyways Saju On Dec 18, 5:06 pm, "Dan R." wrote: Forgot a couple things... try this one: Sub test() Dim fName As String, wb As Workbook Dim rng As Range fName = Application.GetOpenFilename() If fName < "False" Then Set wb = Workbooks.Open(fName) Else Exit Sub End If Set rng = Application.InputBox("Select a cell", Type:=8) If Not rng Is Nothing Then rng.Parent.Activate MsgBox "Range: " & rng.Address & vbLf & _ "Filename: " & wb.Name & vbLf & _ "Sheet: " & ActiveSheet.Name Else MsgBox "Nothing selected!" End If wb.Close SaveChanges:=False End Sub -- Dan On Dec 18, 9:27 am, wrote: hi, i want the user to open an excel workbook of his choice and then choose a range from that excel workbook. this, of course, has to happen during the macro run. and the macro needs to capture the name of the new workbook, the sheet name and range chosen. when using inputbox, it works when i choose a range within the same workbook as is the macro. but when i try it with another workbook - nothing is returned. any ideas? I am using the code that I found on one of the pages on this very forum... Sub tester1() On Error Resume Next Set rng = Nothing Set rng = Application.InputBox("Select a cell with the mouse", Type:=8) MsgBox rng.Address rng.Parent.Activate If Not rng Is Nothing Then rng(1).Select Else MsgBox "You didn't select" End If End Sub Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
InputBox that obtains Workbook, Worksheet, and Range | Excel Programming | |||
Application.inputbox for another workbook | Excel Programming | |||
Trying to return Range from InputBox | Excel Programming | |||
Inputbox used to return value of selected cell | Excel Programming | |||
Inputbox to return address of cell selected by mouse | Excel Programming |