ullrichr
12-16-2019, 09:41 AM
Ok, a novice...
Here is a script I'm working on. It is for a barcode scanner. When they scan a bar code it places it in the Excel selected and enter date and time info in col B, C, and D
It work for the most part but I'm trying to have it check to see if the barcode value placed in the cell is in col A of another worksheet ("Student Roster") to make sure it is a valid entry. If it's not valid clear the cell before it enters the other data and give an error beep. If it is valid play a successful "beep" and enter the date and time info.
Any suggestion would be great.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range
Set rInt = Intersect(Target, Range("A:A"))
If Not rInt Is Nothing Then
'how to?
If Target.Value = Range("A:A") Then
For Each rCell In rInt
Set tCell = rCell.Offset(0, 1)
If IsEmpty(tCell) Then
tCell = Now
tCell.NumberFormat = "mmm dd, yyyy hh:mm:ss AM/PM"
End If
Set tCell = rCell.Offset(0, 2)
If IsEmpty(tCell) Then
tCell = Int(Now)
tCell.NumberFormat = "dddd mmm dd, yyyy"
End If
Set tCell = rCell.Offset(0, 3)
If IsEmpty(tCell) Then
'how to pull just the time
tCell.NumberFormat = "hh:mm:ss AM/PM"
End If
Next
'rest of the IF
Else
Beep
End If
End If
End Sub
Here is a script I'm working on. It is for a barcode scanner. When they scan a bar code it places it in the Excel selected and enter date and time info in col B, C, and D
It work for the most part but I'm trying to have it check to see if the barcode value placed in the cell is in col A of another worksheet ("Student Roster") to make sure it is a valid entry. If it's not valid clear the cell before it enters the other data and give an error beep. If it is valid play a successful "beep" and enter the date and time info.
Any suggestion would be great.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range
Set rInt = Intersect(Target, Range("A:A"))
If Not rInt Is Nothing Then
'how to?
If Target.Value = Range("A:A") Then
For Each rCell In rInt
Set tCell = rCell.Offset(0, 1)
If IsEmpty(tCell) Then
tCell = Now
tCell.NumberFormat = "mmm dd, yyyy hh:mm:ss AM/PM"
End If
Set tCell = rCell.Offset(0, 2)
If IsEmpty(tCell) Then
tCell = Int(Now)
tCell.NumberFormat = "dddd mmm dd, yyyy"
End If
Set tCell = rCell.Offset(0, 3)
If IsEmpty(tCell) Then
'how to pull just the time
tCell.NumberFormat = "hh:mm:ss AM/PM"
End If
Next
'rest of the IF
Else
Beep
End If
End If
End Sub