No announcement yet.

Calculate sheet and sheet of in Excel 2010

  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculate sheet and sheet of in Excel 2010

    I finally got things figured out (with some help here) regarding the formula and function code to number the sheets in my workbook as 1 of 3, 2 of 3, 3 of 3 etc. Now..... when I move the file over to Excel 2010 I error out. The formulas work fine in Excel 2003 XP. The Error Note indicates "Invalid Name Error" and when I to to help on error or Error Checking Options, I still do not know what I need to do. Do I need to give the cells on the first worksheet a Name?

    If someone could explain what Excel is asking me to do, define - I'm ready to be educated! Regards - Lenny2

    On each worksheet there are two cells: for example (A1 is [=SheetPosition()] and A3 is [=GetSheetCount()]

    The code in 'This Workbook' is:
    Function SheetPosition() As Integer
    Application.Volatile True
    SheetPosition = Application.Caller.Parent.Index
    End Function

    Function SheetCount (Optional Worksheets_Only As Boolean) As Long
    If Worksheets_only = True Then
    SheetCount = Worksheets.Count
    SheetCount = Sheets.Count
    End If
    End Function
    Last edited by Lenny2; February 23rd, 2012, 10:10.

  • #2
    Re: Calculate sheet and sheet of in Excel 2010


    Hopefully, you have take the time to read the forum rules, which require that you use code tags any time you post VBA code.
    Please make an immediate edit to your post to add the code tags.

    Easiest way; highlight all of the code then click the button the # symbol.

    If you prefer to manually add the codes, then
    How to use code tags

    your code goes between these tags


    Forum Rules | Message to Cross Posters | How to use Tags