Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Protection and AllowFormatting (Excel 2000 and 2003)

    Hi All,

    I have locked/protected sheets in VBA. I have Excel 2003 at work, 2000 at home, and my users have either 2003 or 2000.
    I have allowed formatting cells (and the use of autofiltering) in the code:

    Worksheets("mySheet").Protect AllowFiltering:=True, AllowFormattingCells:=True

    Unfortunately, the Excel 2000 users receive a runtime error (1004) -- which if they click "End", leaves the worksheet unprotected, so my question is how can I ask which version of excel is being used before I protect the sheet?

    myExcelVersin = ???
    If myExcelVersion = "2003" then
    Worksheets("mySheet").Protect AllowFiltering:=True, AllowFormattingCells:=True
    Else
    Worksheets("mySheet").Protect
    End If

    Thanks!
    --Cindy

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

    Re: Protection and AllowFormatting (Excel 2000 and 2003)

    Application.Version returns a string representing the value: 8.0 for Excel 97, 9.0 for Excel 2000, 10.0 for Excel 2002 (XP) and 11.0 for Excel 2003. Excel 2007 will be 12.0. So you could try

    Dim myExcelVersion As Integer
    myExcelVersion = Val(Application.Version)
    If myExcelVersion > 9 Then
    Worksheets("mySheet").Protect AllowFiltering:=True, AllowFormattingCells:=True
    Else
    Worksheets("mySheet").Protect
    End If

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection and AllowFormatting (Excel 2000 and 2003)

    Thank you very much, Hans.

Posting Permissions

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