View Full Version : Solved: Add Input box to this code
YellowLabPro
03-04-2007, 07:16 AM
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
03-04-2007, 07:44 AM
Hi Yelp,
Just prompt for the correct address format
With Range(InputBox("Enter range in N135:W163 format"))
mdmackillop
03-04-2007, 07:49 AM
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
03-04-2007, 07:51 AM
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
03-04-2007, 08:07 AM
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
03-04-2007, 08:14 AM
Hi Yelp,
Dennis is the other XL guy. This one is known as "Bob"
mdmackillop
03-04-2007, 08:19 AM
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
03-04-2007, 08:50 AM
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
03-04-2007, 09:15 AM
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
03-04-2007, 09:22 AM
Sub Naming()
Dim XLD As String, XL-Dennis As String, MD As String
XLD = "Bob"
XL-Dennis = "Dennis"
MD = "Malcolm"
End Sub
YellowLabPro
03-04-2007, 09:48 AM
Oh TOO FUNNY!
Gotcha- now. Thanks Malcolm.... a little to witty today for me :-)
Bob Phillips
03-04-2007, 09:53 AM
Sub Naming()
Dim XLD As String, XL-Dennis As String, MD As String
XLD = "Bob"
XL-Dennis = "Dennis"
MD = "Malcolm"
End Sub
:rotlaugh:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.