Save sheet when cell value changes
 

Cam.,

Haven't tried this as I don't have IIS and ASP on this machine, but this
worksheet event code will force a save when a cell changes, but what I don't
know is if your web update will force this event.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
ThisWorkbook.Save

ws_exit:
Application.EnableEvents = True
End Sub


If you want to only do the updates on changes to certain cells, try this mod
for example
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:M100")) Is Nothing Then
ThisWorkbook.Save
End If

ws_exit:
Application.EnableEvents = True
End Sub


What worries me is what happens if the web update flods the page with
changes? Each cell change will trigger a workbook save, and what happens to
the outstanding updates. Anyway, try it and see.

--

HTH

Bob Phillips

"Cam" wrote in message
...
OK, I have a workbook with several sheets, one sheet takes
input from a .asp web page and updates other sheets based
on the values. I DESPERATELY need to save the workbook
evertime a cell value changes (from the .asp web page
input) as currenlty the update occurs but will not save
before the next entry is made from the .asp page. THANKS

Save sheet when cell value changes
 

Well, its an internal thing and likely to be only one
user 'updating' at once so probably won't be an issue.
Sorry to be so dense but is this sub something i add to
the asp or excel? Where would it be added and how is it
called? i REALLY apreciate the help!!
-----Original Message-----
Cam.,

Haven't tried this as I don't have IIS and ASP on this
machine, but this
worksheet event code will force a save when a cell
changes, but what I don't
know is if your web update will force this event.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
ThisWorkbook.Save

ws_exit:
Application.EnableEvents = True
End Sub


If you want to only do the updates on changes to certain
cells, try this mod
for example
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:M100")) Is Nothing
Then
ThisWorkbook.Save
End If

ws_exit:
Application.EnableEvents = True
End Sub


What worries me is what happens if the web update flods
the page with
changes? Each cell change will trigger a workbook save,
and what happens to
the outstanding updates. Anyway, try it and see.

--

HTH

Bob Phillips

"Cam" wrote in message
...
OK, I have a workbook with several sheets, one sheet
takes
input from a .asp web page and updates other sheets
based
on the values. I DESPERATELY need to save the workbook
evertime a cell value changes (from the .asp web page
input) as currenlty the update occurs but will not save
before the next entry is made from the .asp page.
THANKS


.

Save sheet when cell value changes
 

The code works inside Excel PERFECTLY however it doesn't
work with the asp update. It seems the asp page somehow
only leaves its most recent update with no save when it
inserts values into the cells. I have it set to return
the cells to blank or zero when the user leaves each page,
so they enter 6 pieces of data, confirm their entry (on
confirm button the sheet is updated) then taken to another
page (only 2 buttons, enter another or quit) Enter
another updates excel again with blank or zero and they
are taken to the update page where they enter another set
of six values. I have a formula =recall(cell)+cell that
increments with each change in that same cell so that if i
am in excel and enter 1 then 0 then 1 then 0 the cell with
that formula reads 2. The asp page will change the cell
to 1 then 0 then 1 then 0 (if i were to enter 1, go
through the cycle, enter 1 again) and I check the sheet
after each update, it has the values and the formula cell
reflects the SAME values but will not update (no running
total). Anyway sorry to babble and REALLY REALLY
appreciate any additional help. THANKS!!!
-----Original Message-----
It's Excel worksheet event code, so it goes in the sheet
being updated. To
add it
- right-click on the sheet tab
- on the menu select View Code
- in the code pane, copy the code provided

Hopefully that will do it.

--

HTH

Bob Phillips

"Cam" wrote in message
...
Well, its an internal thing and likely to be only one
user 'updating' at once so probably won't be an issue.
Sorry to be so dense but is this sub something i add to
the asp or excel? Where would it be added and how is it
called? i REALLY apreciate the help!!
-----Original Message-----
Cam.,

Haven't tried this as I don't have IIS and ASP on this
machine, but this
worksheet event code will force a save when a cell
changes, but what I don't
know is if your web update will force this event.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
ThisWorkbook.Save

ws_exit:
Application.EnableEvents = True
End Sub


If you want to only do the updates on changes to
certain
cells, try this mod
for example
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:M100")) Is
Nothing
Then
ThisWorkbook.Save
End If

ws_exit:
Application.EnableEvents = True
End Sub


What worries me is what happens if the web update flods
the page with
changes? Each cell change will trigger a workbook save,
and what happens to
the outstanding updates. Anyway, try it and see.

--

HTH

Bob Phillips

"Cam" wrote in message
...
OK, I have a workbook with several sheets, one sheet
takes
input from a .asp web page and updates other sheets
based
on the values. I DESPERATELY need to save the
workbook
evertime a cell value changes (from the .asp web page
input) as currenlty the update occurs but will not
save
before the next entry is made from the .asp page.
THANKS


.



.

Save sheet when cell value changes
 

Well, here's where a DRASTIC skill shortage comes in :( I
can't find how to save excel through ASP and don't know
how. I have used access with ASP before and had very
little trouble as when a record is entered access 'saves'
it. I am handcuffed and have to use excel for this
application. Anyway again THANK YOU for the help!
-----Original Message-----
I thought that might happen.

Could you not have the workbook being saved upon
completion of the asp
update? If so, you could either trap the deactivate event
and switch the
sheet and save it, or have a nominated cell that if you
select it triggers
the save.

--

HTH

Bob Phillips

"cam" wrote in message
...
The code works inside Excel PERFECTLY however it doesn't
work with the asp update. It seems the asp page somehow
only leaves its most recent update with no save when it
inserts values into the cells. I have it set to return
the cells to blank or zero when the user leaves each
page,
so they enter 6 pieces of data, confirm their entry (on
confirm button the sheet is updated) then taken to
another
page (only 2 buttons, enter another or quit) Enter
another updates excel again with blank or zero and they
are taken to the update page where they enter another
set
of six values. I have a formula =recall(cell)+cell that
increments with each change in that same cell so that
if i
am in excel and enter 1 then 0 then 1 then 0 the cell
with
that formula reads 2. The asp page will change the cell
to 1 then 0 then 1 then 0 (if i were to enter 1, go
through the cycle, enter 1 again) and I check the sheet
after each update, it has the values and the formula
cell
reflects the SAME values but will not update (no running
total). Anyway sorry to babble and REALLY REALLY
appreciate any additional help. THANKS!!!
-----Original Message-----
It's Excel worksheet event code, so it goes in the
sheet
being updated. To
add it
- right-click on the sheet tab
- on the menu select View Code
- in the code pane, copy the code provided

Hopefully that will do it.

--

HTH

Bob Phillips

"Cam" wrote in message
...
Well, its an internal thing and likely to be only one
user 'updating' at once so probably won't be an
issue.
Sorry to be so dense but is this sub something i add
to
the asp or excel? Where would it be added and how
is it
called? i REALLY apreciate the help!!
-----Original Message-----
Cam.,

Haven't tried this as I don't have IIS and ASP on
this
machine, but this
worksheet event code will force a save when a cell
changes, but what I don't
know is if your web update will force this event.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
ThisWorkbook.Save

ws_exit:
Application.EnableEvents = True
End Sub


If you want to only do the updates on changes to
certain
cells, try this mod
for example
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:M100")) Is
Nothing
Then
ThisWorkbook.Save
End If

ws_exit:
Application.EnableEvents = True
End Sub


What worries me is what happens if the web update
flods
the page with
changes? Each cell change will trigger a workbook
save,
and what happens to
the outstanding updates. Anyway, try it and see.

--

HTH

Bob Phillips

"Cam" wrote in message
...
OK, I have a workbook with several sheets, one
sheet
takes
input from a .asp web page and updates other
sheets
based
on the values. I DESPERATELY need to save the
workbook
evertime a cell value changes (from the .asp web
page
input) as currenlty the update occurs but will not
save
before the next entry is made from the .asp page.
THANKS


.



.



.