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:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Will the entries be unique? If so you could store the last printed entry and if the next entry (by your scanner) is different than previous then do the print process again.
 

orion2323

New Member
Joined
Dec 20, 2017
Messages
26
Yes, every entry will be unique and the quick print macro prints the data put together on each row, individually on demand, only printing the last row completed. The previous rows + info remain
 

orion2323

New Member
Joined
Dec 20, 2017
Messages
26

ADVERTISEMENT

Anyone ?.
 
Last edited:

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Why does target.value = 2? This doesn't seem like a unique entry
 

orion2323

New Member
Joined
Dec 20, 2017
Messages
26

ADVERTISEMENT

Hello!
That's the value to trigger the quick print action / macro

Unique is the barcode # going into column B (starting at B2) (I thought that's what you had asked)

I scan to B2 (unique entry) then type "2" on C2 (which prints) then type 1 on D2 (which completes the process). Press enter and start entering data on B3, repeat

The only reason I'd like to make this an automated process is to free the user's hands to grab and item, scan code using a desk mounted barcode scanner, allowing excel to capture the barcode, trigger the print, reset, and move to the next scan field (B3)

FYI, what is being printed is information put together using v-lookups, going into a designated print area. Typing "1" on D2, clears this data in order to collect the next v-lookup information after the scan
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Ok thanks for the explanation -- so removing the manually input of "2" and clearing after print:

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=#333333]Worksheets(1).PrintOut[/COLOR]
[COLOR=#333333]Application.Intersect(Target, targCell) = "" ' reset cell to blank, what for next scan[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]
 

orion2323

New Member
Joined
Dec 20, 2017
Messages
26
Thank you for your reply... is this supposed to be "wait" ?
reset cell to blank, what for next scan

Also, for this: Set targCell = Worksheets(1).Range("C2:C100")
The input column is B, so I changed that...

It seems to work, however, the new code is triggering many print jobs not just the completed or scanned row. @ least 50 jobs are being sent to the printer
 
Last edited:

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
The "wait" was the printout, then the clear of the cell. Wasn't supposed to do anything until the next scan. Are the other print jobs blanks, duplicates or what?
I guess I was under the impression you were scanning everything into one cell and clearing it.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,237
Messages
5,600,481
Members
414,383
Latest member
konmtu

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
Top