Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox that obtains Workbook, Worksheet, and Range
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()? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox that obtains Workbook, Worksheet, and Range
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 "J@Y" wrote in message ... 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()? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox that obtains Workbook, Worksheet, and Range
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. "Norman Jones" wrote: 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 "J@Y" wrote in message ... 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()? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox that obtains Workbook, Worksheet, and Range
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 "J@Y" wrote in message ... 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. "Norman Jones" wrote: 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 "J@Y" wrote in message ... 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()? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox that obtains Workbook, Worksheet, and Range
Ah I didnt realize the range variable incorporates the full path as is.
Thanks very much Norman. "Norman Jones" wrote: 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 "J@Y" wrote in message ... 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. "Norman Jones" wrote: 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 "J@Y" wrote in message ... 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()? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range through Inputbox | Excel Programming | |||
Creating an Index Sheet that obtains a cell Value using VBA | Excel Programming | |||
InputBox method with range | Excel Programming | |||
Application.inputbox for another workbook | Excel Programming | |||
Select range with InputBox. Is it possible? | Excel Programming |