Automated cell fill (tab and enter functions ?)

[orion2323]

Hello everyone!

I'm back with more questions

So I'd like to know if it's possible to automate TWO steps (key strokes) while keeping the printing macro already in use ?

This is the current macro which enables a quick print every time I complete cell C2 (then C3, C4, and so on)

Sub jumpnext()
Range("B" & ActiveCell.Row + 1).Select
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim targCell As Range
Set targCell = Worksheets(1).Range("C2:C100")


If Not Application.Intersect(Target, targCell) Is Nothing Then
If Target.Value = 2 Then
Worksheets(1).PrintOut
End If
End If
End Sub



I'm scanning a number into cell B2, scanner automatically switches to cell C2, I then complete the cell with a particular value (2, which triggers the print macro) and tab to cell D2 and enter another value (let's say 1) and this completes the entire process. I press ENTER, Excel jumps to the next row, starting in B3


Is there a way to automate this process ?

If so, I'd need a tiny delay from one action to the other, so after scanning to cell B2, the scanner tabs to cell C2 (Autocomplete function here ?) (excel prints) then (Autocomplete function here ?) triggering a value in D2, and finally excel returns to the row below (B3) where I would scan the next barcode

Thank you All!

 

[orion2323]

Oh no, the cells cannot reset as the information on the row needs to remain as a record, which is what D2 does. It resets the printable area, not the row that was scanned

The information is collected on columns B through H. The v-lookups put information on column K, rows 1 through 6. Basically a vertical mirror or the information of the row that is scanned (B through H)
D2 resets the information on K, but not the original "Row"


It appears that "52" jobs are sent to the printer and all are the same job

 

[Roderick_E]

Can you make a cell that collects the most recently scanned unique number, and if the new one is the same as most recent, a popup tells the user to scan another cell. This would act like the manual input of the 2.

 

[orion2323]

I don't quite understand what you asked... Sorry

This is what I originally tried as "automatic" input for the 2 adjacent cells after the scan in B2 (C2, and D2)

=IF(B2<>"",2,"")
=IF(C2<>"",1,"")

Which kind of works, as a formula, but not for my needs

1: If I scan to B2, the formula above works and places a 2 in cell C2, however the formula result does not trigger the print job, it only happens if I actually type "2" (direct input, and I don't understand why)

2: If I could get this to work somehow, then there needs to be a small delay after entering # 2 (to allow the print job) and then continue to place a value on D2, jump to the row below and repeat

It sounds simple in theory, but not so


BTW, I really appreciate the time you have spent on this

 

[Roderick_E]

No worries.
Let me rehearse how I think this is supposed to work. The user has a scanner, to scan a bar or QR code. This looks up the number on the spreadsheet somewhere and collects all the revelant data to the unique/scanned number and then prints this out.

It looks like we need to use Worksheet_Change and Worksheet_SelectionChange.
What I was suggesting is everytime the user scans, it places the scanned number in a specific temporary cell. Your printout process occurs and awaits next scan. When the user scans a new item, it compares it to the temp cell and if it is the same number, a msgbox comes up indicating it. If different, it repopulates the temp cell and does print out. This way, you'd only need to have your change code watch this temp cell and see if it changes, to trigger printout -- like manually entering 2.

 

[orion2323]

Yes, there is another sheet with the master data
The v-lookup pulls the information that is printed

J1 is a cell that can be used as the "TEMP" location
And the scanned data will never repeat, it will always be unique (no need for the checkbox really)

 

[Roderick_E]

So couldn't you modify your code like this:

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]
[COLOR=#333333]Dim targCell As Range[/COLOR]
[COLOR=#333333]Set targCell = Worksheets(1).Range("C2:C100")[/COLOR]


[COLOR=#333333]If Not Application.Intersect(Target, targCell) Is Nothing Then[/COLOR]
[COLOR=#ff0000]If scannednumber<>range("J1") then[/COLOR]
[COLOR=#333333]Worksheets(1).PrintOut
[/COLOR]​[COLOR=#FF0000]range("J1") = scannednumber[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

I don't know where/how you are capturing the scanned number.

 

[Roderick_E]

Better yet....

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]

[COLOR=#333333]if target.address = "$J$1" Then
[/COLOR]if trim(range("J1"))<>"" then
[COLOR=#ff0000]If scannednumber<>range("J1") then[/COLOR]
[COLOR=#333333]Worksheets(1).PrintOut
[/COLOR]​[COLOR=#FF0000]range("J1") = scannednumber[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If
[/COLOR]End If
[COLOR=#333333]End Sub[/COLOR]

 

[Roderick_E]

hmm on second thought, it may keep triggering. How about a reset.

Private Sub Worksheet_Change(ByVal Target As Range)


if target.address = "$J$1" Then
if trim(range("J1"))<>"" then
If scannednumber<>range("J1") then
Worksheets(1).PrintOut
​range("J1") = "" ' set to blank so it won't trigger again
exit sub
End If
End If
End If
End Sub