Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box that allows user to select range
To all,
I have a macro that runs normally from the range J2:J100, and another for M2:M100 howver this range can change, i.e. could be J2:J50, and M2:M50. The range is always the SAME for both columns.At the moment I am going in to edit the code every time I want to change it. What I want to be able to do is have a pop up box that allows the user to enter two numbers, one for the range start i.e. 2, and one for the range stop i.e. 60. (i.e. only specify the row, not the coulmn!). This would then set the range in the macro as J2:J60, and M2:M60. Any help would be much appreciated, Regards, Joseph Crabtree |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box that allows user to select range
Why don't you post your code and someone will modify it to meet your needs
You could just select the range and establish start and end from that rather than needing two Input Boxes Regards Trevor "joecrabtree" wrote in message ps.com... To all, I have a macro that runs normally from the range J2:J100, and another for M2:M100 howver this range can change, i.e. could be J2:J50, and M2:M50. The range is always the SAME for both columns.At the moment I am going in to edit the code every time I want to change it. What I want to be able to do is have a pop up box that allows the user to enter two numbers, one for the range start i.e. 2, and one for the range stop i.e. 60. (i.e. only specify the row, not the coulmn!). This would then set the range in the macro as J2:J60, and M2:M60. Any help would be much appreciated, Regards, Joseph Crabtree |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box that allows user to select range
As a user, I'd rather point and click at the range than trying to remember the
starting and ending row (and typing them in correctly). And if you always use column J and column M, you could just pick up one range and use that as the starting point. dim Rng1 as range dim Rng2 as range set rng1 = nothing on error resume next set rng1 = application.inputbox(Prompt:="select the rows to process." & _ vblf & "I'll worry about the columns!", type:=8).areas(1) on error goto 0 if rng1 is nothing then 'user hit cancel exit sub end if with rng1 set rng1 = intersect(.entirerow,.parent.range("J:J")) set rng2 = rng1.offset(0,3) end with msgbox rng1.address(0,0) & vblf & rng2.address(0,0) joecrabtree wrote: To all, I have a macro that runs normally from the range J2:J100, and another for M2:M100 howver this range can change, i.e. could be J2:J50, and M2:M50. The range is always the SAME for both columns.At the moment I am going in to edit the code every time I want to change it. What I want to be able to do is have a pop up box that allows the user to enter two numbers, one for the range start i.e. 2, and one for the range stop i.e. 60. (i.e. only specify the row, not the coulmn!). This would then set the range in the macro as J2:J60, and M2:M60. Any help would be much appreciated, Regards, Joseph Crabtree -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box that allows user to select range
Hi
Thanks for your help, however I would prefer not to click and point as the selection of the range is just the number of rows that the macro will perform the calculation on. The data is hidden from the user, and so all I want them to do is be able to input two numbers. Thanks in advance, Joseph Crabtree Dave Peterson wrote: As a user, I'd rather point and click at the range than trying to remember the starting and ending row (and typing them in correctly). And if you always use column J and column M, you could just pick up one range and use that as the starting point. dim Rng1 as range dim Rng2 as range set rng1 = nothing on error resume next set rng1 = application.inputbox(Prompt:="select the rows to process." & _ vblf & "I'll worry about the columns!", type:=8).areas(1) on error goto 0 if rng1 is nothing then 'user hit cancel exit sub end if with rng1 set rng1 = intersect(.entirerow,.parent.range("J:J")) set rng2 = rng1.offset(0,3) end with msgbox rng1.address(0,0) & vblf & rng2.address(0,0) joecrabtree wrote: To all, I have a macro that runs normally from the range J2:J100, and another for M2:M100 howver this range can change, i.e. could be J2:J50, and M2:M50. The range is always the SAME for both columns.At the moment I am going in to edit the code every time I want to change it. What I want to be able to do is have a pop up box that allows the user to enter two numbers, one for the range start i.e. 2, and one for the range stop i.e. 60. (i.e. only specify the row, not the coulmn!). This would then set the range in the macro as J2:J60, and M2:M60. Any help would be much appreciated, Regards, Joseph Crabtree -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box that allows user to select range
dim Num1 as long
dim Num2 as long num1 = application.inputbox(Prompt:="top row is:", type:=1) num2 = application.inputbox(Prompt:="bottom row is:", Type:=1) if application.min(num1,num2) < 1 _ or application.max(num1,num2) activesheet.rows.count then msgbox "nope!" exit sub end if joecrabtree wrote: Hi Thanks for your help, however I would prefer not to click and point as the selection of the range is just the number of rows that the macro will perform the calculation on. The data is hidden from the user, and so all I want them to do is be able to input two numbers. Thanks in advance, Joseph Crabtree Dave Peterson wrote: As a user, I'd rather point and click at the range than trying to remember the starting and ending row (and typing them in correctly). And if you always use column J and column M, you could just pick up one range and use that as the starting point. dim Rng1 as range dim Rng2 as range set rng1 = nothing on error resume next set rng1 = application.inputbox(Prompt:="select the rows to process." & _ vblf & "I'll worry about the columns!", type:=8).areas(1) on error goto 0 if rng1 is nothing then 'user hit cancel exit sub end if with rng1 set rng1 = intersect(.entirerow,.parent.range("J:J")) set rng2 = rng1.offset(0,3) end with msgbox rng1.address(0,0) & vblf & rng2.address(0,0) joecrabtree wrote: To all, I have a macro that runs normally from the range J2:J100, and another for M2:M100 howver this range can change, i.e. could be J2:J50, and M2:M50. The range is always the SAME for both columns.At the moment I am going in to edit the code every time I want to change it. What I want to be able to do is have a pop up box that allows the user to enter two numbers, one for the range start i.e. 2, and one for the range stop i.e. 60. (i.e. only specify the row, not the coulmn!). This would then set the range in the macro as J2:J60, and M2:M60. Any help would be much appreciated, Regards, Joseph Crabtree -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box that allows user to select range
If the data is hidden from the user, will they be able to guess the right row
numbers? If yes, how do they do it? Maybe your code could make the same guess and not bother the user. joecrabtree wrote: Hi Thanks for your help, however I would prefer not to click and point as the selection of the range is just the number of rows that the macro will perform the calculation on. The data is hidden from the user, and so all I want them to do is be able to input two numbers. Thanks in advance, Joseph Crabtree Dave Peterson wrote: As a user, I'd rather point and click at the range than trying to remember the starting and ending row (and typing them in correctly). And if you always use column J and column M, you could just pick up one range and use that as the starting point. dim Rng1 as range dim Rng2 as range set rng1 = nothing on error resume next set rng1 = application.inputbox(Prompt:="select the rows to process." & _ vblf & "I'll worry about the columns!", type:=8).areas(1) on error goto 0 if rng1 is nothing then 'user hit cancel exit sub end if with rng1 set rng1 = intersect(.entirerow,.parent.range("J:J")) set rng2 = rng1.offset(0,3) end with msgbox rng1.address(0,0) & vblf & rng2.address(0,0) joecrabtree wrote: To all, I have a macro that runs normally from the range J2:J100, and another for M2:M100 howver this range can change, i.e. could be J2:J50, and M2:M50. The range is always the SAME for both columns.At the moment I am going in to edit the code every time I want to change it. What I want to be able to do is have a pop up box that allows the user to enter two numbers, one for the range start i.e. 2, and one for the range stop i.e. 60. (i.e. only specify the row, not the coulmn!). This would then set the range in the macro as J2:J60, and M2:M60. Any help would be much appreciated, Regards, Joseph Crabtree -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to select a specific range based on the time value of user form input | New Users to Excel | |||
using input from user to select wk1 to wk52 | Excel Programming | |||
User input as a range | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
select data based on user input | Excel Programming |