|
|
I have a simple spreadsheet that has a list of inventory items, and once per
week someone needs to go through the warehouse and count these items. I
assigned a part number and printed barcode labels for each of these items.
In the spreadsheet, column A includes the written name of the part and column
C has the part # which is on the barcode label, and then column D includes
the quantity, which needs to be filled in.
What I'd like to be able to do is be able to scan the labels and have the
correct row in column D (the quantity column) be selected to match that
item....so it would be automatic....scan the label, then type in the
quantity.
What's the best way to go about that?
|
|
You should install this event sub in your workbook(s):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Range("D" & Target.Row).Select
End Sub
Post if you need help to install it!
Regards,
Stefi
„Brian†ezt Ãrta:
[Quoted Text] > I have a simple spreadsheet that has a list of inventory items, and once per
> week someone needs to go through the warehouse and count these items. I
> assigned a part number and printed barcode labels for each of these items.
> In the spreadsheet, column A includes the written name of the part and column
> C has the part # which is on the barcode label, and then column D includes
> the quantity, which needs to be filled in.
>
> What I'd like to be able to do is be able to scan the labels and have the
> correct row in column D (the quantity column) be selected to match that
> item....so it would be automatic....scan the label, then type in the
> quantity.
>
> What's the best way to go about that?
|
|
It looks like I could use a little help on this--I have a book on VBA but I'm
just getting started on it. I'm using Excel 2007, by the way.
OK, so here's what I'm looking at. I right-click on the correct worksheet
tab at the bottom, and then View Code? Then do I just paste that code in
there? Does it matter if I'm in General or Worksheet? I tried it both
ways....but all I'm getting is what it did before....that my scanner inputs
the barcode info in whatever cell is highlighted.
Just to be sure I'm clear....what I want to do is scan, and then have Excel
say "OK, that barcode info matches the info that's in cell C17, so select D17
so the user can input the quantity".
"Stefi" wrote:
[Quoted Text] > You should install this event sub in your workbook(s):
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 3 Then Range("D" & Target.Row).Select
> End Sub
>
> Post if you need help to install it!
>
> Regards,
> Stefi
>
>
> „Brian†ezt Ãrta:
>
> > I have a simple spreadsheet that has a list of inventory items, and once per
> > week someone needs to go through the warehouse and count these items. I
> > assigned a part number and printed barcode labels for each of these items.
> > In the spreadsheet, column A includes the written name of the part and column
> > C has the part # which is on the barcode label, and then column D includes
> > the quantity, which needs to be filled in.
> >
> > What I'd like to be able to do is be able to scan the labels and have the
> > correct row in column D (the quantity column) be selected to match that
> > item....so it would be automatic....scan the label, then type in the
> > quantity.
> >
> > What's the best way to go about that?
|
|
[Quoted Text] > OK, so here's what I'm looking at. I right-click on the correct worksheet
> tab at the bottom, and then View Code? YES!
Then do I just paste that code in
> there? YES!
Does it matter if I'm in General or Worksheet? NO!
I tried it both
> ways....but all I'm getting is what it did before....that my scanner inputs
> the barcode info in whatever cell is highlighted.
>
> Just to be sure I'm clear....what I want to do is scan, and then have Excel
> say "OK, that barcode info matches the info that's in cell C17, so select D17
> so the user can input the quantity".
Scanner shall input the barcode info in whatever cell is highlighted, so you
have to position to the first free cell in column C manually before starting
scanning. Then this event sub selects the adjacent cell in column D waiting
for inputting the quantity, then selects cell in the next row in column C
waiting for scanning the next barcode.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Range("D" & Target.Row).Select
If Target.Column = 4 Then Range("C" & Target.Row + 1).Select
End Sub
Regards.
Stefi
|
|
I tried it again, and here's what I have happening. My list of inventory
parts is 88 rows long, so my first open C cell (C is where the part # list
is) is C89. I put the cursor in cell C89 and scan a barcode. It then enters
the barcode info that was scanned in cell C89 and then highlights cell D89.
Is that they way it's supposed to work, or did I do it wrong?
What I'm hoping to have happen is this.....the barcode I'm testing matches
the information that's in row 27. I'm wondering if I can scan this barcode
and have the code realize that it matches C27, so I want the cursor placed in
D27 where the quantity should go.
I'm vaguely familiar with VB code....it seems that the code is saying "if
the cursor is in column C, go to column D of the same row. If the cursor is
in column D, go to column C of the next row".
Based on my interpretation of the code, also seems to match what it's doing.
If I put the cursor in D90, it inputs the barcode in D90 and then selects
C91.
"Stefi" wrote:
[Quoted Text] >
> > OK, so here's what I'm looking at. I right-click on the correct worksheet
> > tab at the bottom, and then View Code? YES!
>
>
> Then do I just paste that code in
> > there? YES!
>
> Does it matter if I'm in General or Worksheet? NO!
>
> I tried it both
> > ways....but all I'm getting is what it did before....that my scanner inputs
> > the barcode info in whatever cell is highlighted.
> >
> > Just to be sure I'm clear....what I want to do is scan, and then have Excel
> > say "OK, that barcode info matches the info that's in cell C17, so select D17
> > so the user can input the quantity".
>
> Scanner shall input the barcode info in whatever cell is highlighted, so you
> have to position to the first free cell in column C manually before starting
> scanning. Then this event sub selects the adjacent cell in column D waiting
> for inputting the quantity, then selects cell in the next row in column C
> waiting for scanning the next barcode.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 3 Then Range("D" & Target.Row).Select
> If Target.Column = 4 Then Range("C" & Target.Row + 1).Select
> End Sub
>
>
> Regards.
> Stefi
>
|
|
You read the code right, I misunderstood the task. I still don't understand
it perfectly, this piece of code does what I've understood:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C89" Then 'change C89 to cell
reference where you want to input barcode
If Target.Value = Range("C27").Value Then
Range("D27").Select
Else
MsgBox "barcode doesn't match cell C27!"
End If
End If
End Sub
It expects a barcode inputted in cell C89, doesn't do anything if input is
made in any other cell.
If barcode in cell C89 matches the value of cell C27 then it selects D27 for
inputting quantity.
Please, clarify the task in more details, possibly illustrated with an
example, if this still is not what you need!
Regards,
Stefi
„Brian†ezt Ãrta:
[Quoted Text] > I tried it again, and here's what I have happening. My list of inventory
> parts is 88 rows long, so my first open C cell (C is where the part # list
> is) is C89. I put the cursor in cell C89 and scan a barcode. It then enters
> the barcode info that was scanned in cell C89 and then highlights cell D89.
> Is that they way it's supposed to work, or did I do it wrong?
>
>
> What I'm hoping to have happen is this.....the barcode I'm testing matches
> the information that's in row 27. I'm wondering if I can scan this barcode
> and have the code realize that it matches C27, so I want the cursor placed in
> D27 where the quantity should go.
>
> I'm vaguely familiar with VB code....it seems that the code is saying "if
> the cursor is in column C, go to column D of the same row. If the cursor is
> in column D, go to column C of the next row".
>
> Based on my interpretation of the code, also seems to match what it's doing.
> If I put the cursor in D90, it inputs the barcode in D90 and then selects
> C91.
>
>
> "Stefi" wrote:
>
> >
> > > OK, so here's what I'm looking at. I right-click on the correct worksheet
> > > tab at the bottom, and then View Code? YES!
> >
> >
> > Then do I just paste that code in
> > > there? YES!
> >
> > Does it matter if I'm in General or Worksheet? NO!
> >
> > I tried it both
> > > ways....but all I'm getting is what it did before....that my scanner inputs
> > > the barcode info in whatever cell is highlighted.
> > >
> > > Just to be sure I'm clear....what I want to do is scan, and then have Excel
> > > say "OK, that barcode info matches the info that's in cell C17, so select D17
> > > so the user can input the quantity".
> >
> > Scanner shall input the barcode info in whatever cell is highlighted, so you
> > have to position to the first free cell in column C manually before starting
> > scanning. Then this event sub selects the adjacent cell in column D waiting
> > for inputting the quantity, then selects cell in the next row in column C
> > waiting for scanning the next barcode.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Column = 3 Then Range("D" & Target.Row).Select
> > If Target.Column = 4 Then Range("C" & Target.Row + 1).Select
> > End Sub
> >
> >
> > Regards.
> > Stefi
> >
|
|
|