[Solved] Grid Lines, via macro

[martius]

How can we hide and show the sheet grid lines, via macro, in CALC?

For example, in Excell, we can do this:

ActiveWindow.DisplayGridlines = False

Thanks!

SEE THE LAST POST. I HAVE AN EXTENSION!!!

[Charlie Young]

How can we hide and show the sheet grid lines, via macro, in CALC?

For example, in Excell, we can do this:

ActiveWindow.DisplayGridlines = False

Thanks!

Code: Select all

ThisComponent.CurrentController.ShowGrid = True/False

[martius]

Ok, It works, thanks.

The problem is that it shows or hides the Grid Lines for all sheets, but i'd like just for the Current Sheet.

How can we hide the Grid Lines just for the Current Sheet, via macro?


SEE THE LAST POST. I HAVE AN EXTENSION!!!

[Charlie Young]

Ok, It works, thanks.

The problem is that it shows or hides the Grid Lines for all sheets, but i'd like just for the Current Sheet.

How can we hide the Grid Lines just for the Current Sheet, via macro?

When (and how) do you look at a sheet that is not current?

[martius]

Let's assume we have a Calc File with sheet1, sheet2 and sheet3. Well, I´d like to hide the grid lines just for the sheet2, for example. So, how can I hide the sheet2 grid lines, without afect the grid lines of sheet1 and sheet3 ???
Thanks!

[Villeroy]

Use a default cell style with borders instead of the grid.
oSheet.CellStlyle = "myBorderlessCellStyle"

or the other way round without borders in "Default" and
oSheet.CellStlyle = "myCellStyleWithBorders"

[martius]

It will be much more practical, if we could have a macro to hide the grid lines just for the sheet we need.

In excell, when we use "ActiveWindow.DisplayGridlines = False", it affects just the active window. Not the all sheets, like in calc.

[Villeroy]

It is obvious that the property belongs to the CurrentController rather than the current sheet.

[martius]

If I understood it right, there is NO WAY to do what I nedd, in calc.

SEE THE LAST POST. I HAVE AN EXTENSION!!!

[Villeroy]

Bordered styles.

[Zizi64]

martius,

you can control visibility of the PRINTED gridlines differentially in the PageStyles (F11).

[Charlie Young]

If I understood it right, there is NO WAY to do what I nedd, in calc.

There is a way, using macros. Whether it's worth the trouble is another question.

Use a selection change listener checking for the active sheet.

Set it up

Code: Select all

Global oListener As Object
Global oDocView As Object

'run this macro to start event intercepting
Sub Setup_SelectionChangeListener
	oDocView = ThisComponent.CurrentController
	
	'create a listener to intercept the selection change event
	oListener = CreateUnoListener( "SelectChange_", "com.sun.star.view.XSelectionChangeListener" )
	
	' register the listener to the document controller
	oDocView.addSelectionChangeListener(oListener)
End Sub

As usual, you need routines for all the listener methods, but the main one here is for selectionChanged

Code: Select all

Sub SelectChange_selectionChanged(oEvent)
	Dim oCurrentSelection As Object
	Dim oSheet As Object
	
  	oDocView.removeSelectionChangeListener(oListener)
  	'the source property of the event struct
  	'gets a reference to the current selection
   	oCurrentSelection =  oEvent.source
   	
   	if oCurrentSelection.SupportsService("com.sun.star.sheet.SpreadsheetView") then
   		oSheet = oCurrentSelection.ActiveSheet
   		ThisComponent.CurrentController.ShowGrid = not HideGrid(oSheet.RangeAddress.Sheet)
   	endif
 	
   oDocView.addSelectionChangeListener(oListener)
End Sub

I'm using a function called HideGrid, wherein is specified which sheets to hide. To hide on only Sheet2 (which has index 1), do

Code: Select all

HideGrids = Array(1)

and put the indices of whatever other sheets to hide in the Array.

The full HideGrid:

Code: Select all

Function HideGrid(Sheet As Integer) As Boolean
	Dim HideGrids
	Dim i As Long
	Dim u As Long
	Dim HideIt As Boolean
	
	HideGrids = Array(1)
	u = UBound(HideGrids)
	HideIt = False
	i = 0
	do while i <= u and not HideIt
		if Sheet = HideGrids(i) then
			HideIt = True
		else
			i = i + 1
		endif
	loop
	
	HideGrid = HideIt
End Function 

The whole ball of wax, started by running Setup_SelectionChangeListener. You can stop it with Remove_Listener.

Code: Select all

REM  *****  BASIC  *****
Option Explicit

Global oListener As Object
Global oDocView As Object

'run this macro to start event intercepting
Sub Setup_SelectionChangeListener
	oDocView = ThisComponent.CurrentController
	
	'create a listener to intercept the selection change event
	oListener = CreateUnoListener( "SelectChange_", "com.sun.star.view.XSelectionChangeListener" )
	
	' register the listener to the document controller
	oDocView.addSelectionChangeListener(oListener)
End Sub

'run this macro to stop event intercepting
Sub Remove_Listener
  ' removes the listener
  	oDocView.removeSelectionChangeListener(oListener)
End Sub

'all listeners must support this event
Sub SelectChange_disposing(oEvent)
  msgbox "disposing the listener"
End Sub

Sub SelectChange_selectionChanged(oEvent)
	Dim oCurrentSelection As Object
	Dim oSheet As Object
	
  	oDocView.removeSelectionChangeListener(oListener)
  	'the source property of the event struct
  	'gets a reference to the current selection
   	oCurrentSelection =  oEvent.source
   	
   	if oCurrentSelection.SupportsService("com.sun.star.sheet.SpreadsheetView") then
   		oSheet = oCurrentSelection.ActiveSheet
   		ThisComponent.CurrentController.ShowGrid = not HideGrid(oSheet.RangeAddress.Sheet)
   	endif
 	
   oDocView.addSelectionChangeListener(oListener)
End Sub

Function HideGrid(Sheet As Integer) As Boolean
	Dim HideGrids
	Dim i As Long
	Dim u As Long
	Dim HideIt As Boolean
	
	HideGrids = Array(1)
	u = UBound(HideGrids)
	HideIt = False
	i = 0
	do while i <= u and not HideIt
		if Sheet = HideGrids(i) then
			HideIt = True
		else
			i = i + 1
		endif
	loop
	
	HideGrid = HideIt
End Function 
	
		

You may invert the words "Hide" and "Show" and the boolean values to suit your tastes.

[martius]

Thank you very much, Charlie Young! Your macro is absolutely fantastic!

Thank you, everybody else for your colaboration!

SEE THE LAST POST. I HAVE AN EXTENSION!!!

[martius]

Inpired on the Charlie's macro, I've got the same thing using the names of the sheets and the PropertyChangeListener checking for the active sheet name. For my needs, it works better.

Code: Select all

Dim oListener As Object
'-----------------------------------------------------------------
Sub setListenerOn
REM run this macro to start event intercepting

oListener = createUnoListener ("SHEET_","com.sun.star.beans.XPropertyChangeListener")

ThisComponent.CurrentController.addPropertyChangeListener("ActiveSheet",oListener)

End Sub

'-----------------------------------------------------------------
Sub SHEET_propertyChange(oEvent)
REM in this macro, you put the sheets name you want to hide grid lines

Dim oSheet as object
oSheet = ThisComponent.CurrentSelection.SpreadSheet

	Select Case oSheet.Name
		
		Case "martius" 'let's assume we hide the grid lines of a sheet called martius
		ThisComponent.CurrentController.ShowGrid = False
		
		Case "charlie" 'let's assume we hide the grid lines of a sheet called charlie
		ThisComponent.CurrentController.ShowGrid = False
		
		Case Else
		ThisComponent.CurrentController.ShowGrid = True			
	
	End Select
End Sub
'-----------------------------------------------------------------

SEE THE LAST POST. I HAVE AN EXTENSION!!!

[martius]

If you prefer, here you are another macro that gets the same result, using sheets name and PropertyChangeListener :

Code: Select all

Dim oListener As Object

'-----------------------------------------------------------------
Sub setListenerOn
'run this macro to start event intercepting

oListener = createUnoListener ("SHEET_","com.sun.star.beans.XPropertyChangeListener")
ThisComponent.CurrentController.addPropertyChangeListener("ActiveSheet",oListener)

End Sub
'-----------------------------------------------------------------
Sub SHEET_propertyChange(oEvent)

oActiveSheet = ThisComponent.CurrentController.ActiveSheet.name

HideGrideSheet = Array("martius","sampa")'in this Array, put the sheets name you want to hide grid lines
'let's assume we'll hide the grid lines of 2 sheets called "martius" and "sampa"

u = uBound(HideGrideSheet)
	
	For i = 0 To u
	
		If oActiveSheet = HideGrideSheet(i) Then
			ThisComponent.CurrentController.showGrid = False
			i = u + 1
		Else
			ThisComponent.CurrentController.showGrid = True
		End If
	
	Next

End Sub
'-----------------------------------------------------------------

SEE THE LAST POST. I HAVE AN EXTENSION!!!

[martius]

Hi,
I have got a better macro that permitted me to create an extension.

This extension allows you to hide or show the grid lines on a sheet of a Calc file with multiple sheets, without affecting the other sheets, just by clicking a button, just the way Excel does.

You do NOT need to write anything inside the macro. All you need to do is to click the button and the active sheet grid lines will be hidden or shown. Even if you go to another sheet, every time you go back to the sheet you changed, it will be in the way you left it. By the way, you can change how many sheets you want.

Get it from the repository for extensions in:
http://extensions.services.openoffice.o ... /GridLines

If you are looking for the same I was then I know you will like it!

martius