Protection and AllowFormatting (Excel 2000 and 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Thank you very much, Hans.

Reply | Quote