+ Reply to Thread
Results 1 to 16 of 16

Getting date to display after number input

  1. 11-23-2006, 12:01 PM #1
    Hemmiv
    Hemmiv is offline
    Registered User
    Join Date
    11-23-2006
    Posts
    12

    Getting date to display after number input

    Hello to all. Got a bad problem here:

    In A1 i want to enter a 10 digit number like 0110554579
    In B1 I want to have the date to come when i enter the number in A1,

    So I put in B1=IF(A1>1;TIME();"")

    the > sign so that there wouldnt be any numbers in B1 and the "" so there would be a blanc cell and nothing else, nice so far but then I found out that all the time that i started the sheet (open all day) the date would change with the computer date and not stay when I enterd the numbers in A1!

    So My question is dos excel have a formula that I can use so the date wont change with the computer date??

    p.s. if there is any spelling problem dont blame me english not 1 lang,
    thank you so much
    Reply With QuoteRegister To Reply
  2. 11-23-2006, 12:11 PM #2
    Carim
    Carim is offline
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    I would recommend you use an event macro ...
    go the worksheet tab
    right click
    select view code
    and copy code
    Please Login or Register  to view this content.
    HTH
    Cheers
    Carim
    Last edited by Carim; 11-24-2006 at 07:49 AM.
    Reply With QuoteRegister To Reply
  3. 11-23-2006, 12:42 PM #3
    Hemmiv
    Hemmiv is offline
    Registered User
    Join Date
    11-23-2006
    Posts
    12
    Thank you for your fast reply...

    Do you think that there is any other way without useing macro since well to be frank macro is something that I have never used.

    So if you could sugest anything else would be great and by the way man that was a fast reply.

    2 thumbs up.
    Reply With QuoteRegister To Reply
  4. 11-23-2006, 12:51 PM #4
    Carim
    Carim is offline
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Macros are not dangerous ... it is just automating things ...

    A formula would be in cell B1 

    Please Login or Register  to view this content.
    HTH
    Carim
    Reply With QuoteRegister To Reply
  5. 11-23-2006, 01:12 PM #5
    Hemmiv
    Hemmiv is offline
    Registered User
    Join Date
    11-23-2006
    Posts
    12
    Hi.

    This =IF(A1<>"";NOW();"") changed when the date on the computer change date so sorry. any other ideas??

    Thank you.
    Last edited by Hemmiv; 11-23-2006 at 01:15 PM.
    Reply With QuoteRegister To Reply
  6. 11-23-2006, 01:16 PM #6
    Carim
    Carim is offline
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    My initial piece of advice would be the following ...

    1. Turn on the macro recorder ... (Tools Macro Record Macro)
    2. Just execute simple tasks
    3. Go and see (Alt F11) how VBA has "translated" your actions ...

    HTH
    Carim
    Reply With QuoteRegister To Reply
  7. 11-24-2006, 07:00 AM #7
    Hemmiv
    Hemmiv is offline
    Registered User
    Join Date
    11-23-2006
    Posts
    12
    Thank you for all your trouble regarding this. I have tried to run the macro but it allways ends up with the same problem:

    compile error: syntax error
    and then higlights this line

    .Offset(0,1=.Value = Date

    Thank you if you can figure this one out.
    Reply With QuoteRegister To Reply
  8. 11-24-2006, 07:15 AM #8
    Carim
    Carim is offline
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    oops sorry ... again a typo ...

    Please Login or Register  to view this content.

    HTH
    Carim
    Reply With QuoteRegister To Reply
  9. 11-24-2006, 07:44 AM #9
    Hemmiv
    Hemmiv is offline
    Registered User
    Join Date
    11-23-2006
    Posts
    12
    Hello Carim

    Thank you for that, just one stupid question how do you make excel run the macro in the cells?

    I know that aint the smartest question yet but like I sayd macros is something that I have never used so if you could help me with that I would be in your debt.

    Thank you
    Reply With QuoteRegister To Reply
  10. 11-24-2006, 07:52 AM #10
    Carim
    Carim is offline
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Well,

    This particular code is an event macro ...
    To simplify event macros launch themselves as soon as the user changes something in the sheet ...

    Go to worksheet tab
    Right Click
    Select View Code
    Copy code

    that's it ...

    HTH
    Carim
    Reply With QuoteRegister To Reply
  11. 11-24-2006, 12:47 PM #11
    Hemmiv
    Hemmiv is offline
    Registered User
    Join Date
    11-23-2006
    Posts
    12
    the worksheet that im working in dont show options to copy to. what do you recomend and also could you tell me where i can find something about learning macro?

    Thank you
    Reply With QuoteRegister To Reply
  12. 11-24-2006, 12:49 PM #12
    Carim
    Carim is offline
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Press Alt F11
    click on sheet1 module
    then copy code ...

    HTH
    Carim
    Reply With QuoteRegister To Reply
  13. 11-27-2006, 07:46 AM #13
    Hemmiv
    Hemmiv is offline
    Registered User
    Join Date
    11-23-2006
    Posts
    12
    Hi Carim,

    Found out what the problem were the file was under share and then no macro right? if i share it again will the macro be disable?

    And also what do I have to ajust regarding the code to make the macro run not just in say D1 but all D?


    Thank you.
    Reply With QuoteRegister To Reply
  14. 11-27-2006, 08:13 AM #14
    Carim
    Carim is offline
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Please Login or Register  to view this content.
    Save & Close your file with macro, then reopen it and save it as shared ...
    the macro will run but will not ne available for modifications ...

    HTH
    Carim
    Reply With QuoteRegister To Reply
  15. 11-27-2006, 08:45 AM #15
    Hemmiv
    Hemmiv is offline
    Registered User
    Join Date
    11-23-2006
    Posts
    12
    Hi. Thanks for that worked like a charme.

    Can I insert anoter code at the same "code window" or do I have to open another? and if so how?

    I want to use the same code so do I have to make anoter code window or can I but the code in the same window?
    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
    If .Cells.Count > 1 Then Exit Sub
    If Intersect(.Cells, Me.Range("B:B")) Is Nothing Then Exit Sub (I want to make the date also come in "C:C" but how?)
    .Offset(0,2).Value = Date
    End With
    End Sub

    Thank you
    Last edited by Hemmiv; 11-27-2006 at 09:54 AM.
    Reply With QuoteRegister To Reply
  16. 11-27-2006, 09:51 AM #16
    Carim
    Carim is offline
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Glad to see your problem is fixed.

    If Intersect(.Cells, Me.Range("B:C")) Is Nothing Then Exit Sub

    Thanks for feedback

    Carim
    Last edited by Carim; 11-27-2006 at 10:15 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1