Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Selection via input box
Would it be possible to use a vba input box to do the cell selection?
For example could I use it to pick c1 through c3? If not is it possible to select cells b1 through b4 (cells that contain amounts) and then put something like received in the corresponding c1 through c4 cells? I am trying to find a way to automate this very time consuming inventory task. See wishful example below: I would like to select the cells that have the amounts 1 and 4 in them and run macro/vba code that would fill in the corresponding cells with Text Like "Received". Amounts Status 1 Received 4 Received 6 Received 7 Highlighting them allows me to see the total in the Sum Area of Excel. Once I have selected the amount of Items that I know have come in, I would activate the Macro. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Selection via input box
How about a button in the sheet. Assign the following macro to that button.
You select a group of cells, contiguous or not, and click on the button. Done. Post back if I misunderstood what you want. HTH Otto Sub ReceivedA() Dim i As Range For Each i In Selection i.Offset(, 1).Value = "Received" Next i End Sub "JTech" wrote in message ... Would it be possible to use a vba input box to do the cell selection? For example could I use it to pick c1 through c3? If not is it possible to select cells b1 through b4 (cells that contain amounts) and then put something like received in the corresponding c1 through c4 cells? I am trying to find a way to automate this very time consuming inventory task. See wishful example below: I would like to select the cells that have the amounts 1 and 4 in them and run macro/vba code that would fill in the corresponding cells with Text Like "Received". Amounts Status 1 Received 4 Received 6 Received 7 Highlighting them allows me to see the total in the Sum Area of Excel. Once I have selected the amount of Items that I know have come in, I would activate the Macro. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Selection via input box
JTech,
I think... Range(InputBox("Please enter a range of cells")).Select ....might work But you would have to type in... C1:C3 ....to get it to work like that. You could also try... C1.C3 ....to try to help speed things up. (when entering formulas in cells, you can use the period/dot/decimal point on the number pad in place of the colon when you type in range references. typing a period is a lot quicker than typing a colon, especially if you hand is already over there using the number pad to type in row numbers in the range references.) I don't know if the period-in-place-of-the-colon trick will work in this way. Try it and let me know. HTH, Conan "JTech" wrote in message ... Would it be possible to use a vba input box to do the cell selection? For example could I use it to pick c1 through c3? If not is it possible to select cells b1 through b4 (cells that contain amounts) and then put something like received in the corresponding c1 through c4 cells? I am trying to find a way to automate this very time consuming inventory task. See wishful example below: I would like to select the cells that have the amounts 1 and 4 in them and run macro/vba code that would fill in the corresponding cells with Text Like "Received". Amounts Status 1 Received 4 Received 6 Received 7 Highlighting them allows me to see the total in the Sum Area of Excel. Once I have selected the amount of Items that I know have come in, I would activate the Macro. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Selection via input box
This may be exactly what I am looking for. If I understand the code right it
will offset the value received in 1 column over. Right? "Otto Moehrbach" wrote: How about a button in the sheet. Assign the following macro to that button. You select a group of cells, contiguous or not, and click on the button. Done. Post back if I misunderstood what you want. HTH Otto Sub ReceivedA() Dim i As Range For Each i In Selection i.Offset(, 1).Value = "Received" Next i End Sub "JTech" wrote in message ... Would it be possible to use a vba input box to do the cell selection? For example could I use it to pick c1 through c3? If not is it possible to select cells b1 through b4 (cells that contain amounts) and then put something like received in the corresponding c1 through c4 cells? I am trying to find a way to automate this very time consuming inventory task. See wishful example below: I would like to select the cells that have the amounts 1 and 4 in them and run macro/vba code that would fill in the corresponding cells with Text Like "Received". Amounts Status 1 Received 4 Received 6 Received 7 Highlighting them allows me to see the total in the Sum Area of Excel. Once I have selected the amount of Items that I know have come in, I would activate the Macro. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Selection via input box
Otto's suggestion is good if you want to Select a range and then run some
code. If you want to Select the range via user input within the code, then: Sub sistence() Set r = Application.InputBox(Prompt:="select range with mouse", Type:=8) r.Select End Sub -- Gary''s Student - gsnu200771 "JTech" wrote: Would it be possible to use a vba input box to do the cell selection? For example could I use it to pick c1 through c3? If not is it possible to select cells b1 through b4 (cells that contain amounts) and then put something like received in the corresponding c1 through c4 cells? I am trying to find a way to automate this very time consuming inventory task. See wishful example below: I would like to select the cells that have the amounts 1 and 4 in them and run macro/vba code that would fill in the corresponding cells with Text Like "Received". Amounts Status 1 Received 4 Received 6 Received 7 Highlighting them allows me to see the total in the Sum Area of Excel. Once I have selected the amount of Items that I know have come in, I would activate the Macro. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Selection via input box
Now, That is very cool. And I believe that I could merge the two. Is there a
way to display the sum in bottom of excel while selecting? "Gary''s Student" wrote: Otto's suggestion is good if you want to Select a range and then run some code. If you want to Select the range via user input within the code, then: Sub sistence() Set r = Application.InputBox(Prompt:="select range with mouse", Type:=8) r.Select End Sub -- Gary''s Student - gsnu200771 "JTech" wrote: Would it be possible to use a vba input box to do the cell selection? For example could I use it to pick c1 through c3? If not is it possible to select cells b1 through b4 (cells that contain amounts) and then put something like received in the corresponding c1 through c4 cells? I am trying to find a way to automate this very time consuming inventory task. See wishful example below: I would like to select the cells that have the amounts 1 and 4 in them and run macro/vba code that would fill in the corresponding cells with Text Like "Received". Amounts Status 1 Received 4 Received 6 Received 7 Highlighting them allows me to see the total in the Sum Area of Excel. Once I have selected the amount of Items that I know have come in, I would activate the Macro. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Selection via input box
I don't know how to do that. The sum pops up at the bottom only after the
selection is complete. -- Gary''s Student - gsnu200771 "JTech" wrote: Now, That is very cool. And I believe that I could merge the two. Is there a way to display the sum in bottom of excel while selecting? "Gary''s Student" wrote: Otto's suggestion is good if you want to Select a range and then run some code. If you want to Select the range via user input within the code, then: Sub sistence() Set r = Application.InputBox(Prompt:="select range with mouse", Type:=8) r.Select End Sub -- Gary''s Student - gsnu200771 "JTech" wrote: Would it be possible to use a vba input box to do the cell selection? For example could I use it to pick c1 through c3? If not is it possible to select cells b1 through b4 (cells that contain amounts) and then put something like received in the corresponding c1 through c4 cells? I am trying to find a way to automate this very time consuming inventory task. See wishful example below: I would like to select the cells that have the amounts 1 and 4 in them and run macro/vba code that would fill in the corresponding cells with Text Like "Received". Amounts Status 1 Received 4 Received 6 Received 7 Highlighting them allows me to see the total in the Sum Area of Excel. Once I have selected the amount of Items that I know have come in, I would activate the Macro. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Selection via input box
One More thing that I forgot to mention Otto, Conan and Gary that is very
important is that I am also using autofilters with this worksheet. So that if I select say anything in the filtered results and apply an edit to a offset cell, the action is going to take place for each cell in between as well. The only way that this does not happen is if i select each cell with a ctrl. Is this the nature of the beast? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Selection via input box
Right. Otto
"JTech" wrote in message ... This may be exactly what I am looking for. If I understand the code right it will offset the value received in 1 column over. Right? "Otto Moehrbach" wrote: How about a button in the sheet. Assign the following macro to that button. You select a group of cells, contiguous or not, and click on the button. Done. Post back if I misunderstood what you want. HTH Otto Sub ReceivedA() Dim i As Range For Each i In Selection i.Offset(, 1).Value = "Received" Next i End Sub "JTech" wrote in message ... Would it be possible to use a vba input box to do the cell selection? For example could I use it to pick c1 through c3? If not is it possible to select cells b1 through b4 (cells that contain amounts) and then put something like received in the corresponding c1 through c4 cells? I am trying to find a way to automate this very time consuming inventory task. See wishful example below: I would like to select the cells that have the amounts 1 and 4 in them and run macro/vba code that would fill in the corresponding cells with Text Like "Received". Amounts Status 1 Received 4 Received 6 Received 7 Highlighting them allows me to see the total in the Sum Area of Excel. Once I have selected the amount of Items that I know have come in, I would activate the Macro. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Selection via input box
That would work. Also, the filter can be used and the code modified
slightly to select only the visible values. Your call. Otto "JTech" wrote in message ... One More thing that I forgot to mention Otto, Conan and Gary that is very important is that I am also using autofilters with this worksheet. So that if I select say anything in the filtered results and apply an edit to a offset cell, the action is going to take place for each cell in between as well. The only way that this does not happen is if i select each cell with a ctrl. Is this the nature of the beast? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input selection choices | Excel Discussion (Misc queries) | |||
Pivot table input selection criteria | Excel Programming | |||
Input two cells with one selection | Excel Programming | |||
Macro to change list box input range based on selection made in another cell | Excel Programming | |||
Input box does not allow selection in another file | Excel Programming |