+ Reply to Thread
Results 1 to 8 of 8

jump to specific date in cell-range based on last opening of workbook

  1. #1
    Forum Contributor wamp's Avatar
    Join Date
    11-10-2008
    Location
    Norway
    MS-Off Ver
    97, 2000 & 2003 (diff. workstations)
    Posts
    184

    jump to specific date in cell-range based on last opening of workbook

    Hi.
    I'll try to make this post as clean as possible.

    Intro:
    I am trying to create an excel-workbook that will function as a message-board.
    In simplicity, the goal is that when a user opens the workbook, it will display the last messages that's been entered since the last time this user opened the workbook. As there will be many different users

    It's easier to visualise this, so.. the sheet is supposed to look something like this:

    ......A............B.................C
    1 <date> <Message> <officeusername>
    2 01.10.08...test.........George Carlin(RIP)
    3 02.10.08...test2........Pink Floyd

    Problem:
    Now my problems are as follows:
    1: make excel remember WHO you are and when you last logged in
    (have a macro running that records/overwrites this? in a way)

    2: Based on who you are and when you logged in, skip to the first post that was entered on this date.

    let's say that your last login was 03.jan.2008
    Then you log in at 10.jan.2008, then it will automatically browse to 03.jan in the message-sheet, so that you can catch-up with the latest messages.


    Partly-solution:
    my trail of thought is as follows: I have one sheet called "messages" with
    static dates in columnA, the text(messages) in columB and the signature(username) in columnC.
    Then, I create another sheet (datasheet) that stores the usernames of everyone opening the workbook (and put's a static-date on it); that will function as a sortof database on when a user last 'logged in'
    Then at last run a macro or similar at startup that checks who you are, when you last logged inn and finds that date in the "messages" sheet.

    So far, all I've managed to do is create static-date insertion and usernames in the messages-sheet.

    My codes (so far) are as follows:
    code behind the message-sheet to execute a static-date in columnA on doubleclicking an empty cell:
    Please Login or Register  to view this content.
    Then as a module in the workbook, to insert the usernames:
    Please Login or Register  to view this content.
    Now what I've done to auto-insert signatures, I've written the following code in C1:
    Please Login or Register  to view this content.
    The same in C2 (with reference to A2) and so forth.

    (this is a little messy, but it was all i could think of..
    I also tried to insert a sub routine to do this, but it somehow failed:
    Please Login or Register  to view this content.
    )


    I would really appreciate if someone has an idea on what to do next? any codes/formulas for my specific problems or if someone has a better idea on how to make a workbook like this?

    *crossing fingers*

    Kenneth
    Last edited by wamp; 11-14-2008 at 07:25 AM. Reason: title reflected wrong problem

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Attach an example of what you have so far.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor wamp's Avatar
    Join Date
    11-10-2008
    Location
    Norway
    MS-Off Ver
    97, 2000 & 2003 (diff. workstations)
    Posts
    184
    Here is an attachment (it is coded in Norwegian, so the "IF" statements are called "HVIS" but I assume excel interprets that.
    Attached Files Attached Files

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Your list of signatures will change according to who opens the workbook, is that what you intend?

  5. #5
    Forum Contributor wamp's Avatar
    Join Date
    11-10-2008
    Location
    Norway
    MS-Off Ver
    97, 2000 & 2003 (diff. workstations)
    Posts
    184
    The list of signatures change according to who uses it (to write messages).
    (I could go for the environ("UserName") variable, but the code UserNameOffice seems to work well as all the users have their own regisered version of office, so that part seems to be working.)

    what i intend is to have excel track when a user last opened the workbook.




    I imagine that I have to make another sheet in the workbook that automatically adds a timestamp (staticdate) and the user name when opening the workbook..

    then insert a macro or subroutine that checks to see if the username exists allready in this sheet, and then jump to that date in the "messages" sheet.


    You open the workbook, and it automatically adds your today's date+your name:

    ........A............B
    1 01.01.08....user1
    2 01.02.08....user2
    3 01.03.08....user3
    4 01.03.08....user2 <---Then here it will recall that "user2" was last logged in on 01.02.08 according to cell B2 and A2, and then it will open the "messages" sheet and jump to this date



    I don't know how to make excel run a macro at startup; - creating theese staticdates and usernames automatically, but my biggest problem is to make a routine for excel to check when the user was last logged in.
    I assume it's a cell.range something-something, but I'm unfortunately not a great programmer.

    I would assume it is supposed to be something like this:
    Please Login or Register  to view this content.
    Last edited by wamp; 11-10-2008 at 11:25 AM.

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    In your example you have used the username function as an sheet function, so it updates.

    This will log users
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor wamp's Avatar
    Join Date
    11-10-2008
    Location
    Norway
    MS-Off Ver
    97, 2000 & 2003 (diff. workstations)
    Posts
    184
    Hi.
    Sorry for the late reply, as I've been away from my computer the last days.
    The code you provided works perfectly for logging user-logons

    The workbook is finally starting to take shape.

    I now have one sheet for logging user-logons, and another sheet for writing the messages.
    The remaining problem is to make excel remember when your -last- logon was and them find that date in the 'messaging-sheet'

    I assume there's a simple formula for checking the dates agains each-other but I have no idea even where to begin looking.
    I Tried to make a code like this:

    Please Login or Register  to view this content.
    However it doesn't work, and I don't even know if I am on the right track?

    I've attatched the spreadsheet so that it will be easier to see what I hope to do.
    Attached Files Attached Files

  8. #8
    Forum Contributor wamp's Avatar
    Join Date
    11-10-2008
    Location
    Norway
    MS-Off Ver
    97, 2000 & 2003 (diff. workstations)
    Posts
    184
    I recently discovered the sharing of a workbook; that tracks changes made since last save or similar.
    I would assume that this is the right way to go about my problem instead of all the wrong coding I've done so far.

    However, sharing a workbook disables the VBcode and macros..
    is there a way to alter this?

    Teaching the people that are gonna use this that they have to click "Tools - track changes" and then input the criterias simply won't work.
    I'd prefer if excel automatically displayed changes since last opening.


    Any ideas anyone?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1