Hiding toolbars hide Calc Sheet tabs

Keyboard macros or custom scripts

Hiding toolbars hide Calc Sheet tabs

Postby zabolyx » Mon Nov 29, 2010 8:01 pm

Using the following code is causing a minor issue for me.

Code: Select all   Expand viewCollapse view
Sub hideallBars_inOOo2( )
    doc = ThisComponent
    frame = doc.CurrentController.Frame
    lmgr = frame.LayoutManager
    lmgr.setVisible(False)
End Sub


But it is also hiding the tabs that allow me to switch between the sheets on the statusbar (which still shows). I can hover over where they should be and click and it will show the tab for a second and even change the sheet but then it goes back into hiding. If I unlock the spreadsheet and click on it it will show back up until the next time I open the sheet.

I must keep the document and sheets locked until a macro needs to write as the people I'm writing this for cannot keep themselves from deleting formulae and formatting.

Any suggestions?
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user

My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
zabolyx
 
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Postby zabolyx » Mon Nov 29, 2010 9:50 pm

At the moment all one has to do is run a macro that unlocks the sheet and document to perform some action and the tabs show up.... So running with this idea I added that into the startup macro that I run that hides the toolbars and runs other various tasks such as setup the listeners and such...

It didn't work. I was hoping it would.
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user

My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
zabolyx
 
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Postby Charlie Young » Tue Nov 30, 2010 12:03 am

I've been experimenting with this using 3.3 (the only thing I have at the moment). It seems to behave a bit differently than you describe. When I use your macro, it hides the status bar but not the tabs, but at first it hides menu bar as well. The menu bar reappears if I click away to something else and then back to Calc.

I changed your macro into a toggle (note the last line):

Code: Select all   Expand viewCollapse view
Sub hideallBars_inOOo2()
   Dim doc As Object
   Dim frame As Object
   Dim lmgr As Object
   
   doc = ThisComponent
   frame = doc.CurrentController.Frame
   lmgr = frame.LayoutManager
   lmgr.setVisible(not lmgr.isVisible())
End Sub


If I then toggle to Visible = True, only the Find bar reappears at first, but again, everything comes back if I click to something else then back.

I find I can coax things into doing what is expected by adding a couple of lines at the bottom:

Code: Select all   Expand viewCollapse view
Sub hideallBars_inOOo2()
   Dim doc As Object
   Dim frame As Object
   Dim lmgr As Object
   
   doc = ThisComponent
   frame = doc.CurrentController.Frame
   lmgr = frame.LayoutManager
   lmgr.setVisible(not lmgr.isVisible())
   doc.CurrentController.Frame.ContainerWindow.Visible = False
   doc.CurrentController.Frame.ContainerWindow.Visible = True
End Sub


However since my difficulties were not identical to yours, I don't know if this will help.

 Edit: Just now noticed that my added two line reproduces your problem! Stay tuned. 
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Hiding toolbars hide Calc Sheet tabs

Postby zabolyx » Tue Nov 30, 2010 12:23 am

On of the biggest hurdles I have to face in this project is getting the code to work on 3.1, 3.2 and 3.3 as our office doesn't upgrade everything at the same time or stick with the same old when need be. I've tried to instill this concept into the IT peeps but they "no" better than me. I'm usually the one they come to when they have a problem... nice.

So we have some machines with 3.1 and 3.2 and I'm sure that once 3.3 is final that will be installed on the newer users machines. "Because that's what's available". And let's not get started on the LibreOffice... I've ran a test with that and it crashes when trying to unlock the document.... I'm wanting to be compatible but... I've got to draw the line somewhere.
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user

My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
zabolyx
 
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Postby Charlie Young » Tue Nov 30, 2010 8:09 pm

An approach I've been exploring is to hide or show the elements individually. The elements are identified by their ResourceURL (strings), and there are normally five of them

Code: Select all   Expand viewCollapse view
private:resource/toolbar/standardbar
private:resource/toolbar/findbar
private:resource/toolbar/formatobjectbar
private:resource/menubar/menubar
private:resource/statusbar/statusbar


A macro to hide all of these is

Code: Select all   Expand viewCollapse view
Sub hideallBars()
   Dim doc As Object
   Dim frame As Object
   Dim lmgr As Object
   Dim LayoutElements(4) As String
   Dim i As Integer
   
   LayoutElements(0) = "private:resource/toolbar/standardbar"
   LayoutElements(1) = "private:resource/toolbar/findbar"
   LayoutElements(2) = "private:resource/toolbar/formatobjectbar"
   LayoutElements(3) = "private:resource/menubar/menubar"
   LayoutElements(4) = "private:resource/statusbar/statusbar"
   
   doc = ThisComponent
   frame = doc.CurrentController.Frame
   lmgr = frame.LayoutManager
   for i = 0 to 4
      lmgr.hideElement(LayoutElements(i))
   next i
End Sub


And to show them again just change hideElement to showElement.

Code: Select all   Expand viewCollapse view
Sub showallBars()
   Dim doc As Object
   Dim frame As Object
   Dim lmgr As Object
   Dim LayoutElements(4) As String
   Dim lElements
   Dim i As Integer
   
   LayoutElements(0) = "private:resource/toolbar/standardbar"
   LayoutElements(1) = "private:resource/toolbar/findbar"
   LayoutElements(2) = "private:resource/toolbar/formatobjectbar"
   LayoutElements(3) = "private:resource/menubar/menubar"
   LayoutElements(4) = "private:resource/statusbar/statusbar"
   
   doc = ThisComponent
   frame = doc.CurrentController.Frame
   lmgr = frame.LayoutManager
   for i = 0 to 4
      lmgr.showElement(LayoutElements(i))
   next i
End Sub


Obviously these could be combined into one macro using some kind of hide/show parameter.

A problem here, which I probably haven't explored fully, is that if I run hideallBars, then close Calc even without saving the document via the Close Button or alt+F4, then reopen Calc, all the elements except the menu bar remain hidden, and running showallBars as listed above doesn't restore them. To get them back, one then has to display them individually from the View menu. It is good that the menu bar doesn't remain hidden on reopening, or one would be rather helpless and would have to resort to some drastic steps to restore things.

This version of showallBars seems to fix the close/reopen problem

Code: Select all   Expand viewCollapse view
Sub ShowAllBars()
   Dim doc As Object
   Dim frame As Object
   Dim lmgr As Object
   Dim LayoutElements(4) As String
   Dim lElements
   Dim hasElement As boolean
   Dim i As Integer
   Dim j As Integer
   
   LayoutElements(0) = "private:resource/toolbar/standardbar"
   LayoutElements(1) = "private:resource/toolbar/findbar"
   LayoutElements(2) = "private:resource/toolbar/formatobjectbar"
   LayoutElements(3) = "private:resource/menubar/menubar"
   LayoutElements(4) = "private:resource/statusbar/statusbar"
   
   doc = ThisComponent
   frame = doc.CurrentController.Frame
   lmgr = frame.LayoutManager
   lElements = lmgr.getElements
   for i = 0 to UBound(lElements,1)
      MsgBox(lElements(i).ResourceURL)
   next i
   For i = 0 to 4
      j = 0
      hasElement = False
      do while not hasElement and j <= UBound(lElements,1)
         if lElements(j).ResourceURL = LayoutElements(i) then
            hasElement = True
         else
            j = j + 1
         endif
      loop
      if hasElement then
         lmgr.showElement(LayoutElements(j))
      else
         lmgr.createElement(LayoutElements(i))
         lmgr.showElement(LayoutElements(i))
      endif
   next i
End Sub


I haven't dealt with the issues of protected sheets/documents or versions prior to 3.3. Hopefully these won't be big problems.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Hiding toolbars hide Calc Sheet tabs

Postby zabolyx » Tue Nov 30, 2010 9:20 pm

Thank you dear sir...

Now is that saved in OOo settings, or within that sheet? If it is only in that sheet then I could use this in the prep for release macro I run before finalizing a version which removes any data and settings that the end user doesn't need (they get imported from the older version upon upgrading).

Also when Hiding the toolbar does that also take care of any extension toolbars? I have a few installed but if the end user has different ones than me does that cause issues.
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user

My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
zabolyx
 
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Postby Charlie Young » Tue Nov 30, 2010 10:41 pm

zabolyx wrote:Thank you dear sir...

Now is that saved in OOo settings, or within that sheet? If it is only in that sheet then I could use this in the prep for release macro I run before finalizing a version which removes any data and settings that the end user doesn't need (they get imported from the older version upon upgrading).

Also when Hiding the toolbar does that also take care of any extension toolbars? I have a few installed but if the end user has different ones than me does that cause issues.


You're welcome.

I have been just working in a sheet, though I suppose it could be made to work either was. For the extension bars, I don't have any handy, but I would suggest using LayoutManager.getElements, MRI, or XRay to see if you can find the ResourceURLs. If they are under LayoutManager.Elements (listed are just elements 0-4), you should be able to handle them as above.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Hiding toolbars hide Calc Sheet tabs

Postby zabolyx » Tue Nov 30, 2010 10:51 pm

Right I can get the ones I have in my setup no problem but what about the end user's... see my problem. I need to have the program work for 7 people in my company that I can't keep track of every extension one installs.

If all else fails I might try setting up a dialog box that comes up as part of the start-up macro that forces the user to click a button that runs a macro... seems like a pain but the locking and unlocking of the sheets in the startup alone didn't work but running a macro after everything is up and running does. I'll also try that method and see what it gets me.
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user

My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
zabolyx
 
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Postby Charlie Young » Tue Nov 30, 2010 11:37 pm

zabolyx wrote:Right I can get the ones I have in my setup no problem but what about the end user's... see my problem. I need to have the program work for 7 people in my company that I can't keep track of every extension one installs.

If all else fails I might try setting up a dialog box that comes up as part of the start-up macro that forces the user to click a button that runs a macro... seems like a pain but the locking and unlocking of the sheets in the startup alone didn't work but running a macro after everything is up and running does. I'll also try that method and see what it gets me.


hideallBars could be written to deal with a local machine using getElements. The following should work identically to the above, it just reads the ResourceURLs from the local configuration instead of assigning them explicitly

Code: Select all   Expand viewCollapse view
Sub hideallBars2()
   Dim doc As Object
   Dim frame As Object
   Dim lmgr As Object
   Dim LayoutElements
   Dim i As Integer
   
   doc = ThisComponent
   frame = doc.CurrentController.Frame
   lmgr = frame.LayoutManager
   LayoutElements = lmgr.getElements
   for i = 0 to UBound(LayoutElements, 1)
      lmgr.hideElement(LayoutElements(i).ResourceURL)
   next i
   
End Sub


The problem here is restoring the toolbars to their original state. You would need to store the LayoutElements array, perhaps in a sheet somewhere, then read it back in when you are done. You could also store it in a global array maybe, or a local text file. I'd probably do the latter, as it would be the least likely thing for a user to mess up.

Incidentally, in my second version of showallBars above I carelessly left in a MsgBox loop I had in for testing

Code: Select all   Expand viewCollapse view
for i = 0 to UBound(lElements,1)
   MsgBox(lElements(i).ResourceURL)
next i


That may be deleted.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Hiding toolbars hide Calc Sheet tabs

Postby zabolyx » Wed Dec 01, 2010 4:37 pm

Charlie... you've been a stud... that sounds grand.

My spreadsheet has a sheet dedicated to storing variables and settings that the user sets. So this is more than feasible.

Would I tie that macro into the Close Document or the Document is Closing events?
And how would the system handle the crash of OOo? I would know that reopening the sheet would allow me to safely close it and restore the toolbars in the process. My users will not.
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user

My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
zabolyx
 
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Postby Charlie Young » Wed Dec 01, 2010 5:41 pm

zabolyx wrote:Charlie... you've been a stud... that sounds grand.

My spreadsheet has a sheet dedicated to storing variables and settings that the user sets. So this is more than feasible.

Would I tie that macro into the Close Document or the Document is Closing events?
And how would the system handle the crash of OOo? I would know that reopening the sheet would allow me to safely close it and restore the toolbars in the process. My users will not.


The attached spreadsheet has a hideElements macro tied to Document Open and showElements to Document is going to be closed. I'm writing the ResourceURLs to Sheet1 column A, but they could go anywhere, as you'll see if you inspect the macros. Since it also hides the menu bar, it can be helpful to have a Basic IDE window open while playing with this.
Attachments
HideThemBars.ods
Hide/Show Layout Elements
(10.52 KiB) Downloaded 642 times
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Hiding toolbars hide Calc Sheet tabs

Postby zabolyx » Thu Dec 02, 2010 12:49 am

Well... POOP

Trying out your spreadsheet.... I'm finding it is hiding the Menubar, but not the Formulabar or the added extensions toolbars.

But on the plus side the sheet tabs are still showing :) I'll try tinkering around for a few and see what else I can break
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user

My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
zabolyx
 
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Postby Charlie Young » Thu Dec 02, 2010 3:35 pm

zabolyx wrote:Well... POOP

Trying out your spreadsheet.... I'm finding it is hiding the Menubar, but not the Formulabar or the added extensions toolbars.

But on the plus side the sheet tabs are still showing :) I'll try tinkering around for a few and see what else I can break


Evidently neither the Formulabar nor the extension toolbars are LayoutManager elements. I can't find an API property or method to deal with the Formula bar, but it can be handled with the dispatcher. Add calls to ShowFormulaBar to hideElements and showElements with ShowBar = False and True respectively.

Code: Select all   Expand viewCollapse view
sub ShowFormulaBar(ShowBar As Boolean)

   dim document   as object
   dim dispatcher as object

   document   = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

   dim args1(0) as new com.sun.star.beans.PropertyValue
   args1(0).Name = "InputLineVisible"
   args1(0).Value = ShowBar

   dispatcher.executeDispatch(document, ".uno:InputLineVisible", "", 0, args1())

end sub


Another catch is with having showelements tied to Document is going to be Closed. If the document has been modified, then the user will be offered the chance to cancel the close, then the all elements will be displayed with the document still opened. If showelements is tied to Document Closed however, and it is stored in the document, then it can't run because the file it is in is closed! To solve that one, either put showelements in MyMacros, or put doc.store(True) in showelements, which will suppress the Save/Cancel dialog.

I haven't a clue about the extension toolbars at this point, and I don't have any to play with.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Hiding toolbars hide Calc Sheet tabs

Postby zabolyx » Thu Dec 02, 2010 9:51 pm

Well thanks for the insightful info... it looks to be just another thing I'll have to live with in the end.

I'll look into the changes for a future version of my program and hopefully better methods will be included in OOo in the future.
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user

My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
zabolyx
 
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests