stopping macros

[craggers]

Hi, I got a problem, I got a spreadsheet that I update, but I want a time on the sheet that I can update every time I make changes to the spreadsheet. How would I be able to make a button to update my time . I have already tried the =now() function but every time I open up the spreadsheet, it updates the time, which is not what I want. Any help would be appreciated, thanks



craig

 

[elgringo56]

Put the formula in a cell, say cell R10. =NOW()
Make your macro say
Range("R10").Select
Selection.Copy
Range("R2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Cell R2 will only be updated when you want it to be

 

[elgringo56]

Cell R2 Should be formated as Time witht he method of display you wish to see.

 

[Nimrod]

If you want it to auto update EACH time you make a cell change you can do it with these two lines of code

DateTimeValue = Date & " " & Time
If Not (Target = DateTimeValue) Then Range("A1").Value = DateTimeValue

Put these two lines of code in the "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" of your workbook.

This is a built in routine that runs every time you modify the workbook. To find the procedure: 1)open up the procedure editor , 2)select the "ThisWorkBook" object from the pane of the editor, 3)Select "workbook" from editors drop downs and select "sheetchange".
4) add the two lines of code.
After this everytime you change any cell in the workbook the Date will change automatically.

Note: The cell "A1" can be changed to any cell that you want the date in.

 

[Nimrod]

If want the date to change only on command then open the procedure editor and insert following code:

Public Sub ChangeDate()
Range("A1").Value = Date & " " & Time
End Sub

Once this code is inserted then just go to macro editor and assign a shortcut key. After that any time you want a date change just use the assigned shortcut key.

 

[Ivan F Moala]

On 2002-05-04 16:59, Nimrod wrote:
If you want it to auto update EACH time you make a cell change you can do it with these two lines of code

DateTimeValue = Date & " " & Time
If Not (Target = DateTimeValue) Then Range("A1").Value = DateTimeValue

Put these two lines of code in the "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" of your workbook.

This is a built in routine that runs every time you modify the workbook. To find the procedure: 1)open up the procedure editor , 2)select the "ThisWorkBook" object from the pane of the editor, 3)Select "workbook" from editors drop downs and select "sheetchange".
4) add the two lines of code.
After this everytime you change any cell in the workbook the Date will change automatically.

Note: The cell "A1" can be changed to any cell that you want the date in.

Also note: You may need to explcitly set your
sheet to place this Datetimevalue otherwise
it will place the Date time in any sheet
you change @ A1....if this is what you want
then it's OK.