Go from sheet 1 to sheet 17 (for instance)

Hi, I’d like to put a few buttons on de first sheet that brings me to sheet nr 11 or 24 or… ect. in the same calc-doc.

Thank you

Here is a solution as button:

  1. Set cursor to place, where the button shall appear.
  2. Menu Insert > Hyperlink or short-cut Ctrl+K
  3. In left part select ‘Document’
  4. Click the icon on the right side of ‘Target’ field. That opens a list with sheets names. Never try to enter a target manually, but always use this list.
  5. Select the sheet you want as target and click button ‘Apply’.
  6. In section ‘Further Settings’ select ‘Button’ from the drop-down-list ‘Form’.
  7. In field ‘Text’ enter the text you want to be shown on the button.
  8. Click on button OK.

To edit the button, set ‘Design Mode’ on. That is in toolbar ‘Form Controls’. Of cause you can use the ‘Form Controls’ toolbar and the ‘Controls Properties’ directly too. But I find the way via Hyperlink-Dialog easier to get the needed entries correctly.

hi Regina,
tnx for the quick response.
When I hit the button, the doc opens in Excel and I have no license for it, (the reason why I use Libre).

ps : tnx for the “design mode”-explain

The way I have described it assumes that you have a spreadsheet document in .ods format.

great ! :+1: :+1: :+1:
how does Excel-users see it ?

Where did Excel users come from? :slightly_smiling_face:
If you plan to save the document in .xlsx format, then use the Hyperlink function in this form:

=HYPERLINK("#Sheet4!A1"; "Goto Sheet4")

It works, tnx again.

Now I try to make a pdf from a libre impress. So far so good, but the different hyperlinks in the pdf go to the same webpage.


Hit the F5 key and see.

1 Like

hi Villeroy,
what do you mean

What do you mean, exactly, by “bring me to sheet …”
Suppposing you want to select a specific cell or celllrange (cell ,A1 e.g.), and you can refer to the sheetnames, you best don’t create buttons, but simply use the HYPERLINK() function in a cell with a formula like =HYPERELINK("#MyTargetSheet.A1").
If you actually need to address the sheets by numbers, you will need a small amouint of user code (“macro”). This is also the case if you have more special ideas like “Select the cell range congruent with the currently selected range(s) in this sheet, but in the sheet with its tab 5 places to the right.”

Here is code that I use all the time to compose hyperlinks or indirects from sheet number:

Function SheetName(Optional sheetIndex As Variant)
	Dim Result As Variant
	Dim Row As Integer
	Dim Column As Integer
	Dim Index as Variant

	If IsMissing(sheetIndex) Then
		SheetName = "#" & CVErr(502)
		Exit Function
	ElseIf IsEmpty(sheetIndex) Then	
		SheetName =  "#" & CVErr(502)
		Exit Function
	ElseIf IsNull(sheetIndex) Then
		SheetName = "#" & CVErr(502)
		Exit Function
	End If

	If IsArray(sheetIndex) Then
		ReDim Result(LBound(sheetIndex) to UBound(sheetIndex), LBound(sheetIndex,2) to UBound(sheetIndex,2))
		For Row = LBound(sheetIndex) to UBound(sheetIndex)
			For Column = Lbound(sheetIndex,2) to UBound(sheetIndex,2)
				REM Specifically, empty entries in sheetIndex() will be "", the empty string
				If IsNumeric(sheetIndex(Row, Column)) Then
					Index = sheetIndex(Row, Column) - 1
					If (Index >= 0) And (Index < ThisComponent.Sheets.Count) Then
						Result(Row, Column) = ThisComponent.Sheets(Index).getName
						Result(Row, Column) = CVErr(502)
					End If
				End If
			Next Column
		Next Row
		SheetName = Result
		If IsNumeric(sheetIndex) Then
			Index = sheetIndex - 1
			If (Index >= 0) And (Index < ThisComponent.Sheets.Count) Then
				SheetName = ThisComponent.Sheets(Index).getName
				SheetName = "#" & CVErr(502)
			End If
			SheetName = "#" & CVErr(502)
		End If
	End If
End Function