Return position of a sheet in a workbook
 

I am using Office 2003 on Windows XP.

I need a function that would return an integer or a long that indicates the
current position of the active sheet, counting the sheets from left to right.

So if you have sheets named: RawData, Reports, Data, and Calcs in a workbook
and "Data" is currently the active sheet, the function should return 3.

Your example code would be most appreciated. Thanks much in advance.

Return position of a sheet in a workbook
 

The following code just picks up the name of the current worksheet and its
index number (its position) and displayes a message box displaying that
information:

Sub WhereAmI()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = ActiveSheet

MsgBox "The active sheet name is " & ws.Name & _
" and its position is " & ws.Index

Set wb = Nothing
Set ws = Nothing

End Sub

--
Kevin Backmann


"XP" wrote:

I am using Office 2003 on Windows XP.

I need a function that would return an integer or a long that indicates the
current position of the active sheet, counting the sheets from left to right.

So if you have sheets named: RawData, Reports, Data, and Calcs in a workbook
and "Data" is currently the active sheet, the function should return 3.

Your example code would be most appreciated. Thanks much in advance.

Return position of a sheet in a workbook
 

try

Sub sheetlocation()
MsgBox ActiveSheet.Index
End Sub

--
Don Guillett
SalesAid Software

"XP" wrote in message
...
I am using Office 2003 on Windows XP.

I need a function that would return an integer or a long that indicates
the
current position of the active sheet, counting the sheets from left to
right.

So if you have sheets named: RawData, Reports, Data, and Calcs in a
workbook
and "Data" is currently the active sheet, the function should return 3.

Your example code would be most appreciated. Thanks much in advance.

Return position of a sheet in a workbook
 

Just a heads up that this will work if all the sheets to the left are
worksheets. If you insert a chart sheet as the first sheet, for example, it
will give you the wrong number.

Probably not a problem in your example, but something you should be aware
of.

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
try

Sub sheetlocation()
MsgBox ActiveSheet.Index
End Sub

--
Don Guillett
SalesAid Software

"XP" wrote in message
...
I am using Office 2003 on Windows XP.

I need a function that would return an integer or a long that indicates
the
current position of the active sheet, counting the sheets from left to
right.

So if you have sheets named: RawData, Reports, Data, and Calcs in a
workbook
and "Data" is currently the active sheet, the function should return 3.

Your example code would be most appreciated. Thanks much in advance.

Return position of a sheet in a workbook
 

Here's a function that returns the index

Function CurrentLocation() As Integer

CurrentLocation = ActiveWorkbook.ActiveSheet.Index

End Function

--
Kevin Backmann


"XP" wrote:

I am using Office 2003 on Windows XP.

I need a function that would return an integer or a long that indicates the
current position of the active sheet, counting the sheets from left to right.

So if you have sheets named: RawData, Reports, Data, and Calcs in a workbook
and "Data" is currently the active sheet, the function should return 3.

Your example code would be most appreciated. Thanks much in advance.