Automated cell fill (tab and enter functions ?)

orion2323

New Member
Joined
Dec 20, 2017
Messages
26
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!
 
Last edited:


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
 
Last edited:
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Last edited:
Upvote 0
So couldn't you modify your code like this:

Code:
[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.
 
Last edited:
Upvote 0
Better yet....

Code:
[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]
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top