Listbox for go to any sheet

[Primus]

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.

[sokol92]

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

[joshua4]

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)

[schiavinatto]

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)

[Primus]

@sokol92

Right-click on the sign located to the left of the Calc document sheet tabs.

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

[Primus]

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

schiavinatto, _GS.ods is better. Thanks too.

[robleyd]

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