"document.sheets(x)" query

[michaely]

Instead of using “document.sheets(x)” [replacing the x with what sheet you want targeted, eg 0 for sheet one, etc – is it possible to make it so that it will target the currently active sheet? Eg if sheet four is active id like that to be the one that’s targeted, while if sheet three is active id like that to be the one

[LeroyG]

See @mariosv answer at How do you get a sheet name to display in a cell?.

[jfn]

Hello,
Create your own sheet accessor, then use the result object to get what you’re looking for (see examples in code).

Here’s mine:

Function GetSheet(Optional ByRef pSheetRef As Variant, Optional pDoc As Object) As Object  
'returns a sheet object.  
'Input:  
'-- pSheetRef: (optional) the name or the index of the sheet to process or an initialized sheet object  
'              ex: "Sheet1" or 0 or MySheet  
'              If the sheet reference is missing then defaults to the active sheet.  
'-- pDoc: (optional) the document to process.  
'         Defaults to the current document.  
'Output: the sheet object or Null if not found  
'  
'Usage examples:  
'MySheet = GetSheet() -> current sheet for current document  
'MySheet = GetSheet("Sheet3") -> sheet named "Sheet3" in current document  
'MySheet = GetSheet(1, SomeDoc) -> 2nd sheet in SomeDoc document object  
Dim lo_Sheet As Object
Dim i As Long

If IsMissing(pDoc) Then pDoc = ThisComponent

'initialize the sheet object
If IsMissing(pSheetRef) Then
	'active sheet
	lo_Sheet = pDoc.CurrentController.ActiveSheet
ElseIf IsNumeric(pSheetRef) Then
	'number: sheet index
	i = Fix(pSheetRef)
	If (i >= 0) And (i < pDoc.Sheets.Count) Then
		lo_Sheet = pDoc.Sheets(i)
	End If
ElseIf (VarType(pSheetRef) = 8) Then
	'string: sheet name
	If pDoc.Sheets.hasByName(pSheetRef) Then
		lo_Sheet = pDoc.Sheets.GetByName(pSheetRef)
	End If
ElseIf (VarType(pSheetRef) = 9) Then
	'object: sheet object
	lo_Sheet = pSheetRef
End If

GetSheet = lo_Sheet  
End Function 'GetSheet  

HTH,