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.
Macro to Avoid Scrolling
Macro to Avoid Scrolling
Open Office 4.1.7 MacOS 10.13.6
Re: Macro to Avoid Scrolling
Please upload your ODF type sample file with the embedded macro code.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Macro to Avoid Scrolling
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.
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 193 times
Open Office 4.1.7 MacOS 10.13.6
Re: Macro to Avoid Scrolling
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...
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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Macro to Avoid Scrolling
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.
Thanks for looking and your thoughts.
Open Office 4.1.7 MacOS 10.13.6
Re: Macro to Avoid Scrolling
Hide any intermediate columns that don't require input.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Macro to Avoid Scrolling
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.
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.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
- MrProgrammer
- Moderator
- Posts: 4904
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Macro to Avoid Scrolling
You can do this without writing any macros. Record a macro to display the second section next to the first column.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.
• 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, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).