Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Protecting columns (Office 2000)

    I am using a spreadsheet from work and was asked to set it up so that certain columns could not be edited, to which I did, but the only problem is is that they want to be able to resize columns, hide columns, and use the autofilter. This is not possible from what I can see. What I am wondering is, is there a way to make this possible.

    Thanks
    Eric

  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: Protecting columns (Office 2000)

    <post#=340494>post 340494</post#> and the answers discuss adding autofilter with protection.

    You could create your own routines for working with the other columns. These routines would unprotect the workbook and do everything for them.

    Another option would be to put the hidden columns on a different sheet and hide the whole other sheet, then the sheet you want people to have more control over would not need protecting.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Protecting columns (Office 2000)

    Excel 2002 and higher are much more flexible in this respect - you can specify which aspects of a worksheet are locked.

    If you turn on AutoFilter before protecting the worksheet, you can use code such as in the thread starting at <post#=340494>post 340494</post#> to be able to use it in the protected sheet too. As far as changing column widths and hiding columns is concerned, you could create macros that unprotect the sheet, modify columns, then reprotect the sheet.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting columns (Office 2000)

    You could put some code like this into the Worksheet Change event routine (this code will protect columns C:E from changes):

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C:E")) Is Nothing Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    End If
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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