[Solved] Grid Lines, via macro

Discuss the spreadsheet application

[Solved] Grid Lines, via macro

Postby martius » Sat Mar 05, 2011 3:07 am

: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
martius
 
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Grid Lines, via macro

Postby Charlie Young » Sat Mar 05, 2011 3:40 am

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   Expand viewCollapse view
ThisComponent.CurrentController.ShowGrid = True/False
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Grid Lines, via macro

Postby martius » Tue Mar 08, 2011 8:34 pm

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
martius
 
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Grid Lines, via macro

Postby Charlie Young » Tue Mar 08, 2011 8:55 pm

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
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Grid Lines, via macro

Postby martius » Tue Mar 08, 2011 9:04 pm

: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
martius
 
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Grid Lines, via macro

Postby Villeroy » Tue Mar 08, 2011 9:08 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 29724
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Grid Lines, via macro

Postby martius » Tue Mar 08, 2011 9:19 pm

: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
martius
 
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Grid Lines, via macro

Postby Villeroy » Tue Mar 08, 2011 9:22 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 29724
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Grid Lines, via macro

Postby martius » Tue Mar 08, 2011 9:28 pm

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
martius
 
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Grid Lines, via macro

Postby Villeroy » Tue Mar 08, 2011 9:32 pm

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
Villeroy
Volunteer
 
Posts: 29724
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Grid Lines, via macro

Postby Zizi64 » Tue Mar 08, 2011 10:04 pm

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
Zizi64
Volunteer
 
Posts: 9843
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Grid Lines, via macro

Postby Charlie Young » Tue Mar 08, 2011 11:18 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
HideGrids = Array(1)


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

The full HideGrid:

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

[Solved]Grid Lines, via macro

Postby martius » Wed Mar 09, 2011 1:21 pm

: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

Postby martius » Thu Mar 10, 2011 1:16 am

: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   Expand viewCollapse view
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

Postby martius » Thu Mar 10, 2011 9:35 pm

:D
If you prefer, here you are another macro that gets the same result, using sheets name and PropertyChangeListener :
Code: Select all   Expand viewCollapse view
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

Postby martius » Wed Mar 23, 2011 1:46 pm

: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
martius
 
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil


Return to Calc

Who is online

Users browsing this forum: Villeroy and 19 guests