MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

make workbook ignore keyboard activity while timer running

  Asked By: Don    Date: Dec 13    Category: MS Office    Views: 1013

Using a VBA timer routine, I am sampling financial data from a
realtime data stream, and then storing and processing them. Any
keyboard activity directed at the worksheets in this workbook, whether
accidental or on purpose, interrupts the timer and the data collection.

Is there any VBA function that can make the workbook impervious to
keyboard/mouse activity and yet let the program's internal
calculations be recorded on the sheets?

I am able to work in other programs while the timer and processing are
running, and want to continue to do so.



14 Answers Found

Answer #1    Answered By: Laurie Lawrence     Answered On: Dec 13

Perhaps set up a user form to initiate the process but leave it showing?

Answer #2    Answered By: Madaniyah Malik     Answered On: Dec 13

Interesting idea.
I do this all the time with VB6, but I can't seem to get this
to work  in Excel.

I have a command button on Sheet1 named cbSTART.
I created a form and changed these properties manually:
-- (Name) -> FormTest
-- Caption -> FormTest
-- BackColor -> Light green

How do I get the form to appear? This is what I tried...

Sub cbSTART_Click()
-- FormTest.Top=500
-- FormTest.Visible = True <<---------- crashes here
End Sub

Answer #3    Answered By: Essie Garza     Answered On: Dec 13

"How do I get the form to appear?" <== if i understood this question right then
i can prefer this:

Sub cbSTART_Click()
End Sub

Answer #4    Answered By: Eleanor Hunt     Answered On: Dec 13

The good news is that .Show worked ! Thanks
The bad news is that once the form is ".shown", the
Application.OnTime function  suspends (until the form is
closed). Thus, this is a dead-end.

Thnaks anyway.

Answer #5    Answered By: Nahal Malik     Answered On: Dec 13

And what about the other idea? I mean forcing the user only choose Range("A1") ?

Answer #6    Answered By: Billy Evans     Answered On: Dec 13

I have experienced the same problem. I'm using the
the following type of syntax

Sub MainTimer()
-- TmrTime = Now + TimeValue("00:00:01")
-- Application.OnTime TmrTime, "Sheet1.MainTimer", EndTime
-- >> other code <<
End Sub

The sub constantly calls itself (once per second) until
EndTime is reached (defined elsewhere). If I begin typing
something in any cell on Sheet1, the MainTimer stops
working until I press the <enter> key. I don't have
a solution to prevent this from happening.

In the VB6 world, where everything is form-based, I've
used something like this:

Sub Form_KeyPress(KeyAscii As Integer)
-- If isBLP Then
---- Exit Sub
-- End If
-- >> other code <<
End Sub

If the variable isBLP evaluates to True, the sub is exited.
Otherwise, the keyboard  entries are processed. I'm afraid
that I don't know of a parallel in Excel when you are
"sheet-based" or "application-based".

Anyone else have any ideas?

Answer #7    Answered By: Isam Bashara     Answered On: Dec 13

At the beginning of your routine  enter

Application.EnableCancelKey = xlDisabled

One warning, if there is an error in a loop you want be able to stop the

Answer #8    Answered By: Volker Fischer     Answered On: Dec 13

I assume that you meant "won't", and not "want" (hehe).

Although that wasn't what I was after, it gave me an
idea -- use Application.OnKey

The brute force method would be something along the lines

Application.OnKey "a", ""
Application.OnKey "b", ""
Application.OnKey "c", ""
Application.OnKey "d", ""
.. etc.

This disables each of the keys "a" to "d", one at a time
(note, it is case sensitive, so "A" is still live)

A more elegant way would be:

For ii = 33 to 128
-- Application.OnKey Chr(ii), ""
Next ii

The above will disable any printable character. You could
possibly expand the scope of the loop to handle others.
However, navigation keys and such have specific references
(check out "onkey" on VBA help), although I doubt that they
are giving a6 any problems.

It seems that this loop can go in any sub that gets executed
before the timer  sub begins.

Answer #9    Answered By: Jack Williams     Answered On: Dec 13

As I read through all the good suggestions, it occurred to me that if
I could just stop any selection of a cell on any of the worksheets
with a mouse click -- i.e., not be able to select or focus on a cell
or cells, that should take care of the problem. Is that possible?

Answer #10    Answered By: Victoria Hughes     Answered On: Dec 13

Good idea. Then i prefer this;

Choose a cell which has not important data. I assume "A1" is this one.

Then put this in the program:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

That means when the user choose a Range or Ranges it will be forced to choose
onle the Cell "A1". He can just change the data  in "A1" .

Is it close to what you're looking for?

Answer #11    Answered By: Ramond Fischer     Answered On: Dec 13

Does this stop only the Cancel Key or any key/mouseclick that focuses
on a cell in the workbook?

I have found that selecting a cell on any of the worksheets  while the
timer or routine  is running  may or may not interrupts the timer. I can
select another sheet and have no problem, I can scroll slowly through
a sheet with no problem. A mouse click in any cell, even an empty one,
will sometimes interrupt the timer; accidently entering something from
the keyboard  into a cell always interrupts the timer. Since I want to
work elsewhere on the desktop while the program is running, I am
looking for a way to make  the sheets impervious to any external entry.

Answer #12    Answered By: Cedric Sanders     Answered On: Dec 13

I know this is not exactly what you want but very simple:

ActiveWorkbook.ActiveSheet.Visible = xlSheetHidden

Now the sheet is invisible. Son no Entry from keyboard  or mouse can reach on

Answer #13    Answered By: Edjo Chalthoum     Answered On: Dec 13

Good idea, but I need to see the results of my processing  as they are
posted to the sheets. So, I want to sheets to be impervious to
external interruption, not invisible.

Answer #14    Answered By: Beau Smith     Answered On: Dec 13

If i didn't understand wrong then i think this helps;

During the process just activate a little userform which has label like
"Please wait during the Calculation Process" and leave Userform's ShowModal
property "true". As you know, that means the sheet is visible but unreachable
during the Userform is active.

I think somebody already gave this advise,didn't he?

Anyway, i like simple solutions and hope this simple one works

Didn't find what you were looking for? Find more on make workbook ignore keyboard activity while timer running Or get search suggestion and latest updates.