Listbox for go to any sheet

English
,
#1

Hi.

I’m not used to using macros in Calc.

I have many worksheets in one file:

  • Menu
  • Sheet01
  • Sheet02
    …

In facts, each sheet is named by a person’s first and last name.

From the “Menu” sheet, I’d like to access to any sheet from a listbox (listbox and range of values ok). For the moment, I’ve made a test with a button :

Sub GoMenu
	
	Dim strName As String
	Dim objSheet As Object

    strName = "Menu"
    objSheet = ThisComponent.GetSheets().GetByName(strName)
    ThisComponent.CurrentController.SetActiveSheet(objSheet)
    
End Sub

I have this button at the top of each sheet, so I can return to the menu with a single click.

I’m searching to adapt this macro so that it works with a listbox.

Thanks.

#2

The LO developers have already solved a similar problem. :slight_smile:
Right-click on the :heavy_plus_sign: sign located to the left of the Calc document sheet tabs.

#3

In case you do want to put things on a sheet…say, to filter the tabs listed, etc., here is an example sheet that has some tricks. It uses a macro to fetch a sheet name from an index to create a list of sheets with hyperlinks to them, and shows a droplist (not listbox) with a Go button for jumping to sheets.

Goto Sheet on Sheet.ods (18.7 KB)

#4

Taking advantage of @joshua4’s suggestion, here’s a macro linked to Spreadsheet Event / Changed Content

Goto Sheet on Sheet_GS.ods (17,8,KB)

Improved version, in new spreadsheets include the Menu button.
Goto Sheet on Sheet_GS2.ods (13,3,KB)

#5

Yes, I know, it’s easy but no ergonomic with 50 or more sheets.

#6

joshua4, it’s a good way for me, what I would. Thanks.

schiavinatto, _GS.ods is better. Thanks too.

#7

There is also Sheets | Navigate | Go to Sheet which allows searching by name. You could set a keyboard shortcut to this dialog.

1 Like
#8

Yes, but no enough ergonomic…

#9

Perhaps it was a terribly poor design decision to spread the data across 50 sheets?

#10

Are you referring to a database that would be more suitable? Yes, but this Calc is for someone who’s more at ease with it than a database. :slightly_smiling_face:

#11

Nevertheless, there are design principles that do apply to spreadsheets as well. If you misuse a spreadsheet as database, you should try to mimic a database as far as possible.

#12

Probably, but I have almost no experience of macros in Calc.

#13

Nobody needs any macros if you keep it smart and simple. Splitting across dozens of sheets is the opposite of smart and simple.
A database would be far easier to use, but more difficult to set up.

#14

Sample without any macro. There is not even any formula:
Pivot_Month_Person_Product.ods (77.9 KB)
A1:D1 has column labels.
Subsequent rows have a flat, simple, consistent list with dates below dates, text below text, numbers below numbers.
There is no separate sheet for products nor persons nor months. There is not even any sort order. You can sort that list any way you want.
The header row has auto-filter fields, free list boxes to select years, months, products and/or persons.
The right side shows a pivot table derived from the source data on the left. Normally, you would store pivot tables on separate sheets, so they won’t be filtered when the source data are filtered.

2 Likes
#15

Thanks, but the pivot tables are useless in the file. Moving from sheet to sheet with a simple listbox and returning to the “home sheet” with a button is all the person asked for…

#16

It is just an old sample file of mine demonstrating how to use a spreadsheet as a database surrogate in a clean, efficient manner, so you can use features like the data pilot.
The auto-filter can split your data by products, persons, dates, months, years or any combination of these. For instance, you can get all “Bananas” of month “April 2020” without navigating anywhere.
And yes, it is possible to show aggregations (sum, count, min, max, average etc.) for filtered data.

There is no advantage in splitting data.

#17

I sometimes use pivot tables in accounting operations, for example, which is ideal for this kind of work. But I repeat, for this topic, the person doesn’t want anything more…

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.