How to refresh Share workbook in excel someone can see changes

D

Dorothy Robitalle

Share work book in Excel. There are 5 tabs. 4 of the 5 tabs are handle by 4
person. 4 person enter their own workbook (They will enter the numbers). 1 of
5 tab is total the numbers from 4 tabs. I wrote a macro called savefile() and
trigger it under worksheet_change event. The purpose is to save the chnges
every 5 second if there is chagne to the sheet. However, it will not see the
chagnes right away if you are not make chnage to your own sheet. Is there a
way to refresh the workbook?
Here is the code under each worksheet_change event: Please help

Sub SaveFile()

' Author Dorothy Robitaille on 05/04/2006.

Dim PauseTime, Finish, TotalTime
Dim StartTime
Dim CurrentTime
'Set this value to 5 if you want 5 seconds between saves
PauseTime = 5 ' Set duration.
StartTime = Timer 'Set start time

Do While Timer < StartTime + PauseTime
DoEvents
Loop
CurrentTime = Timer
TotalTime = CurrentTime - StartTime
ActiveWorkbook.Save

ActiveWorkbook.RefreshAll
End sub
 
K

Kjeldc

Have you tryed
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub


"Dorothy Robitalle" skrev:
 
T

Tom Ogilvy

Look at Chip Pearson's page on using Application.Ontime

http://www.cpearson.com/excel/ontime.htm

You can use Kjeldc's idea of using the workbook level sheet change event and
in that event, set a public variable (declared in a general module) to
indicate the last time a sheet was edited. In your ontime procedure, if the
last time of change is prior to or equal to the last save, then do nothing
but reschedule the next save. If not, then save the workbook and schedule
the next save.
 
D

Dorothy Robitalle

Thank you Kjeldc,

Yes. I had place the savefile function under the Workbook_SheetChange. It
works for the person who made the changes lst 5 second. The problem is the
person who is not make the chagnes last 5 second. It will not able to see the
changes. I was wondering if there is a refresh function in Excel
 
D

Dorothy Robitalle

Thanks Tom for the responses. I had put my function under the
Private Sub Worksheet_Change(ByVal Target As Range)
SaveFile
End Sub
It works if you make the changes to the sheet. My problem is if you have not
make the changes to the sheet last 5 second, it will not see the chagnes
someone else made the 5 second ago. 4 diferent people can make the chagnes to
their own sheet. I would liket they can see the changes right away. If person
A made the chagnes to the sheet, person A can see changes, however, person
B,C and D can not see the chagnes from person A right away. Person B, C and D
has to do the File ---> Save in order to see the chagns person A made. If it
is possible that person B, C and D can see the chagnes from A and without do
file and save

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top