[Solved] Select & highlight a range in macro

[eeigor]

How to select a range in a macro with highlighting the selected cells on display?
In screenshot 2, the range is selected (and highlighted) with the mouse, in screenshot 1 - with a macro (not highlighted).

Code: Select all

Function getCurrentRegion(oRange As Object)
	Dim oCursor As Object

	oCursor = oRange.Spreadsheet.createCursorByRange(oRange)  '.getSpreadsheet()
	' Expand the cursor into the region containing the cells to which
	' it currently points. A region is a cell range bounded by empty cells.
	oCursor.collapseToCurrentRegion()
	getCurrentRegion = oCursor
End Function

Sub test_CurrentRegion()
	Dim view, sel, rg, addr

	view = ThisComponent.CurrentController  '.getCurrentController() -> ScTabViewObj
	sel = view.Selection  '.getSelection()
	If sel.supportsService("com.sun.star.sheet.SheetCellRange") Then
		rg = getCurrentRegion(sel)
		addr = rg.RangeAddress
		'Call report(addr, "CurrentRegion")
		view.select(rg)  '***selects but not highlights***
	Else
		MsgBox "No single cell or range selected"_
		 , MB_ICONEXCLAMATION, "Selection Error"
	End If
End Sub

[FJCC]

If I have the spreadsheet not the code, as the active display and I use the menu Tools -> Marcos -> Run Macro to run the procedure Dummy, the selected region gets highlighted. Out of curiosity, why do you need to highlight the region?

Code: Select all

Sub Dummy
oSheet = ThisComponent.Sheets.getByName("Sheet1")
oCell = oSheet.getCellRangeByName("A2")
oRegion = getCurrentRegion(oCell)
ThisComponent.CurrentController.select(oRegion)
End Sub

Function getCurrentRegion(oRange As Object)
   Dim oCursor As Object

   oCursor = oRange.Spreadsheet.createCursorByRange(oRange)  '.getSpreadsheet()
   ' Expand the cursor into the region containing the cells to which
   ' it currently points. A region is a cell range bounded by empty cells.
   oCursor.collapseToCurrentRegion()

   getCurrentRegion = oCursor
End Function

[eeigor]

Yes indeed.
Very unexpected because the following code works but doesn't select and highlight the block of cells.

Code: Select all

oRanges = ThisComponent.CurrentSelection.queryVisibleCells()  'or queryEmptyCells()
ThisComponent.CurrentController.select(oRanges)  '***visually nothing happens, the selection is reset***
Msgbox ThisComponent.CurrentSelection.AbsoluteName  '***but everything is correct here, we have multiple selection***

Why do I need it? For additional visual control of the cells selected by the user, where the data from the user will be inserted.
I'm attempting to restore missing functionality in Calc versus Excel.

But is this a mistake of Calc? The subtleties with the "active display" are not clear to me.
In principle, the problem is solved, but there is still a misunderstanding why it does not work when running a macro from the IDE? I have many other questions.
Thanks

UPD: Clicking in the row selection buttons area when the cursor is over the hidden row number and looks like a double-headed arrow restores the selection, and displaying the hidden row shows that it is not selected, which is what was required (queryVisibleCells).
Ok, but selection is lost when running a macro from the IDE.
Moreover, problems with the selection arose when several non-contiguous ranges were selected. In any case, the background of the selected area is not highlighted with color (screenshot 1 above).

The problem has been solved. But for the purpose of debugging the code, is there any command that wakes up the display?

[FJCC]

I don not know of any way to make the Calc display behave as if it was the active display when the IDE is actually active. To avoid clicking through the menus on the Calc sheet to trigger the macro, I wold put a button on the sheet to run the macro directly. That is all I can suggest. Maybe someone else will know of another solution.

[eeigor]

I will wait for other answers. Thanks

[Villeroy]

Code: Select all

Sub Main
rg = thiscomponent.sheets(1).getCellRangeByName("C5:D8")
view = thiscomponent.getCurrentController()
frame = view.getFrame()

REM mandatory
' mri = createUnoService("mytools.Mri")
' mri.inspect(frame)

REM necessary
frame.activate()

REM optional
win = frame.getContainerWindow()
win.toFront()

view.select(rg)
End Sub

[eeigor]

Villeroy, it couldn't be better. Exactly what is needed. Thank you very much.