Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: stopping macros

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •