[Solved] Grid Lines, via macro

Discuss the spreadsheet application
Post Reply
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

[Solved] Grid Lines, via macro

Post by martius »

:D
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!!!
Last edited by martius on Mon Mar 21, 2011 10:08 pm, edited 3 times in total.
LibreOffice 6.2.8.2 (x64), Windows 10 Home
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Grid Lines, via macro

Post by Charlie Young »

martius wrote::D
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
Apache OpenOffice 4.1.1
Windows XP
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Grid Lines, via macro

Post by 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?
:D

SEE THE LAST POST. I HAVE AN EXTENSION!!!
Last edited by martius on Mon Mar 21, 2011 10:07 pm, edited 1 time in total.
LibreOffice 6.2.8.2 (x64), Windows 10 Home
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Grid Lines, via macro

Post by Charlie Young »

martius wrote: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?
:D
When (and how) do you look at a sheet that is not current?
Apache OpenOffice 4.1.1
Windows XP
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Grid Lines, via macro

Post by martius »

:D
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!
Last edited by martius on Tue Mar 08, 2011 9:10 pm, edited 1 time in total.
LibreOffice 6.2.8.2 (x64), Windows 10 Home
User avatar
Villeroy
Volunteer
Posts: 30676
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Grid Lines, via macro

Post by 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"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Grid Lines, via macro

Post by martius »

:D
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.
LibreOffice 6.2.8.2 (x64), Windows 10 Home
User avatar
Villeroy
Volunteer
Posts: 30676
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Grid Lines, via macro

Post by Villeroy »

It is obvious that the property belongs to the CurrentController rather than the current sheet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Grid Lines, via macro

Post by 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!!!
Last edited by martius on Mon Mar 21, 2011 10:07 pm, edited 1 time in total.
LibreOffice 6.2.8.2 (x64), Windows 10 Home
User avatar
Villeroy
Volunteer
Posts: 30676
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Grid Lines, via macro

Post by Villeroy »

Bordered styles.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 10490
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Grid Lines, via macro

Post by Zizi64 »

martius,

you can control visibility of the PRINTED gridlines differentially in the PageStyles (F11).
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Grid Lines, via macro

Post by Charlie Young »

martius wrote: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.
Apache OpenOffice 4.1.1
Windows XP
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

[Solved]Grid Lines, via macro

Post by martius »

:D :super: 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!!!
Last edited by martius on Mon Mar 21, 2011 10:05 pm, edited 1 time in total.
LibreOffice 6.2.8.2 (x64), Windows 10 Home
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: [Solved] Grid Lines, via macro

Post by martius »

:D
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!!!
Last edited by martius on Mon Mar 21, 2011 10:06 pm, edited 1 time in total.
LibreOffice 6.2.8.2 (x64), Windows 10 Home
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: [Solved] Grid Lines, via macro

Post by martius »

:D
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!!!
Last edited by martius on Mon Mar 21, 2011 10:06 pm, edited 1 time in total.
LibreOffice 6.2.8.2 (x64), Windows 10 Home
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: [Solved] Grid Lines, via macro

Post by martius »

:D
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!
:super:
martius
LibreOffice 6.2.8.2 (x64), Windows 10 Home
Post Reply