Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    next worksheet/chart (XL97 sr2 on Win2000/ NT4)

    This has got to be trivial...

    I have some VBA code stored in the Excel Object Sheet, i.e. not in a module. I did it this way originally because the code is:
    1) specific to that page
    2) refers explicitly to another sheet (a chart derived from the data on the sheet in question actually)

    I would now like to generalise the code and put it in a module so that other sheets in the workbook can make use of the code. I've got the first bit sorted by replacing the sheet specific code :
    Worksheets("Sheet1").Range(etc...)
    with
    ActiveSheet.Range(etc...)

    BUT I've hit a brick wall trying to figure out how to generalise the reference to a specific chart:
    With Charts("Chart1").Axes(etc...)

    The structure of my work book is such that the chart in question is always the next sheet in the workbook so I wondered if I could say something like:
    "activate the next sheet" (which will be the required chart)
    With ActiveChart.Axes(etc...)
    "return to the previous sheet" (so you're back where you started from)

    Can anybody offer the appropriate VBA syntax to get me to the next sheet and back? NB I've experimented with Worksheet.Next.Activate but I've not got it right yet.

    Alternatively is there a better way of getting the effect I'm after that ignorance prevents me from seeing?

    stuck

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: next worksheet/chart (XL97 sr2 on Win2000/ NT4)

    <pre>sheets(activesheet.index-1).select</pre>

    will get to PREVIOUS sheet

    <pre>sheets(activesheet.index+1).select</pre>

    will get you to NEXT sheet

    It will give an runtime error if there is NO previous or NO next sheet.
    Steve

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: next worksheet/chart (XL97 sr2 on Win2000/ NT4)

    <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> I knew it would be easy.

    Thank you,

    (un)stuck

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: next worksheet/chart (XL97 sr2 on Win2000/ NT4)

    You are welcome.

    As you probably know, it is ONLY EASY when you know what to do!

    If you do NOT KNOW, then you end up STUCK and Frustrated.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •