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

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

End Sub


"Dorothy Robitalle" skrev:

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

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.

--
Regards,
Tom Ogilvy

"Dorothy Robitalle" <Dorothy wrote in
message ...
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

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

"Kjeldc" wrote:

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

End Sub


"Dorothy Robitalle" skrev:

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

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


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"Dorothy Robitalle" <Dorothy wrote in
message ...
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