Solved: Add Input box to this code

[YellowLabPro]

Option Explicit
Sub Macro1()
Dim i As Long
With Range("n135:w163")
For i = 1 To .Rows.Count
With .Rows(i)
.Rows(i).Sort Key1:=.Rows(i).Range("a1"), Order1:=xlAscending, _
Orientation:=xlLeftToRight
End With
Next
End With
End Sub


Currently this is an absolute reference, I would like to change it to where it will ask through an input box the range.

thanks,
YLP

[mdmackillop]

Hi Yelp,
Just prompt for the correct address format
With Range(InputBox("Enter range in N135:W163 format"))

[mdmackillop]

If you don't trust your users to get the format correct, you could use two inputs
Strt = InputBox("Enter first cell")
Endd = InputBox("Enter last cell")
With Range(Strt & ":" & Endd)

[Bob Phillips]

Dim rng As Range
Dim i As Long
Set rng = Application.InputBox("Select range with the mouse", Type:=8)
If Not rng Is Nothing Then
With rng
For i = 1 To .Rows.Count
With .Rows(i)
.Rows(i).Sort Key1:=.Rows(i).Range("A1"), Order1:=xlAscending, _
Orientation:=xlLeftToRight
End With
Next
End With
End If

[YellowLabPro]

Thanks Guys.... :-)

I am using Dennis' code currently. After I get this worked out, I will experiment w/ Md's.

Dennis, is the reason I am able to use the mouse because you have a type 8?

I have a follow up request for Dennis' code. Can we pass the range from the mouse select to a variable that will allow me to run the code again w/out having to select the range over and over? If we can do this, and I will need your help in writing this code, can we also have a way to select the range again once the range has been selected in the existing file?

My thinking here is that if the range changes I can still use the code, but it will also need to be reset when the code is run in a new file or a newly opened file.

Thanks,

YLP

[mdmackillop]

Hi Yelp,
Dennis is the other XL guy. This one is known as "Bob"

[mdmackillop]

For more detail, check the Inputbox Method in VBA help.
You can set a variable to the returned range
Set MyRange = Application.InputBox("Select range with the mouse", Type:=8) The range address could be stored in the workbook, as a named range or as a workbook property to avoid the need to select it in future use of the workbook.

[Bob Phillips]

Thanks Guys.... :-)

I am using Dennis' code currently. After I get this worked out, I will experiment w/ Md's.

Dennis, is the reason I am able to use the mouse because you have a type 8?

Bob.

Yes, exactly.


I have a follow up request for Dennis' code. Can we pass the range from the mouse select to a variable that will allow me to run the code again w/out having to select the range over and over? If we can do this, and I will need your help in writing this code, can we also have a way to select the range again once the range has been selected in the existing file?

My thinking here is that if the range changes I can still use the code, but it will also need to be reset when the code is run in a new file or a newly opened file.

I already did that, I put it in the rng variable. You might just need to make it a global variable.

[YellowLabPro]

Sorry Bob, thought it was Malcolm.... it had been a while and I was having trouble recalling your name, but then you hit me w/ "Bob" .... now I am really confused :dunno :)

Thanks Dennis,
Did not realize that, I had not tried it, I was just thinking about it while I was working on the file.

[mdmackillop]

Sub Naming()
Dim XLD As String, XL-Dennis As String, MD As String
XLD = "Bob"
XL-Dennis = "Dennis"
MD = "Malcolm"
End Sub

[YellowLabPro]

Oh TOO FUNNY!

Gotcha- now. Thanks Malcolm.... a little to witty today for me :-)

[Bob Phillips]

Sub Naming()
Dim XLD As String, XL-Dennis As String, MD As String
XLD = "Bob"
XL-Dennis = "Dennis"
MD = "Malcolm"
End Sub

:rotlaugh: