MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Return to exact previous position?

  Asked By: Erica    Date: Aug 17    Category: MS Office    Views: 1441

As some of you know, I'm working on a VBA program for my wife who
works as a supervisor in the 911 system. The essence of the task is to
process the call handling times, so supervisors can check the calls to
see what reason for a delay, etc.
At the moment, I'm kinda' stuck on something that would seem to be
a rather significant issue for them, though. 911 supervisors get
interrupted rather often . . . so, I have included a "panic button" so
if they are interrupted, they can hit that, and it automatically saves
everything and closes all the windows and also closes Excel . . . that
way, of course, they won't be leaving security level information on
their screen, so if anyone happened to walk into the supervisor's
office, that information isn't just out there for anyone to see.

But, is there a way for them to return to that exact place whre
they were before, when they are done with the interruption? The VBA
code might have them in the middle of a call on a spreadsheet, with
userforms going about several parts. I think if I could just figure
out a way for them to return to the exact line, the exact call they
were working on, then they can call up the relevant userforms from

Can this be done for different supervisors? . . . at this present
location, there are 4 supervisors, so is there a way for me to write
steps for them to go back to the call they were working on?

Any ideas? I haven't figured out a way to do that . . . in fact,
I don't even know where to start.

Thanks in advance to any of you who might try to help on this one.

As, I've said many times . . . this is a great group and I'll do my
best to offer my ideas to all, as well.



8 Answers Found

Answer #1    Answered By: Topaz Ramirez     Answered On: Aug 17

I would suggest that they use WindowsKey-L. This locks the computer and
clears the screen, then when they want to got back to it they put in their
password. It is quick, simple and foolproof. To program  something like that
in VBA would be highly complex and probably would not work as well IMHO.

Answer #2    Answered By: Angie Bennett     Answered On: Aug 17

Something you said about "4 different supervisors".
They're not opening t he file at the same time? are they?
If so, only ONE set of changes will be stored. all others will be discarded.

If they're working  different shifts, then it shouldn't be a problem.

I assume that if the supervisor is called away, and doesn't get back to it
the next day, and in the meantime, another supervisor has been in-and-out
of this, then:
What I would do is: Create a text file on the user's system, under the
user's login ID.
For each "feature" you're wanting to return  write to the file "something"
that you will be able to read later to use as an indicator for returning.
You'll need things like: ActiveCell. Userform displayed, and even
userform values selected... then, simply read through the file and reset
the things as he left them.

Answer #3    Answered By: Ray Lawrence     Answered On: Aug 17

At first let me say that I am simply terrified that you are using
MS-Excel and VBA to work on a project so sensitive. That makes me shake in my

OK so be the case your solution could be something like that:

1) Supervisor working.
2) Supervisor gets interrupted.
3) Supervisor hits the panic button
4) Code behind the button executes.

4a) In a special area, a worksheet in a workbook or an external text file, you
will save the information necessary to locate the call the supervisor was
working on in step 1. Say if the calls are in a worksheets and they are in rows,
and the supervisor is on row 17 on call number 5, then the code for 4a will save
the worksheet name and 17.

4b) When the supervisor returns to work and he/she activates MS-Excel and the
workbook application is launched the code will look at that special area and see
where the supervisor needs to be placed, worksheet name, and row 17 in the above
example, and you are done.

4c) The code will empty the special location, so that the fact that it is not
empty is indicative of an interruption vs. the supervisor leaving for the day,
or finishing the work. So the special area is populated ONLY in the case of
pressing the panic button, ergo being interrupted.

So I hope this is a starting point for you. My advaice is that you can actually
hide the MS-Excel window, vs closing and exiting MS-Excel. Look at the MSKB
http://support.microsoft.com/search/ for help.

Also you could display a Userform with a login and password that is only known
to the supervisor.

As to how to handle multiple supervisors, that depends on answers to these

1) Will supervisor 1 continue from where supervisor 1 finished, or from where
the panic button was pressed. That is if supervisors 1 and 2 are working, and S1
is interrupted, but S2 is still working  then S2 gets interrupted will S1
continue from S1 position  or S2 position?

2) Is this a shared workbook or each supervisor has his or her workbook?

This is why a password log in userform is so nice, it locks the workbook as is.

OK these re ideas, so you can start, if you need any extra help, call again.

Answer #4    Answered By: Chad Bradley     Answered On: Aug 17

I say "WOW!" back to the depth of your suggestions and
questions. Thank you all, indeed. It gives me several different
levels of reponse--as well as forcing me to have to make some
decisions about how it relates to each supervisor.

I checked first on David's suggestion in regards to WindowsKey-L
and I had to call in to ask my wife if that would work and she
wasn't sure if it would make them have to re-logon to their entire
network, etc. which is somewhat time consuming and since they often
get many interruptions during a shift that wouldn't be good. She's
going to check on that. The other problem with it, though is the
multiple supervisor situation, so I don't think locking Windows will
work. I was good to think about, though, and might come in handy
with other situations.

In regard to Paul's comment about them working  at the same
time, that issue had previously been raised between me and the sups,
and while they do work different shifts, they occasionally come in
on their days off to catch up their reports, so at times they will
need to have more than one working at the same time. The data is
sorted by teams, though, so I was planning to save each team sheet
individually. I haven't got to that part yet, so I don't know  how
well that will work . . . they do need overall stats, too, so I've
got to work out a way to integrate the teams for the final monthly

The general idea of the design to put the needed "return to
previous" info into a separate file was somewhat similar from both
Paul and TechnologyMD, so I'm working on doing it that way.

In answer to the questions about where they would go back to,
they are each responsible for a different team, which is already
sorted by worksheet now, so they would each go back to their own
specific location. I'm thinking that the login/password design
would be the way to keep that sorted . . . when each sup logs on,
they would automatically go back to the last call they were working
on. I don't know of a reason that they would need to go anywhere
else, because all the relevant userforms are accessible by command
button to each other, so they can easily move between them.

As for the shared workbook, at this point I had planned to do it
that way, but I'm not sure if that's the best way. I'll have to do
some more thinking on that.

As for hiding the window versus shutting down Excel, I'm not
sure about that, either. At this point, I"m thinking that those
machines are being heavily used, so having too many application
going might bog them down, which doesn't sound like a good idea to
me. So, I'll do some checking on that, too. The other thing would
be,wouldn't that make it hard for another supervisor to log on
(assuming I can get the save part to be separated by teams)?

Answer #5    Answered By: Laurel Collins     Answered On: Aug 17

One more thought, Scott,

If each sup only uses one machine and each machine is only used by one sup you
could usea common file but have a local file on c: drive which kept track of
the working  position.

Answer #6    Answered By: Orville Rodriguez     Answered On: Aug 17

Unfortunately, that's not the case . . . 2 machines are involved and
they are "tag-team" so they grab the nearest one. Also, I just
picked up my wife from work this evening, and she said she tried the
WindowsKey-L and that's not going to work, for the same reason . . .
it locks up the machine, so no one else can use it. Sometimes,
other people besides sups need those machines for brief time
periods, so the window lock and the hidden screen isn't going to be
a good choice.

So, I'm thinking that I'm back to some kind of log
in . . .or . . perhaps temporariy separating the workbook for each
supervisor, so they can work independantly and then compile them
back at the end of the month.

I really appreciate your ongoing thoughts and suggestions. This
is a great group, indeed. Thank you again.

Answer #7    Answered By: Bonifaco Garcia     Answered On: Aug 17

I was also taken with the sensitive application, but, here's my solution

I have a number of things to save that I think of a Preferences. I
set up another, hidden, sheet that holds them.

In the Selection_Change Sub you save the current cell and sheet
location, in the Preference sheet (I use NamedRange Cells) every time
there is a change. If I recall, Target has the cell just exited and
ActiveCell is the one changed to. Then, when you do your emergency
save, it saves right along with the workbook.
When that workbook is opened again; in the Workbook_Open sub, you
get those Preferences and go back (Activate) that sheet/cell.
For multi OPs, you'll need to have some sort of log-in upon
Workbook Open which picks different locations for the Preferences in
the hidden data sheet. This sounds like a UserForm to me

Answer #8    Answered By: Estella Mitchell     Answered On: Aug 17

Also, very excellant ideas . . . I'll try to step thru that tomorrow
and see if I can get it to work with multiple supervisors. Thanks
again for your detailed suggestions.

Didn't find what you were looking for? Find more on Return to exact previous position? Or get search suggestion and latest updates.