Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Views - only for the active worksheet (Excel 2000)

    I just got Excel 2000 and this problem did not occur in my previous version. I want to set a custom view in worksheet 1, but I do not want the custom view to be "linked" to worksheet 2 within the workbook. I switch between worksheets quite often. Every column in worksheet 2 is a different day, e.g. col A is 9/21, col B is 9/22, col C is 9/23, etc.. When I set the custom view in worksheet 1, a view is created in worksheet 2 showing the information for the day when the view was created, e.g. 9/21. But two days later when I select the view in worksheet 1 and switch to worksheet 2 the column for 9/21 is shown instead of the current day I was in when I switched from worksheet 2 to worksheet 1. How can I stop worksheet 2 from reverting to the column (or day) I was in when I created the custom view in worksheet 1 instead of staying in the column (day) I'm in today? I hope this isn't too confusing. Thanks. Jim

  2. #2
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Views - only for the active worksheet (Excel 2000)

    The settings stored within a Custom View include (among other things) the cells that are selected at the time the view is created.

    Thus, regardless of the sheet that is active when you choose to Show a custom view, the view will show the Active Sheet & selected range at the time when the custom view was created.

    To the best of my recollection, this was how it worked in 97 too, but I am not able to check that at the moment.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Views - only for the active worksheet (Excel 2000)

    Thanks for the comment. I just checked and the Excel I was using before was for Windows 95 Version 7.0. So I was using a really old version. It sounds like I am stuck with having to reset the View in worksheet 1 every so often so that when I switch to worksheet 2 I'll be close to the right day. Any thoughts on how I might get around this?

    Thanks again for taking the time to answer.
    JIm

  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: Views - only for the active worksheet (Excel 2000)

    You could write a macro and out it on Worksheet_Activate (for individual sheets) or Workbook_SheetActivate event (if you want to do it with many sheets), to find the cell in a particular range, closest in date to now and select it. Then whenever you select a new sheet it will select the current (or close to current) date.

    Steve.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Views - only for the active worksheet (Excel 2000)

    Jim:

    This may not be the best answer for your case, as I am not entirely sure what you are trying to achieve with your custom views. However, if it is just a case of wanting to move to a specific place in the w/sheet (once the view is shown), I find named named ranges to be the best way of navigating. (Insert/Name/Define or Insert/Name/Create). I'm a bit pushed for time to give a full explanation at the moment, but post back if (a) you need more guidence or ([img]/forums/images/smilies/cool.gif[/img] you don't think named ranges will be the solution.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  6. #6
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Views - only for the active worksheet (Excel 2000)

    Tony:

    Thanks for the suggestion. This has possibilities. I'll work with it and let you know.

    JIm

  7. #7
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Views - only for the active worksheet (Excel 2000)

    Steve,

    Thanks for your helpful comment. I'm not familiar with the macro language, but I'll see if I can get someone who is to look at your suggestion. It's encouraging.

    JIm

  8. #8
    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: Views - only for the active worksheet (Excel 2000)

    If you give us an idea how your spreadsheet is setup, we might be able to help here. If you can describe the setup and what you would like , we should be able to help with some code.

    Steve

  9. #9
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Views - only for the active worksheet (Excel 2000)

    I'll give it a try. Worksheet 1 has 5 Views so I can move around the worksheet and freeze the heading rows and left side columns the way I need them. View 1 is K23:U251, View 2 is A28:I251, View 3 is A1:G20, View 4 is X1:AP20 and View 5 is X81:AB91.
    Worksheet 2 has the first four columns and first four rows frozen. Starting in column E is a different day, e.g. col. E is 10/6, col. F is 10/7, col. G is 10/8, etc., for entering that day's data. Let's assume I set the Views in Worksheet 1 on 10/6. On Worksheet 2 the first column after the frozen columns is column E or 10/6. But, a week later on 10/13, when I switch from Worksheet 2 to Worksheet 1 and change my view from View 1 to View 2, the view in Worksheet 2 switches to column E or 10/6 instead of staying in the column for 10/13. I have to tab over to the column for 10/13. Each new day I start a new column to collect my data in Worksheet 2. It's a database from which I extract DSUM information that appears on Worksheet 1.

    Hope this understandable. Thanks for your interest.

    Jim

  10. #10
    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: Views - only for the active worksheet (Excel 2000)

    If I understand, when you activate Sheet2 you want the current date selected? If you add this macro in VB to the worksheet object of worksheet 2 when you activate sheet 2 it will find the date or the first blank cell (I assume your list is completely filled in and the dates are in row 4) change as needed.

    Steve

    <pre>Option Explicit
    Private Sub Worksheet_Activate()
    Dim rng As Range
    Dim rCell As Range
    Set rng = Range("E4:IV4")

    For Each rCell In rng
    If rCell.Value > Date Or rCell.Value = "" Then
    rCell.Offset(0, -1).Select
    Exit Sub
    End If
    Next
    End Sub</pre>


  11. #11
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Views - only for the active worksheet (Excel 2000)

    Thanks for your helpful suggestion. What I finally did was write a macro to get the view I wanted in the active worksheet and link it to a button in the toolbar. That way I can go to my "view" with the fixed rows and columns I want but not affect the other worksheet tabs in the workbook.

    Jim

  12. #12
    New Lounger
    Join Date
    Oct 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Views - only for the active worksheet (Excel 2000)

    Thanks for your helpful response. What I ended up doing was writing a macro to get me the different views I needed on the active worksheet and linking them to buttons on the toolbar. That was I can get the different "views" I want without affecting the other worksheets.

    Jim

Posting Permissions

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