Having trouble

[TyeJae]

Ok I hope this makes sense.

I am scanning barcodes and that data gets thrown into excel. Th
problem I am having is that people accidentally will scan a wrong labe
and they will have to start all over, so I made a barcode that wil
read "DELETE" when scanned.

So if the person starts scanning and scans 36 items this would fil
cells A1:A36 with data. The person then scans item 37 and realize
they made a mistake so they scan the "DELETE" barcode and that woul
fill cell A38. I would like that to trigger excel to clear cells A3
and A38 and select cell A37 for the next scan.

Is this possible?

Thanks,
TyeJa

 

[Bob Phillips]

It is with VBA. Add this code to the worksheet code module (right-click the
sheet tab, select View Code from the menu, and paste the code in)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Intersect(Target, Range("A:A")) Is Nothing Then
If LCase(Target.Value) = "delete" Then
Target.Offset(-1, 0).Resize(2, 1).ClearContents
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[TyeJae]

This is what the document would look like after 13 scans and all thes
values would be in column A, starting with A1.

VS9297
VS9297
1102307-9779
VS9297
110237BRTWD
1102307-9779
1102307-9779
1102307-9779
VS9297
VS9297
VS9297
VB8892
VB8892

Then now if the 13th scan was wrong the person would then scan th
"DELETE" barcode and it would insert DELETE in the next cell so the
the fill would look like this:

VS9297
VS9297
1102307-9779
VS9297
110237BRTWD
1102307-9779
1102307-9779
1102307-9779
VS9297
VS9297
VS9297
VB8892
VB8892
DELETE

So if any cell from say A1:A300 = "DELETE" then clear the cell that ha
the word DELETE in it and the cell just before that cell. So in thi
case it would be A13 and A14. Then I would like it to activate th
first empty cell in column A.

Does that make more sense?

Thanks,
TyeJa

 

[TyeJae]

Bob,

Do I need to have somthing trigger that VBA? I did exactly like yo
said and when I put delete in one of the cells nothing happens.

Thanks,
TyeJa

 

[Frank Kabel]

Hi
is it feasible to first scan all items and afterward just clear your
range?

 

[TyeJae]

On Error GoTo ws_exit
Application.EnableEvents = False
If LCase(Target.Value) = "delete" Then
Target.Offset(-1, 0).Resize(2, 1).ClearContents
End If
ws_exit:
Application.EnableEvents = True
End Sub

I took out
If Intersect(Target, Range("A:A")) Is Nothing Then
End If

Once I did this it now works but for example if my data reads

A1: 37438437
A2: 46830428
A3: 63452894
A4: 64785900
A5: delete

It deletes the 2 cells but then in this case A6 would be active for th
next scan instead of A4. Like this:

A1: 37438437
A2: 46830428
A3: 63452894
A4:
A5:
A6: (This cell is selected)

Thanks,
TyeJae

BTW thanks for the VBA though!!

 

[TyeJae]

THANK YOU VERY MUCH GUYS!!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If LCase(Target.Value) = "delete" Then
Target.Offset(-1, 0).Resize(2, 1).ClearContents
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
End If
If LCase(Target.Value) = "clear" Then
Columns("A:A").ClearContents
Range("A1").Select
End If
ws_exit:
Application.EnableEvents = True
End Sub

This works perfectly!! I just needed to be put in the righ
direction.

Thanks again,
TyeJa

 

[Bob Phillips]

The problem is that the last used cell is still pointing at the deleted
cells. You could try this technique that Debra Dalgleish has on her site.
BTW I have fixed the intersect problem a missing Not

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If LCase(Target.Value) = "delete" Then
Target.Offset(-1, 0).Resize(2, 1).ClearContents
DeleteUnused Me
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

Private Sub DeleteUnused(wks As Worksheet)
Dim myLastRow As Long
Dim myLastCol As Long
Dim dummyRng As Range

With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)