Problem with code used during insertion from scanner

English
#1

hello,

I have a problem with scanning QR codes, tried everything but everytime I scan with a 2D barcode scanner it scans the whole number from QR code, all I need is the last 4 numbers from it. The serial number. pls if you have any formulas or anything

i have this code that works in excel but in libre doesnt

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Only run only column C, on row 2 or below
    If Target.Column = 3 And Target.Row >= 2 Then
'       Trim entry to last four characters
        If Len(Target) > 4 Then
            Application.EnableEvents = False
            Target = Right(Target, 4)
            Application.EnableEvents = True
        End If
    End If

End Sub

Maybe can someone help me write a new one? Thanks a lot

#2

Hello Domco and welcome!
Yes, it’s probably a good idea to completely rewrite this procedure. And since it still needs to be rewritten, let’s think, wouldn’t it be better if we completely change the logic of the algorithm?
What is the program doing now? It runs on every change in the sheet - it doesn’t matter where and what has changed, the code is run. Having received as a parameter the cell or range in which the change occurred, the code first of all checks how many cells have changed and if more than one, then it does nothing. In other words, if something is entered in column C not by a barcode scanner, but, for example, a group of values is copied from somewhere and inserted into column C, then the code will ignore it …
The code then checks the column number and row number of the changed cell. This is different from Excel. VBA counts column and row numbers from 1, so Target.Column = 3 And Target.Row >= 2 really means “in the third column and below the first row”. In Calc this numbering starts from 0. Therefore, the same expression means “in column D starting from the third row”.
Having performed all these checks, the code finally cuts off the last four characters (if any) and replaces the contents of the changed cell with them.

I propose to do otherwise.
Yes, we will run the code on every change in the sheet. In Calc, this is done by assigning a “Content changed” event handler to the sheet.

Set Event
Set Event577×574 27.6 KB

But then I suggest not to check anything. We simply read the contents of all cells in column C into an array, loop through them all (excluding the header cell), cut to the last four characters and immediately write back. It seems a little strange - for the sake of changing one cell, process the whole column. But if there are not very many values in this column - a hundred or two - then the code will work very quickly, not much longer than the original one. What do you think of this code change? Or do you have a million entries in your list? Oh, then it’s really going to be too long…

#3

thanks for your reply, yes this code works great in excel, but I need it for CALC. in calc it doesnt work tho
it says some eror and cant even run. I would like to to have a new code in API. If you could help me i would really I appreciate it

#4

I suggest changing the procedure code like this:

Option Explicit 

Sub onContentChanged(oEvent As Variant)
Dim oSheet As Variant
Dim oCursor As Variant
Dim oRange As Variant
Dim aData As Variant
Dim i As Long 
Rem From the "event" parameter (the object that called the code), we get the current sheet:
	oSheet = oEvent.getSpreadsheet()
Rem The easiest and fastest way to determine the area of the sheet being used is
Rem to create a cursor and give it the command "go to the end of the data"
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
Rem Now we know the number of the last used row - oCursor.getRangeAddress().EndRow
	oRange = oSheet.getCellRangeByPosition(2, 0, 2, oCursor.getRangeAddress().EndRow)
	aData = oRange.getDataArray()
Rem Data from the cells of column C in the array, let's process this: '
	For i = LBound(aData)+1 To UBound(aData)
		aData(i)(0) = Right(aData(i)(0), 4)
	Next i
Rem And return the array back to the range cells:
	oRange.setDataArray(aData)
End Sub

RTrimColumnC.ods (10.8 KB)

#5

I copied the whole code and when i press run it says - wrong number of parameters!
did I do something wrong? Am sorry I am newbie.

#6

Yes, you did the right thing, but you didn’t do everything that was required. Look at the screenshot above. You need to right-click on the sheet tab, select the “Sheet events” context menu item, in the form that opens, assign this macro to the “Content changed” event. After that, there is no need to run the macro manually, this will happen automatically when any cell of this sheet is changed.

#7

wow, thanks a lot. it worked thank you soooooooooo much

#8

one more thing if I want this function in other line than that. what should i change in the code. Thanks

#9

Sorry, I didn’t understand the question. Did you mean “another column”?
Look at this line:

oRange = oSheet.getCellRangeByPosition(2, 0, 2, oCursor.getRangeAddress().EndRow)

This reads as “ask the specified sheet for a range of cells that starts in the second column (column C), in the first row (row 0), and ends in the second column (the same column C) and the last filled row.”
In other words, to process another column, you need to change 2 to the desired number.

#10

thank you. but what if I wanted for 2 column? :joy: am so sorry am really newbie :smiley: thanks for your time

#11

Sorry, I didn’t understand again. For two adjacent columns? For two different columns? For the second column in the sense of “for column B”? It will be easier if you provide sample data - insert a screenshot or add sample data to your post.

#12

yes like copy the code and else not only for C column, but also for D column. you know what i mean

#13

So you meant “execute code for two adjacent columns - truncate the last 4 characters in columns C:D”, right? It’s simple - we can do it in two ways.
The first option is the same that we already have - we will ask the sheet for the data of column C, process them and return them to their place, and then ask the data of column D in the same way, process them and return them to their place.
The second option - we immediately ask for the range of columns C: D, process them all at once and return them to their place. In this case, you need to make changes to the range query like this:

oRange = oSheet.getCellRangeByPosition(2, 0, 3, oCursor.getRangeAddress().EndRow)

(do you see? From column 2 to column 3)

and into the array processing loop. Or like this:

	For i = LBound(aData)+1 To UBound(aData)
		aData(i)(0) = Right(aData(i)(0), 4)
		aData(i)(1) = Right(aData(i)(1), 4)
	Next i

Or like this:

	For i = LBound(aData)+1 To UBound(aData)
		For j = LBound(aData(i)) To UBound(aData(i))
			aData(i)(j) = Right(aData(i)(j), 4)
		Next j
	Next i

(In this case, you need to add Dim j As Long at the beginning of the procedure)

#14

@Lupp edited the subject line:

A topic using the word “code” in two extremely different meanings should clearly distinguish.
The original subject line “Problem with code” wasn’t specific in any way and not understandable to an average user.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.