+ Reply to Thread
Results 1 to 7 of 7

Move back to last active cell + automatically save workbook

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    59

    Move back to last active cell + automatically save workbook

    Good day everybody,
    I have to setup a spreadsheet at work to track who uses our equipment and when it is returned.
    This is what I want/expect the excel spreadsheet to do:

    1. The employee will scan a barcode on a handheld. This will enter the equipment's name in the first row of the spreadsheet and will also add a date stamp to the 4th row (This is when it was signed out.) , and move to the second row.
    2. The employee will then scan their badge and this will record their badge ID in the 2nd row and add their name on the 3rd row. This is accomplished via VLOOKUP.
    3. Prevent them from deleting any data that has been entered.
    4. At the end of their shift, they will find their name in the spreadsheet, scan the barcode on the equipment. This will in turn enter the equpment name in row E and add a date stampin row F. The cursor should then go to the last cell (i.e. in the attached spreadsheet, it'll be cell A5)
    5. At the end of the day save the excel sheet with the date,to a network drive, and open a fresh copy of the same excel file.

    This is what I have accomplished so far:

    1. Enter equipment name + signed out time then move to next row

    Please Login or Register  to view this content.

    2. VLOOKUP function to find employee name:

    =VLOOKUP(B2,Sheet2!A1:B349,2,FALSE)


    3. Prevent users from deleting data. My only problem is that if they highlight 2 cells and press on delete, it doesn't prevent them from deleting the data.

    Please Login or Register  to view this content.
    As I just started using VBA and after countless searches on the net, I am not sure how to implement steps 4 and 5.
    I would be very grateful if someone can help me out.

    Thanks a lot.
    Attached Files Attached Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Move back to last active cell + automatically save workbook

    Hi,

    I think this will take care of the deletion issue:

    Please Login or Register  to view this content.
    Last edited by xladept; 03-17-2013 at 07:37 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    08-30-2012
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Move back to last active cell + automatically save workbook

    Hi XLA,
    This indeed fixed the deleting issue. It also helped me implement part of the sign out step. (I just had to modify your code a little bit - posted it below)
    Now I just need to figure out how to make it go to the last cell and how to save & re-open the workbook.


    Please Login or Register  to view this content.

    Thanks for your help.

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Move back to last active cell + automatically save workbook

    Maybe:

    Please Login or Register  to view this content.
    Will select last activecell and close with changes. I may be wrong but I don't think it's possible/practical for you to reopen with the same macro.

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Move back to last active cell + automatically save workbook

    Hi amar,

    You're welcome - John's code should give you the last cell

    You want to save & reopen the workbook - but you don't need to close it to save it and if you don't close it then you needn't reopen it:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-30-2012
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Move back to last active cell + automatically save workbook

    John & XLA, the reason why I want to reopen the workbook is because I want a fresh copy of the workbook every morning.
    I found 1 way of doing it:

    1. I added the code below to a module. This will save a copy of the sheet on the server.

    Please Login or Register  to view this content.
    2. Schedule the macro to run @ 23.55 everyday. Added this to 'ThisWorkbook'

    Please Login or Register  to view this content.
    3. Then schedule a task in Windows to reopen the excel file @ 23.56.

    As I said, I am a novice @ VBA and if you guys have an easier way to do this, please let me know.


    I might not have been clear enough w/ the 'last cell' explanation. I apologize about that.
    Here it goes: We'll take the attached file as an example.
    (i) The employee will sign out a handheld (Equip5) by scanning the barcode on it. This enters the equipment ID in A4 + date and time in cell D4. The active cell automatically switches to B4. He then scans his badge and the badge ID is entered in cell B4 + his name pops up in C4. => Active cell moves to A5 (waiting for another employee to sign out a handheld).
    (ii) If an employee comes to return the handheld he signed out earlier (i.e. Equip1), he'll find his name, move the cursor to cell E2 and scan the barcode on his handheld. This will enter the equipment ID in cell E2 + date and time in F2. I then want the cursor to go back to cell A5 (kind of waiting for the next person to sign out a handheld)

    Not sure if that made sense to you guys; if not, I'll try to put it in a simpler way.

    Thanks,
    Amar.

  7. #7
    Registered User
    Join Date
    08-30-2012
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Move back to last active cell + automatically save workbook

    Hi guys,
    I fugured it out.

    In Sheet1, I have the following code:

    Please Login or Register  to view this content.


    In 'ThisWorkbook', I have:

    Please Login or Register  to view this content.


    And in module 1 I have:

    Please Login or Register  to view this content.

    I scheduled a task which will bring up a fresh copy of the excel file everyday @ 23.55.01
    I know that this is a longer way of doing it, but it's working for me.


    Thanks a lot for all your help

+ 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