application.wait help

[flowtester]

I could really use some help with one. I almost get it but then run
smack into a wall. It's hard to explain but I'll try my best.

Cell A1 receives it's data from another application. It's a pressure
reading.

The user needs to be able to adjust a control knob on a machine that in
effect, changes the value of A1 by controlling the pressure. That part
all works fine. The machine functions, A1 values change like they
should.

As A1 changes, the values of B1 and C1 change as well.

When the desired pressure is met, I have individual control buttons
that run macros to copy A1:C1 to 11 locations on the worksheet in
series.

I'm trying to automate the process so that A1 is monitored and upon
reaching a specified value, will run the macros in series.

To monitor A1, I use M1 as the preset value. When A1=M1 run the first
macro.

At that point, the process needs to wait while an adjustment is made to
the control knob and to another mechanical part. I need a short delay
for the adjustments to be carried out [application.wait with a value..I
have that figured out] As the adjustments are made, A1 <> M1 BUT when it
again equals A1, I want the next macro to run.

Application.wait seemed like the answer but it's not because while the
application.wait process is going on, A1 is sitting there in limbo and
the value is not changing while the adjustments are made.

As soon as the application.wait time has expired, my current code calls
the next macro and it's grabbing inaccurate data.

Any and all ideas are welcome! Please, someone that knows VB respond to
me on this. I'm 99% of the way to where I need to be and there's this
one last little hurdle...

 

[Bob Phillips]

You could try a API timer. I have posted an example of this a few times that
will allow you to click a button and start the clock, click another to stop
it. It could easily be amended to a countdown.

Can you do that or do you want to mail me the workbook, and I could replace
your Application Wait to a timed countdown?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[flowtester]

Bob,

By using a timer, is the application put into a suspend mode like with
application.wait?

What I'd really like to be able to do is monitor A1 and compare it to
M1 which is the preset trigger value . Then when A1 equals M1, fire the
macro to copy A1:C1 to their locations.

When A1 = M1, Fire the macro that copies [A1:C1] to [C11:E11]
Then wait while adjustments are made but keep monitoring A1 while the
adjustments are made. That puts the first set of values in place.

Then when A1=M1 and [c11:e11] <> [""] fire the macro that copies
[a1:c1] to [c12:e12]. Then wait for adjustments again while continuing
to monitor A1 and when [a1=M1] again, fire the next macro....this time
checking to see that [c12:e12] <> [""]....and keep doing this in turn,
allowing time for adjustments between each macro run...until values of
[A1:C1] have been copied and pasted to all 11 locations..being
[C11:E11] on down through [C21:E21]


I will gladly email you the workbook if you want.

 

[Bob Phillips]

The timer that I am describing uses events, so it is sharing the CPU with
the other tasks.

But by this description, couldn't you just use worksheet change events to
monitor a cell, and when it reaches a value, fire your macro. The timer
doesn't seem to be needed.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Maybe you could use application.ontime to just delay your call to the second
macro.

Chip Pearson has lots of instructions at:
http://www.cpearson.com/excel/ontime.htm

I could really use some help with one. I almost get it but then run
smack into a wall. It's hard to explain but I'll try my best.

Cell A1 receives it's data from another application. It's a pressure
reading.

The user needs to be able to adjust a control knob on a machine that in
effect, changes the value of A1 by controlling the pressure. That part
all works fine. The machine functions, A1 values change like they
should.

As A1 changes, the values of B1 and C1 change as well.

When the desired pressure is met, I have individual control buttons
that run macros to copy A1:C1 to 11 locations on the worksheet in
series.

I'm trying to automate the process so that A1 is monitored and upon
reaching a specified value, will run the macros in series.

To monitor A1, I use M1 as the preset value. When A1=M1 run the first
macro.

At that point, the process needs to wait while an adjustment is made to
the control knob and to another mechanical part. I need a short delay
for the adjustments to be carried out [application.wait with a value..I
have that figured out] As the adjustments are made, A1 <> M1 BUT when it
again equals A1, I want the next macro to run.

Application.wait seemed like the answer but it's not because while the
application.wait process is going on, A1 is sitting there in limbo and
the value is not changing while the adjustments are made.

As soon as the application.wait time has expired, my current code calls
the next macro and it's grabbing inaccurate data.

Any and all ideas are welcome! Please, someone that knows VB respond to
me on this. I'm 99% of the way to where I need to be and there's this
one last little hurdle...

 

[flowtester]

Maybe you could use application.ontime to just delay your call to the
second
macro.

Chip Pearson has lots of instructions at:
http://www.cpearson.com/excel/ontime.htm

I thought about that too and I'll bet it would work as a work around.
The thing of it is that the time frame for adjustments can vary because
once the mechanical part adjustment is made you then adjust for a
specific pressure and that sometimes takes a little juggling. It may or
may not fall right into place. You may or may not have to play with the
control knob to get the desired pressure to hold steady at the preset
value you want the captures to occur at.

OnChange was tried first but for whatever reason, it won't pick up the
changes in A1. It just doesn't see them happening. Carriage returns
were added to the back end application that feeds A1 and made no
difference...

Ultimately I think the way I have the code working right now, it's just
firing the macro after application.wait time has been satisfied...

Larry

 

[Bob Phillips]

If the change event doesn't work, you could try adding a cell referring to
A1, =A1, which would force the Calculate event, which you could then use.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[flowtester]

Bob,

I would like to email the workbook to you but I don't see your email
address. I tried looking it up in the member area but no-go on
that...

Larry

 

[Bob Phillips]

Well I won't have the facilities to capture the data as you do, but if you
click on the From field in the header, you will see my addy. Note my spam
filter notice in my signature though.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[flowtester]

I dont' see anything that gives me your email. I've tried switching my
view options and even broke down and read the FAQ..

If you email me..i'll grab your address and send the workbook..

(e-mail address removed)

 

[Dave Peterson]

(e-mail address removed)

(remove nothere from the email address if mailing direct)