Is there a way to edit cells while macro is paused at message box? I
have a spreadsheet that uses autofilters. Currently I am able to use a
macro to cycle through one of the Autofilters to print unique data. I
would like to now Using VBA cycle through one autofilter and come to a
message box. Once at the message box I would like it to stay active
while I edit a few of the filtered cells. Then I would like to hit the
Messagebox's OK button to go to the next autofiltered result and do
the same thing.

If that can't be done, Can the following be done instead?

Sorry that the subject is vague but, I was wondering if the following
is possible. I have a spreadsheet that I maintain that holds a years
worth of data. It is very similar to the following:

Name QTY Item Status
Name, one 1 Item one
Name, two 2 Item one
Name, three 1 Item two
Name, four 2 Item one


What I would like to be able to do is to count the items that have
come in and place that figure into an input box. And then mark that
particular amount as being received.


So let's say that 1 item one has come in. I would like to put
received
in the corresponding Status cell. It would get tricky if only 3 Item
ones came in. I would like to based on a total in the qty column
place
received in the corresponding cells as shown below:


Name QTY Item Status
Name, one 1 Item one Received
Name, two 2 Item one Recieved
Name, three 1 Item two
Name, four 2 Item one


Is there a way to do this via VBA? One more note is that I am using
autofilters.


If there is a way to do this I would greatly appreciate the
assistance.

Part one is not practical with a Message Box.

In part two, you could probably write some code to use an input box and
update the status of Items that you find needing it during your edit. It
would not be automatic. You would have to have a separate macro to run the
updates but you could Identify those needing updating with the edits and then
start the update macro to complete the task.

"Magoo" wrote:

> Is there a way to edit cells while macro is paused at message box? I
> have a spreadsheet that uses autofilters. Currently I am able to use a
> macro to cycle through one of the Autofilters to print unique data. I
> would like to now Using VBA cycle through one autofilter and come to a
> message box. Once at the message box I would like it to stay active
> while I edit a few of the filtered cells. Then I would like to hit the
> Messagebox's OK button to go to the next autofiltered result and do
> the same thing.
>
> If that can't be done, Can the following be done instead?
>
> Sorry that the subject is vague but, I was wondering if the following
> is possible. I have a spreadsheet that I maintain that holds a years
> worth of data. It is very similar to the following:
>
> Name QTY Item Status
> Name, one 1 Item one
> Name, two 2 Item one
> Name, three 1 Item two
> Name, four 2 Item one
>
>
> What I would like to be able to do is to count the items that have
> come in and place that figure into an input box. And then mark that
> particular amount as being received.
>
>
> So let's say that 1 item one has come in. I would like to put
> received
> in the corresponding Status cell. It would get tricky if only 3 Item
> ones came in. I would like to based on a total in the qty column
> place
> received in the corresponding cells as shown below:
>
>
> Name QTY Item Status
> Name, one 1 Item one Received
> Name, two 2 Item one Recieved
> Name, three 1 Item two
> Name, four 2 Item one
>
>
> Is there a way to do this via VBA? One more note is that I am using
> autofilters.
>
>
> If there is a way to do this I would greatly appreciate the
> assistance.
>

On Feb 25, 5:20*pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> Part one is not practical with a Message Box.
>
> In part two, you could probably write some code to use an input box and
> update the status of Items that you find needing it during your edit. *It
> would not be automatic. *You would have to have a separate macro to run the
> updates but you could Identify those needing updating with the edits and then
> start the update macro to complete the task.


Could you provide an example?

This is an over simplified example. You would select the cell to be revised
and
then trigger the macro.

Sub fixit()
newData = InputBox("Enter corrective data", "CORRECTION")
Selection.Value = newData
End Sub

The example is merely to illustrate how to replace data in a cell using
code. If you incorporated this principle into a macro that performs an edit
to find discrepant items then it would be useful.

"Magoo" wrote:

> On Feb 25, 5:20 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > Part one is not practical with a Message Box.
> >
> > In part two, you could probably write some code to use an input box and
> > update the status of Items that you find needing it during your edit. It
> > would not be automatic. You would have to have a separate macro to run the
> > updates but you could Identify those needing updating with the edits and then
> > start the update macro to complete the task.
>
>
> Could you provide an example?
>

Would it be possible to use the input box to do the cell selection?
For example could I use it to pick c1 through c3? and then put the
update in those selections?

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 Highlight the cells that have the amounts 1 and 4 in
them and run the macro/vba code that would fill in the corresponding
cells.

Amounts Status
1 Received
4 Received
6
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.