Barcode produces Data + Current Date/Time

[cmmcullough]

Hello all,

I work at a school and I am trying to set something up in Excel where students' names are attached to a barcode. We want to print this barcode on dance tickets. The idea is that when the students leave the dance, someone can scan their barcode and produce their name in an excel spreadsheet along with the date and time that they left.

I have figured out how to create a barcode for each student's name. Everything works perfectly when the barcode is scanned - each name goes into excel on a separate row. I just can't figure out the next step of causing the NOW function to execute as soon as each name is scanned in. Is this possible? Any help is greatly appreciated!

 

[L. Howard]

Hi there and welcome to the forum.

Post your code you are using to get the students name.

Probably something like this to get the date and time:

Range("A" & Rows.count).End(xlUp)(2) = Now

Where A is the column, change to the column next to the where the name is being returned.

Howard

 

[cmmcullough]

Hi L. Howard - thanks for the welcome and your reply. Sorry for the delay, I didn't have access to the workbook over the weekend. I download the barcode generator from the following website: E. Townsend Schmidt: code-39-multiple-barcode-generator-for-excel

The A column is where your write the names
The B column has the formula: =SUBSTITUTE(TRIM(A2)," ","!")
The C column has the formula: ="*"&B2&"*"
The D column is where the barcode is produced using a font that you download

Here are a few pictures if you need a visual:

Where would I post the formula that you suggested? Thanks for you help!

 

[L. Howard]

On the sheet you are displaying the barcode activity, do ALT + f11 and paste this code in the large white space.

These assumptions apply:

The entry of the barcode in column D produces the same enter event as hitting the enter key.
The change event range is limited to column D, rows D2:D100. This is expandable/retractable as needed by changing it in the code.

The date and time will look like this in column E on the row that just got the new barcode in column D. 4/20/2015 9:33:27 PM.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

    Dim KeyCells As Range
    Set KeyCells = Range("D2:D100")
  
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

    Target.Offset(, 1) = Now
    
    End If
    
End Sub

 

[cmmcullough]

It works perfectly! You are awesome!! Thanks so much for your help!

 

[L. Howard]

You are welcome.

Thanks for the feedback.

Howard

 

[cmmcullough]

OK, new problem. I am ready to create the passes with the student's name and barcode using mail merge in Word. However, instead of recognizing the barcode in Column D, Word is picking up the text that the formula ="*"&A2&"*" produces in Column C. Column D (The barcode) simply has the formula =C1, =C2, etc. to change the information in column C into a barcode (I hope this makes sense - if not see images in posts above). My question is this - is there a way to make the Mail Merge feature in Word recognize the Barcode result in Column D instead of the code used to create the barcode. I've tried copying and pasting the barcodes into a new spreadsheet as values, but it still copies the original formula and that's what word recognizes in the mail merge. I am able to copy/paste each individual barcode into the completed mail merge on the individual pages, but it would be so much faster if word created it automatically since there are over 200 records.

 

[L. Howard]

OK, new problem. I am ready to create the passes with the student's name and barcode using mail merge in Word. However, instead of recognizing the barcode in Column D, Word is picking up the text that the formula ="*"&A2&"*" produces in Column C. Column D (The barcode) simply has the formula =C1, =C2, etc. to change the information in column C into a barcode (I hope this makes sense - if not see images in posts above). My question is this - is there a way to make the Mail Merge feature in Word recognize the Barcode result in Column D instead of the code used to create the barcode. I've tried copying and pasting the barcodes into a new spreadsheet as values, but it still copies the original formula and that's what word recognizes in the mail merge. I am able to copy/paste each individual barcode into the completed mail merge on the individual pages, but it would be so much faster if word created it automatically since there are over 200 records.

Hmm, well that is interesting.

I am sorry, I have no response to that regarding the barcode reconversion.

Howard

 

[cmmcullough]

That's ok. I did some research and did not find anything on the topic either. We ended up just copy/pasting the individual barcodes into Word and that worked out fine. Thanks again for your help!