VBA Cell Selection via input box

  • Thread starter Thread starter JTech
  • Start date Start date
J

JTech

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.
 
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,

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
 
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'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
 
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?
 
I don't know how to do that. The sum pops up at the bottom only after the
selection is complete.
 
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?
 
Right. Otto
JTech said:
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?
Click to expand...
 
That would work. Also, the filter can be used and the code modified
slightly to select only the visible values. Your call. Otto
 
Can you give me an example of the modified code? Also is there a way to
display the sum of the selection back in the input box? The latter is
probably impossible.
 
Hi JTech,

Just adding to Gary's code this will show another input box with the
sum of the range selected.

Sub rngtest()

Dim myRng As Range
Dim mySum As Variant

Set r = Application.InputBox(Prompt:="select range with mouse",
Type:=8)
r.Select
If TypeOf Selection Is Range Then
Set myRng = Selection
End If
mySum = Application.WorksheetFunction.Sum(myRng)
a = Application.InputBox(Prompt:="the total of that range is",
Default:=mySum)

End Sub

Regards

Michael
 
Thanks, Mike

The code is nice.

However I would like to be able to see the sum while I am selecting.

Here is the Scenario:

I get orders that come in on a monthly basis. I would like to be able to
count the items that have come in and then run the code to Match the
requested items.

Lets say a few people have ordered seven yellow pencils. 4 pencils came in
and 3 are pending. I would like to run the code and select the amount
totaling the 4 pencils and tag them as received which Otto's code will allow
me to do eliquently. Everyone's combined code would be excellent if I could
just see the sum as the selection process takes place.

The only reason that I am trying to do this by code is that I have code that
Cycles through autofiltered items. It would be so cool to add this code to a
received in feature.
 
Hi JTech,

The results sum, count, average etc that appear in the right of the
status bar are not (as far as l know) programatically available in
VBA.

If you want to select a range AND see the sum of the selected range at
the same time you will have to use a userform and programme it
accordingly. The main controls to use would be RefEdit for the range
input and a text box to display the sum value. On closing the userform
you can then 'Call' Otto's code.

This is just a basic idea which hopefully will put you on the right
track

Regards

Michael.
 
Hi JTech,

This code might be sufficient for your purposes, it presents the total
of the range selected in a message box, if the total is OK then do
something(Otto's code?), if NO then select range again, Cancel exit
sub.

If is not as good as using a userform but it is an alternative.

BEWARE WORD WRAP

Sub rngtest()

Dim myRng As Range
Dim mySum As Variant

Set r = Application.InputBox(Prompt:="select range with mouse",
Type:=8)
r.Select
If TypeOf Selection Is Range Then
Set myRng = Selection
End If
mySum = Application.WorksheetFunction.Sum(myRng)
Select Case MsgBox("The total of the range selected is " & mySum & ""
_
, vbYesNoCancel Or vbInformation Or vbDefaultButton1,
"Read This:")
Case vbYes
MsgBox ("Run Otto's code") '<<<< eg. replace with Call
OttosMacro
Case vbNo
Set r = Application.InputBox(Prompt:="select range with
mouse", Type:=8)
Case vbCancel
Exit Sub
End Select

End Sub



HTH

Regards

Michael
 
That would actually work. Alternatively (I have to ask this) do you think it
would be possible for me to do the following:

Lets say 5 pencils came in. Could I have the code pop up an input box asking
me how Many Items came in? I would enter 5.
Next the code would sum the numbers in the "amount" column and only perform
Otto's code on those items.

In other words the code would perform the math for me and I would no longer
need to see the sum in the bottom of excel anymore.

Something like this:

The first two items together equal 5 so they are the only ones that would
get the received tag.

Amounts Items Status
2 Pencil Received
3 Pencil Received
1 Pencil

I really appreciate your help on this endeavor. This has been recking my
brain for about a year.
 
Back
Top