Macro to Avoid Scrolling

Creating a macro - Writing a Script - Using the API

Macro to Avoid Scrolling

Postby KeithOO » Thu Nov 14, 2019 9:37 am

I have a simple Spreadsheet which is much too wide to display without scrolling to the right. The columns are shared out to make up 11 sections with various numbers of columns in each. Columns also vary in widths. The format does not normally change from month to month. There are 2 frozen rows for headers and the first column is also frozen.
On the first frozen column, I wish to have a set of buttons (one for each section). By pressing a button the relevant section will display next to the first frozen column and thus avoid manual left/right scrolling.
I have managed to make this work but only if Section 1 button is also pressed, i.e. Section 1 works fine but Sections 2 to 11 require Section 1 button to be pressed first. I have made a Macro for each button which simply clicks on a top cell (below headers) and to the right of the required section.
I wish to have each button work without the need to press Section 1 button first. I also wonder if there is also a professional and more elegant way of achieving this.
Any ideas please. Thanks.
Open Office 4.1.7 MacOS 10.13.6
KeithOO
 
Posts: 71
Joined: Sat Jun 15, 2013 8:34 am

Re: Macro to Avoid Scrolling

Postby Zizi64 » Thu Nov 14, 2019 10:19 am

Please upload your ODF type sample file with the embedded macro code.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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: 8521
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to Avoid Scrolling

Postby KeithOO » Thu Nov 14, 2019 2:35 pm

Sheet attached. Macros are under Finance. Ignore those beginning SORT. Not sure if a smaller or larger screen will give a different result but I think you will understand.
Document is just to help a friend with his finances and hobby business, so not worth spending too much time.
Thanks.
Attachments
FinanceAcSheet.ods
(51.52 KiB) Downloaded 9 times
Open Office 4.1.7 MacOS 10.13.6
KeithOO
 
Posts: 71
Joined: Sat Jun 15, 2013 8:34 am

Re: Macro to Avoid Scrolling

Postby Zizi64 » Thu Nov 14, 2019 3:17 pm

Just a tip:

Why do not use more than one Sheet instead of the macros? The buttons seems and work as the TABs of the sheets, but they are vertically ordered...
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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: 8521
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to Avoid Scrolling

Postby KeithOO » Thu Nov 14, 2019 3:44 pm

Thanks. Regret I don't think we would like to split up the sheet into separate sheets as there are already more sheets in the document and there other practical problems..
Thanks for looking and your thoughts.
Open Office 4.1.7 MacOS 10.13.6
KeithOO
 
Posts: 71
Joined: Sat Jun 15, 2013 8:34 am

Re: Macro to Avoid Scrolling

Postby RoryOF » Thu Nov 14, 2019 4:27 pm

Hide any intermediate columns that don't require input.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29869
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macro to Avoid Scrolling

Postby JeJe » Thu Nov 14, 2019 7:26 pm

You can set the scroll position by accessing the scrollbar directly. The trouble is its a pain finding the scrollbar... but what you do is start with

Thiscomponent.currentcontroller.componentwindow.AccessibleContext

and you go down through the tree of child windows using getAccessibleChild till you find the horizontal scrollbar - and then you can control its scroll position.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 626
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro to Avoid Scrolling

Postby JeJe » Thu Nov 14, 2019 7:55 pm

deleted
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 626
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro to Avoid Scrolling

Postby MrProgrammer » Fri Nov 15, 2019 6:10 am

KeithOO wrote:I have a simple Spreadsheet which is much too wide to display without scrolling to the right. The columns are shared out to make up 11 sections with various numbers of columns in each. Columns also vary in widths. … On the first … column, I wish to have a set of buttons (one for each section). By pressing a button the relevant section will display next to the first … column and thus avoid manual left/right scrolling.
You can do this without writing any macros. Record a macro to display the second section next to the first column.
• Tools → Macros → Record
• Type B1:DB1 in the Name Box and press Enter
• Format → Columns → Show
• Type B1:J1 in the Name Box and press Enter
• Format → Columns → Hide
• Stop recording → Macro name → BankAccounts → Save macro in → select a macro location → Save
Record similar macros for the other sections. Record a macro to show the Summary by showing all the columns.

Create a toolbar with buttons for the various sections:
• Tools → Customize → Toolbars → New → Name → Sections → Save in → select a toolbar location → OK
• Add → Category → OpenOffice Macros → provide macro location specified above → BankAccounts → Add
• Repeat for other macros → Close → OK

Since you will have your buttons on a toolbar you won't need your first column and will have more width on the screen for data.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3893
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests