VBA Application.OnTime not executed if cell editing is active

[mzinab]

I have VBA code that reads a temperature device every minute "On the minute", displays the data in Sheet1, and saves the data to an *.csv file. I am using Application.OnTime to run the VBA code each time Seconds = 00.
The problem is, I would like to allow the user to enter a message along with the data (whenever necessary). This can easily be done by them typing a message into a cell, and the VBA code saving the message, along with the data.
But, while the user is in the cell and typing, the Application.OnTime will not execute. If the user takes to long, I will miss data. That's not a problem if it's only two or three minutes. But if the operator walks off before hitting enter, no data will be collected from then on.
Is there an interactive way that doesn't interfere with Application.OnTime? I've tried using a second EXCEL spreadsheet to do the message, but it still interrupts the code in my original Sheet1 (even if I start another instance of Excel)

 

[NoSparks]

You might try using a worksheet event like SelectionChange to bring up a small user form with a text box and a command button to write back to the target cell.

 

[mzinab]

You might try using a worksheet event like SelectionChange to bring up a small user form with a text box and a command button to write back to the target cell.

It's been a while since I tried to resolve this, and I believe I tried that. But I will try it in the next day or so, just to be sure. THANKS!

 

[Anthony47]

In addition to NoSparks' suggestion...

I don't know which is your full process, but suggest the following approach:
-You shall work with a worksheet protected with the option UserInterFaceOnly:=True , with no access from the user to the cells, even no selection.
-When the OnTime macro starts it collects the data and then open a userform with one TextBox, a Label containing for example "Add your comment, but rush because the form will close in 30 seconds", and a "Done" commandbutton.
-The UserForm_Initialize should schedule starting a macro to close the form in 30 seconds:

Private Sub UserForm_Initialize() 
ufOn = True
Application.OnTime Now + TimeValue("00:00:30"), "CloseTheForm"
End Sub

The Commandbutton click event macro:

Private Sub CommandButton1_Click()
ufOn = False
DataToSend = "Comment: " & Me.TextBox1.Text & vbCrLf & DataToSent
Unload UserForm1
Call TheMacroThatSendsTheData
End Sub

In a standard vba module you insert

Public ufOn As Boolean                'This line MUST BE on top of the Module

Private Sub CloseTheForm()
If ufOn = False Then Exit Sub
DataToSend = "NoComment" & vbCrLf & DataToSent
Unload UserForm1
Call TheMacroThatSendsTheData
End Sub

Note that the "Public" instruction should be at the top of the Module, before any Sub or Function.

In other words the data are collected, then you wait max 30 seconds for the comments to be inserted in the TextBox; at the end of this timeout the data are sent without comments. Or they are transmitted along with the comment when the CommandButton is pressed.

Of course the code need to be adapted to your situation.

Hth, bye.