Hiding toolbars hide Calc Sheet tabs

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
zabolyx
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Hiding toolbars hide Calc Sheet tabs

Post by zabolyx »

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

Code: Select all

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

Post by zabolyx »

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

Re: Hiding toolbars hide Calc Sheet tabs

Post by Charlie Young »

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

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

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
zabolyx
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Post by zabolyx »

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

Re: Hiding toolbars hide Calc Sheet tabs

Post by Charlie Young »

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

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

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

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

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
zabolyx
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Post by zabolyx »

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

Re: Hiding toolbars hide Calc Sheet tabs

Post by Charlie Young »

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
zabolyx
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Post by zabolyx »

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

Re: Hiding toolbars hide Calc Sheet tabs

Post by Charlie Young »

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

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

for i = 0 to UBound(lElements,1)
	MsgBox(lElements(i).ResourceURL)
next i
That may be deleted.
Apache OpenOffice 4.1.1
Windows XP
zabolyx
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Post by zabolyx »

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

Re: Hiding toolbars hide Calc Sheet tabs

Post by Charlie Young »

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 911 times
Apache OpenOffice 4.1.1
Windows XP
zabolyx
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Post by zabolyx »

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

Re: Hiding toolbars hide Calc Sheet tabs

Post by Charlie Young »

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

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
zabolyx
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Hiding toolbars hide Calc Sheet tabs

Post by zabolyx »

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/
Post Reply