Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Hiding All Extra Rows and Columns (97)

    I'm trying to create a template for users where they are restricted to a limited area, say A1 to D15. I tried hiding rows 16 on and columns E-IV and that seemed to work but after I saved the worksheet and tried to reload it, it would lock up Excel any time I tried to load it.

    1) Is there any other way, other than hiding, to restrict the user?
    2) Any suggestions for recovering the worksheet I've already created? (The A1 to D15 was an example, it's actually much larger.)

    Thanks

    Ronny
    Ronny Richardson

  2. #2
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding All Extra Rows and Columns (97)

    You can lock cells that you don't wish people to use.

    Hightlight the area that you would like them to use
    Right click then FORMAT CELLS
    Go to the PROTECTION tab and untick LOCKED and OK
    Then TOOLS - PROTECTION - PROTECT SHEET (you can even use a password here)
    They will now only be able to write in the areas that you have allowed them.

    As to the second part , if you have hidden from row 16 all the way to the bottom
    Try this
    In the NAME BOX (Which is the box at the top that says which cell you are in) type A65536 and press return
    This takes you to the cell A65536 which is at the bottom
    Then go FORMAT ROW UNHIDE
    This gives you the last row
    Now Hightlight this row and hold SHIFT and hightlight the row above (which sounds like it's row 15 in your case)
    then go FORMAT ROW UNHIDE


    Hope this helps

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding All Extra Rows and Columns (97)

    You can lock cells that you don't wish people to use.

    Hightlight the area that you would like them to use
    Right click then FORMAT CELLS
    Go to the PROTECTION tab and untick LOCKED and OK
    Then TOOLS - PROTECTION - PROTECT SHEET (you can even use a password here)
    They will now only be able to write in the areas that you have allowed them.

    As to the second part I'm not sure what has been hidden but if you have hidden from row 16 all the way to the bottom
    Try this
    In the NAME BOX (Which is the box at the top that says which cell you are in) type A65536 and press return
    This takes you to the cell A65536 which is at the bottom
    Then go FORMAT ROW UNHIDE
    This gives you the last row
    Now Hightlight this row and hold SHIFT and hightligt the row above (which sounds like it's row 15 in your case)
    then go FORMAT ROW UNHIDE


    Hope this helps

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hiding All Extra Rows and Columns (97)

    You can set the scroll area of any worksheet, which does restrict selection cells to that range. However it requires to be set each time the workbook is opened as it does not persist after closing and/or saving the workbook. The following example will restrict selection to Range A115 of Sheet1, but you can change these to suit.

    Private Sub Workbook_Open()
    Sheets("Sheet1").ScrollArea = "A115"
    End Sub

    That code is placed in the ThisWorkbook code module. Or you can rename it Sub Auto_Open and place it in a general module.

    As for your second question try some of the links in <post#=23374>post 23374</post#> . You could also try opening the file in Internet Explorer.

    Andrew C

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Hiding All Extra Rows and Columns (97)

    This is simple and neat, thanks.
    Ronny Richardson

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Hiding All Extra Rows and Columns (97)

    I appreciate the help from everyone. Here is what I figured out.

    I have seen worksheets where rows and columns beyond a certain range cannot be seen. I expect that those worksheets were created with a version of Excel beyond the 97 that I use. When I hid rows and columns in my tiny spreadsheet and saved it, the file ended up being over 500K and Excel 97 would not load it again. I took it to school and opened ith with Excel XP without any problem. I unhid the rows and columns and resaved it and the file size was down to 67K with no other changes.

    So, I guess that with Excel 97, I'll have to stick to locking cells and the scroll control macro that was posted.

    I appreciate all the help.


    Ronny
    Ronny Richardson

Posting Permissions

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