+ Post New Thread
Results 1 to 7 of 7
Scripts Thread, Now() copy & paste in Coding and Web Development; I am trying to self teach VBA, but before I have got up to speed a request has come in ...
  1. #1

    Join Date
    Nov 2012
    Location
    Bradford
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Question Now() copy & paste

    I am trying to self teach VBA, but before I have got up to speed a request has come in for a script and I am not quite getting it right.

    We are using a barcode scanner, which then populates then next blank cell in column A - as this populates there are some lookups, and a Now() formula to insert the date and time of the scan - however, this obviously then changes each time a new scan takes place. Therefore I need a macro that when cell A* is populated, it then selects the date and time and pastes as values so that it is not overridden.

    I think it should be a fairly simple code for you more experienced VBA users to do the select, copy paste and loop it - so if anyone can advise while I continue trying to work it out I would be grateful for the help.

  2. #2


    Join Date
    May 2009
    Posts
    2,908
    Thank Post
    259
    Thanked 770 Times in 585 Posts
    Rep Power
    270
    I'm curious how the barcode scanner populates the next blank row. It suggests that code would be a good candidate to modify to populate the current date/time into the appropriate column.

  3. #3

    Join Date
    Nov 2012
    Location
    Bradford
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I'm not a programmer, more of a front end data user to I don't know the exact background of it - but the context of it is each student has a barcode in their planner, printed from SIMS that is their Admission number. The scanner reads this and inserts ~000000, then automatically drops to the cell below (assuming you haven't moved the selected cell in between).
    The process works fine in our library for speeding up the process of logging books in and out, but now that they want to use it for students arriving late in the morning a date and time stamp is vital and manually typing them in doesn't make the process much quicker than just scribbling them down.

  4. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,446
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707
    Sounds like the spreadsheet is interfacing with the scanner via VBA. Open up the VB editor in Excel (Ctrl+F11) with the spreadsheet open and take a look. It's going to be much simpler to edit that code than try to patch something else on top of it. Have to say though, if you don't really understand what's happening yet, it's going to be difficult for anybody to help you without seeing how it works for themselves.

  5. #5


    Join Date
    May 2009
    Posts
    2,908
    Thank Post
    259
    Thanked 770 Times in 585 Posts
    Rep Power
    270
    Or it could be as simple as pressing return and excel moves the cursor down, but code needs to be checked.

    If I were stating from scratch, I think I'd look at the Worksheet_change event, which is triggered by any change, to any cell. That would allow the barcode reader to trigger an insert of the date/time. It will all get a bit messy if people mistreat it - one of the disadvantages of trying to use excel as a database.

  6. #6

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,446
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707
    Quote Originally Posted by pcstru View Post
    Or it could be as simple as pressing return and excel moves the cursor down, but code needs to be checked.
    Either way, if the OP doesn't know what's happening, it's going to be damn near impossible for anyone to help without seeing it first hand.

  7. #7

    Join Date
    Apr 2008
    Location
    Keighley, West Yorks
    Posts
    320
    Thank Post
    50
    Thanked 49 Times in 43 Posts
    Rep Power
    21
    Quote Originally Posted by LosOjos View Post
    Either way, if the OP doesn't know what's happening, it's going to be damn near impossible for anyone to help without seeing it first hand.
    The barcode scanners we used a couple of years ago simply behaved as a keyboard; it reads a number, types it into the active cell, then presses enter (itself), which moves the cursor to the next row (assuming Excel is set to do this).

    This matches up with what the OP is saying, so I propose we go on that basis. The operator simply scans barcode after barcode after barcode and ends up with a list of student IDs as follows:

    012345
    373482
    272489
    etc.

    I've not used events in VBA so I can't help with this aspect of it, but once someone comes up with the right event, getting the VBA to insert date/time into ActiveCell(R[-1],C[+1]) should be easy enough. (Maybe worksheet_change as has been suggested, but I don't know what options there are)

    Peter

SHARE:
+ Post New Thread

Similar Threads

  1. IE7 + Copy Picture + Paste Into Word
    By ahuxham in forum Office Software
    Replies: 14
    Last Post: 12th November 2010, 03:15 PM
  2. Copy and Pasting - Excel
    By synaesthesia in forum Office Software
    Replies: 4
    Last Post: 14th January 2009, 10:48 PM
  3. Replies: 2
    Last Post: 4th December 2008, 11:23 AM
  4. preventing copy and paste
    By mikeymike in forum Windows
    Replies: 0
    Last Post: 6th October 2008, 10:19 AM
  5. Office 2000 crashing when copying and pasteing
    By ricki in forum General Chat
    Replies: 0
    Last Post: 1st May 2008, 01:48 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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