Looping while msgbox is active

[gtslabs]

I have some code that uses a form to start acquiring data from a
serial port using using netcomm1.ocx which is similar to mscomm1.ocx.

It is putting the live data into an excel cell. So essentiall I have a
meter in a cell reading the output from a data aquistion system.

I have it being called from a form becuase that is how I added the
activex netcomm1.ocx.
I really dont want to start from a form and would like to have command
buttons on the sheet.
How can I do that?

My second question is that I want to stream this data live to a cell
and have it updated at a certain frequency. Using the form code to
start this process I need a way to have a user stop the live stream.
I wanted a msgbox to be hit to stop the data. So I tried to code
below a loop that would keep updatind the cells with live data while
the ok button was not depressed.
It did not work. Any idea on what I need to fix here?



Response = 0
Do While Response <> 1

Response = MsgBox("Click Yes to Stop the live data stream",
vbOKOnly + vbDefaultButton1, "Stop Live Data")


Buffer$ = ""
NETComm1.Output = "CALL 5" & Chr(13) ' retrieve reading
from Serial Device
Buffer$ = Buffer$ & NETComm1.InputData
Cells(6, 1) = Application.Clean(Buffer$)
TimedDelay (0.1)

Buffer$ = ""
NETComm1.Output = "CALL 6" & Chr(13) ' retrieve reading
from Serial Device
Buffer$ = Buffer$ & NETComm1.InputData
Cells(6, 2) = Application.Clean(Buffer$)
TimedDelay (0.1)

Loop

Thanks

 

[Vergel Adriano]

Look for the Forms toolbox. There's a button control there that you can drag
into the worksheet and assign a macro to it.

As for looping while a message box is displayed, you can consider using a
user form that is displayed with the Modal parameter set to False. When a
message box is displayed, the control is not returned to VBA until the
message box is dismissed. Another idea is to put your Start/Stop button
directly in the spreadsheet.

 

[Mark Ivey]

You may consider embedding two command buttons directly on the sheet...

I would use one to invoke an OnTime event and set it at a specified time
interval to replicate your streaming input data. In other words, have the
OnTime procedure run your data streaming macro at specified time intervals.

Then I would use another command button to disable the OnTime event that was
started with the first command button.