Hi I need to run a macro when a specific range of column (range ) value changed how to do that?
You will need a handler of event Content changed:
Events pass information to the handler about the object that called it (in most cases). This is usually denoted by the parameter oEvent As Variant
Your code may be structured like this:
Sub onCellsChanged(oEvent As Variant)
Dim oSheet As Variant
Dim oColumn As Variant
Dim oMyEditedRange As Variant
oSheet = oEvent.getSpreadsheet()
oColumns = oSheet.getColumns().getByName("B")
oMyEditedRange = oColumns.queryIntersection(oEvent.getRangeAddress())
If oMyEditedRange.getCount() = 0 Then
Print "It is Other Range"
Else
Print "It's My Range"
EndIf
End Sub
Here the line oColumns = oSheet.getColumns().getByName("B") defines the range to check, in this case the entire column B. You can describe your target range in any other way .getCellRangeByName("A2:D40") or .getCellRangeByPosition (0 , 1, 9, 100) or any other. The .queryIntersection() range method will return a set of cells that belong to both the target range and the cells that raised the events.
Update. Two examples of solving the problem in an Scan3.ods file
“oSheet = oEvent.getSpreadsheet()”
The oEvent object passed to an onContentChanged routine may not have a .Spreadsheet property.
The event is also raised if the user cleared (by pressing the ‘Del’ key) a multirange selection. The oEvent object neither supports the com.sun.star.sheet.SheetCell nor the com.sun.star.sheet.SheetCellRange service in this case.
Dear Friend
I’m attaching the file which I have updated sheet event but it is not working correctly.
The result i want.
On event, I need to select the range “c1” and scroll down to end empty cell on every edit on (column “f”) but i have just recorded the macro but not working correctly.
Am attaching the Scan 2.odsfile here can anyone help to correct the code as per the above requirement,
Scan 2.ods
please help friends
It seems to me that you have not formulated your task sufficiently. Why did you decide to move the cursor after editing the cell in column F? It may be better if the first free cell in column C is selected if the cursor is in any empty area - for example, in any cell in column G (Regardless of how the cursor got there - after editing F or after clicking the mouse or after pressing an arrow key).
Dear Thanks for your reply,
This file is going to be used for the material scans. We need to scan 4 barcodes will start from C>D>E>F once the last scan completed we need to move the cursor manually to the next row start from the C column which I’m planning to move through macro since I need to scan around 10k rows per day with macro it will help me to reduce the time of manually moving cursor
Please help for marco code
I supplemented my answer with a file, which contains two options for solving the problem.