Make Combobox Change Sheets

[jibloty]

Hi Everyone, I’ve used Excel in the past but have since moved to Libreoffice in an effort to only use opensource software. I am liking it enough so far but I am having real trouble with the Appache BASIC side of things. I never knew VBA either but being that Excel is so widely used I could usually just search for what I needed and somebody had already written the code and posted it online.

I’m currently undertaking a large update to my financial spreadsheet, and being that my financial situation is somewhat complicated I use a good number of different sheets within the spreadsheet. Therefore, I would like to put a combobox (don’t want to use data validation for this) on the top corner of all my sheets so I can change from one to the other in a quick easy fashion.

I’ve been searching for some code for this, but can’t find anything that helps. I’ve also been going through the wiki’s and other things but can’t get my head around how the code works. Since this is the only code I’ve needed for this spreadsheet in 10 years and will probably ever need I don’t really want to spend many weeks trying to learn Appache BASIC from scratch because I will only forgot it all again, and I have little time to learn in the first place.

So, I’m wondering if there is some kind soul here that can direct me to an example of the code that I am looking for, or perhaps help me write it up?

I’ve attached my spreadsheet to make it easier to understand.temp.ods

[mauricio]

You only need add two lines of code in event Changed in your control.

Sub change_sheet(event)
	sheet = ThisComponent.Sheets.getByName(event.Source.SelectedItem)
	ThisComponent.getCurrentController().setActiveSheet(sheet)
End Sub

You can autoload all names sheets in every control in every sheet, with next code:

Sub auto_load_sheets()
	On Error Resume Next
	
	doc = ThisComponent
	names = doc.Sheets.ElementNames
	
	For Each sheet In doc.Sheets
		dp = sheet.DrawPage
		If dp.Count > 0 Then
			lst = sheet.DrawPage.getByIndex(0)
			lst.Control.StringItemList = names
		End If
	Next
	
End Sub

The code assume that your first control in every sheet it’s a ListBox

I attach example file
example.ods

[jibloty]

Mauricio, thank you very much that is exactly what I was looking for. There is however one more small thing. Is it possible to clear the selection of the listbox after I click on which sheet I want to go to? For example, on sheet1 if I click on the dropdown and select sheet2, it takes me to sheet2 but when I go back to sheet1, sheet2 is still selected in the listbox. This prevents me clicking on it again to go back to sheet2. I hope I’m not asking too much of you, you’ve been a great help so far.

[mauricio]

Change macro change_event

Sub change_sheet(event)
	lst = event.Source
	current = ThisComponent.CurrentController.ActiveSheet.Name
	sheet = ThisComponent.Sheets.getByName(lst.SelectedItem)
	lst.selectItem(current, True)
	ThisComponent.getCurrentController().setActiveSheet(sheet)
End Sub

Best regards

[jibloty]

You’re an absolute champion Mauricio. Thank you so much for your help. Everything works fantastic now.