Macro assistance required Pt2

  • Thread starter Thread starter Ram
  • Start date Start date
R

Ram

Hi,

I post a question last asking how to do a Workbook_BeforeSave

got the following ( slightly modified by me )


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Worksheets(1).Range("a5").Value = "Last saved on " & Now & " By " &
Application.UserName

End Sub

This does the trick nicely but now I'd like to add more functionality to it.

Can the same be done if the for individual sheets. I.E.

My workbook contains 4 sheets. Using the above on hitting the save button
the Date & Time is saved regardless of which sheet was modified, what I
would like is a Marco on the 'sheet' so that the Date & Time is saved on the
sheet that's was modified. Easy option is to have only one sheet per book
but not practical.

Can it be done and can you assist ?


FIA
Ram
 
The only way I can think of to record which sheet has been changed an
when is to use Work Sheet Change

Which would need to be placed in each Sheet module

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
range("a1").value= "Last saved on " & Now & " By " &
Application.UserName
End Su
 
The bad news is that excel doesn't let you (the programmer) know which worksheet
changed. It only keeps track of whether something in the workbook changed.
 
mudraker > said:
The only way I can think of to record which sheet has been changed and
when is to use Work Sheet Change

Which would need to be placed in each Sheet module

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
range("a1").value= "Last saved on " & Now & " By " &
Application.UserName
End Sub
Click to expand...

Cheers Mudraker

That works ok, once focus is changed to another cell it updates but in doing
so you lose the
UNDO Function :(

Ram
 
<Snip>

Don Guillett said:
Couldn't you just change it to
activesheet.range(etc.
Click to expand...


Cheers, abit late well a lot late.

Activesheet.range

Does the trick nicely.

Thx

Ram
 
Don Guillett said:
glad to help.
In the future, please stay in the original thread.

--
Click to expand...

Sorry Don, but you've lost me with that.

It appears in the same tread on my system directly after your post off the
24/06.

Must admit that I posted that reply thru google from work whether that moved
it outside the original tread I don't know.

Ram.
 
Back
Top