I made a Order form for a friend that knows very little about excel, so I
locked all cells that had any formulas in case she deletes them and then the
page would not calculate correctly( She already has deleted some formulas
before).
I created these 2 Radio buttons that basically will make some cells change
the text and run a macro, where this macro will hide a column and change
color in cells ("B2:G2"). Also I need to have the Auto filter running so as
to filter all "NonBlanks". I have password protected and lock ("B2:G2") and
the column E:E (and many others, but theses are the ones affected when
running the macro) So when I run one of the macros in the radio buttons to
hide column E:E and change the color to yellow/blue then of course I get an
msgbox saying those cells are lock and cant be changed.
To solve the problem of running the AutoFilter in Excel 2000 wile cells are
locked, I used this formula I found from Tom Ogilvy and alter to make it
work in my sheet, it works great!
Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Estimate")
If Not .AutoFilterMode Then
.Range("B4:B500").AutoFilter
End If
.EnableAutoFilter = True
.Protect password:="", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub
----------------------------------------------
THE PROBLEM is when I try to run these macros bellow, I still get the
messages of not been able to change because the cells are locked.
The macros a
Sub ClientEstimate()
' Hides the cost of the materials
ActiveSheet.Unprotect
Columns("E:E").EntireColumn.Hidden = True
Range("B2:G2").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = 2
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios
_
:=False 'Re-protect Sheet
End With
End Sub
----------------------------------------------------------------------------
------
Sub Estimate()
ActiveSheet.Unprotect
Columns("E:E").EntireColumn.Hidden = False
Range("B2:G2").Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = 2
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios _
:=False 'Re-protect Sheet
End With
End Sub
-----------------------------------------------------------------
Thank you for all your help

Kevin Brenner


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 6/18/2004

after this
range("B2:g22").Select
add this

*Selection.Locked = False

--
Message posted from http://www.ExcelForum.com