Page 1 of 1

Macro to Avoid Scrolling

PostPosted: Thu Nov 14, 2019 9:37 am
by KeithOO
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.

Re: Macro to Avoid Scrolling

PostPosted: Thu Nov 14, 2019 10:19 am
by Zizi64
Please upload your ODF type sample file with the embedded macro code.

Re: Macro to Avoid Scrolling

PostPosted: Thu Nov 14, 2019 2:35 pm
by KeithOO
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.

Re: Macro to Avoid Scrolling

PostPosted: Thu Nov 14, 2019 3:17 pm
by Zizi64
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...

Re: Macro to Avoid Scrolling

PostPosted: Thu Nov 14, 2019 3:44 pm
by KeithOO
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.

Re: Macro to Avoid Scrolling

PostPosted: Thu Nov 14, 2019 4:27 pm
by RoryOF
Hide any intermediate columns that don't require input.

Re: Macro to Avoid Scrolling

PostPosted: Thu Nov 14, 2019 7:26 pm
by JeJe
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.

Re: Macro to Avoid Scrolling

PostPosted: Thu Nov 14, 2019 7:55 pm
by JeJe
deleted

Re: Macro to Avoid Scrolling

PostPosted: Fri Nov 15, 2019 6:10 am
by MrProgrammer
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.