iLogic: How to get Item Number of ActiveSheet in Sheets Object?

[j.pavlicek]

Hello,
is there a smarter way how to get an Item Number of particular Sheet object in Sheets Object?

Only way what I have found yet is:

Dim oDoc As DrawingDocument
Dim oSheet As Sheet

oSheet = ActiveSheet.Sheet
oDoc = oSheet.Parent

Dim oSheet2 As Sheet
Dim iSheetIndex As Integer = 0
For Each oSheet2 In oDoc.Sheets
	iSheetIndex += 1
	If oSheet Is oSheet2 Then Exit For
Next
Logger.Info("Index = " & iSheetIndex)

I think there has to be more elegant way how to do it.

Thanks.

[JhoelForshav · Accepted solution]

Hi @j.pavlicek 

Something like this maybe? 🙂

Dim oSheet As Sheet = ActiveSheet.Sheet
Dim oDoc As DrawingDocument = oSheet.Parent
'Convert to list to use IndexOf
'List index starts at 0 while indexes of oDoc.Sheets starts at 1, thats why I add +1
Dim oIndex As Integer = oDoc.Sheets.OfType(Of Sheet).ToList().IndexOf(oSheet) + 1
'Check to see that we got the correct sheet
MsgBox(oDoc.Sheets(oIndex).Name)

[NachoShaw · Accepted solution]

i have something similar to Joels but in a function-

Private Shared Function GetSheetNumber(ByVal SheetName As String) As String
        Return SheetName.Substring((SheetName.IndexOf(":") + 1))
    End Function

Then you can call many times like this to get the sheet number-

Dim SheetNo As Double = GetSheetNumber(oSheet.Name)

or

For Each oSheet As oDoc.Sheet in oDoc.Sheets
  MsgBox(GetSheetNumber(oSheet.Name)
Next

or

oDoc.Sheets(GetSheetNumber(oSheet.Name).Activate()

You can calculate the next sheet

oDoc.Sheets((GetSheetNumber(oSheet.Name) + 1).Activate

[j.pavlicek]

Thanks for reply! Your solution seems to be smart. But are you sure that number after ":" always correspond with Sheets.Item(Index)?

I want to be sure of that, to prevent desperate hunt for weird error in future.

PS: Also please correct type of SheetNo variable in your answer to some of integral types 😉
And the fourth example seems like oSheet.Activate with extra steps to me 🙂

[NachoShaw]

Hi

The number after the : will always correspond with the number of the sheet even if some sheets have been excluded from the count, not necessarily with the index. I found personally that catching the actual sheet number instead of the index is more reliable

My bad on the integer types, by nature i always use a Double and as i typed this on my phone, i used a double without thinking..

[j.pavlicek]

---

@NachoShaw wrote:

... I found personally that catching the actual sheet number instead of the index is more reliable

---

Could you please specify, what exactly do you mean by "sheet number"? (iProperty, index in some collection, etc...). Thank you.

[NachoShaw]

sheet numbers extracted direclty from the sheet name

[dba78]

maybe this? 

'iLogic

Dim drw As DrawingDocument = ThisApplication.ActiveDocument 

For i = 1 To drw.Sheets.Count 
If (drw.ActiveSheet Is drw.Sheets(i))	
	MsgBox("index: " & i)
End If 
Next