Refreshing automatically

[Jessica]

Good afternoon,

I was wondering if there is a few simple commands for refreshing a excel
spreadsheet on a timer, as soon as it is opened. The code would be in the
main spreadsheet and when clients access it from their workstations it would
automatically update itself using the web option refresh on a timer, say 30
minutes.

Is that possible?

Thanks for your help,

Jessica

 

[Pflugs]

Yes, it is definitely possible! Look at Chip Pearson's site on scheduling
procedures.
http://www.cpearson.com/excel/OnTime.aspx

Be careful that your refreshing routines do not overwrite the user's work
without his knowledge.

HTH,
Pflugs

 

[Jessica]

Thank you for the quick assistance, that guide will work great.

We get to move onto Part B of the question, since this will be refreshing
automatically when it is opened, is there a way to make it stop refreshing on
the screen where people are doing the editing if they enter in the password
to edit the Excel file?

IE. 30 clients are refreshing automatically on their own, which is fine and
looks easy to do.

1 of the Leads opens the file, enters the edit password and the timer
doesn't refresh for them.

I'm fairly new when it comes to VBA, let alone using it in a fairly
complicated manner.

 

[Pflugs]

That's a little more interesting. I can't think of a perfect way to do this...

Perhaps you could prompt for a password upon opening the workbook. It the
password is correct, you could offer the option to refresh once. Then, don't
schedule future updates.

However, you should ask yourself how many other people you want to edit this
workbook. If it is only you, for example, it would be easier to keep a
master copy of the file with the update code commented out rather than going
through the work of a password. Then, you could take advantage of the
built-in worksheet and workbook protection methods. You could also be in
charge of updates to the file and control when new versions are distributed.

I would recommend the latter solution. Get your refreshing code working and
keep a master and a distribution copy.

Let me know if this is unclear.
Pflugs

 

[Jessica]

Yes, that is where I am having the trouble. I do have the refresher working
perfectly, on the dot every 30 minutes it updates. I do however have six
people who need to update the "master" file, so there really isn't anyway I
can control the master copy and leave the refresher commented out. I did
consider that option though.

The main problem I am having is since there are six people who can modify
the sheet, would I need to have the password change? Or ask for a password
before allowing them to edit the document and thus stopping the refresh
timer.

The file is on a main drive of the computer, example V: Drive and the six
people can edit it from that main file, everyone else has to check readonly,
so the code for the refresher will work just fine.

Any help would be much appreciated. I already grateful for all the help
you've provided thus far.

Jessica

 

[Pflugs]

I see. In that case, I have two further suggestions.

1. Use a toggle button to help control when the spreadsheet updates itself.
You will find toggle buttons as part of the Controls Toolbox (NOT the Forms
toolbar). The toggle button will act as a "push on, push off" kind of
button. Users would click it to "push" it in (that is, change its value from
FALSE to TRUE). Then, before your refresher code executes itself, it would
check to make sure that button is set to TRUE. Otherwise, it could schedule
a new check every two minutes or so.

I would guess you haven't needed to use a toggle button before, so they can
be confusing. You can look at an example here:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=416

Toggle buttons will also require you to use Event programming. That is, a
macro that runs when the toggle button is clicked (double clicking is also an
event). Events are written in the code module for the sheet they belong to.
Look at the Project Explorer window to find the sheet where your toggle
button is. Many people have help with events.

2. The password could be asked for when the user attempts to click on the
toggle button. Before the button changes its value to TRUE, you could prompt
for a password. I would suggest that you look at this website for
information on how to alter the InputBox so that the password characters are
not displayed.

http://www.xcelfiles.com/API_09.html

This is a lot to throw at you, I know. However, judging from how quickly
you picked up the OnTime programming, I think you'll pick this up quickly,
too.

If you any more clarification, let me know. I could also send you an
example toggle button spreadsheet if you get really stuck.

HTH,
Pflugs

 

[Jessica]

I really do appreciate all the help, I am going to go and play with these
examples of code. I am sure I will be back with more questions, being that
this is kind of a first time attempt at, what I think is complicated, vba.