Locked sheet

Anyone know why a locked sheet would not allow the following VB to run -
I get an error at the Selection.clear contents point (although the sheet
is locked only formulae are locked, and they are not near these cells"
Point of the formulae to is to delte (formats and contents of 24c5 to
28c6

Sheets("Review Plan").Select
Application.Goto Reference:="R24C5:R28C6"
Application.CutCopyMode = False
Selection.ClearContents
Selection.ClearFormats

Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Darin...

ClearFormats or Clear will set the locked property for a cell
to the 'default' from the style 'Normal' .

In most cases Normal style will have Locked set to TRUE.
.... thus your cells are locked again.


To avoid problems with automating protected sheets,
use the UserInterfaceOnly option of the Protect Method.

Be sure to read the remarks section in VBA help. You'll need to
Unprotect/Reprotect your worksheets on file open as that option is not
saved.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Darin Kramer wrote :

> Anyone know why a locked sheet would not allow the following VB to
> run - I get an error at the Selection.clear contents point (although
> the sheet is locked only formulae are locked, and they are not near
> these cells" Point of the formulae to is to delte (formats and
> contents of 24c5 to 28c6
>
> Sheets("Review Plan").Select
> Application.Goto Reference:="R24C5:R28C6"
> Application.CutCopyMode = False
> Selection.ClearContents
> Selection.ClearFormats
>
> Thanks
>
> D
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Thanks for the help... just one question...

Is the UserInterfaceONly option an add in...? where can I find it..?

Thanks

D

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Look in Excel VBA help under the Protect method. It is one of the
arguments.

--
Regards,
Tom Ogilvy

"Darin Kramer" <darin_kramer@hotmail.com> wrote in message
news:%23FFpsbyHFHA.3844@TK2MSFTNGP14.phx.gbl...
> Thanks for the help... just one question...
>
> Is the UserInterfaceONly option an add in...? where can I find it..?
>
> Thanks
>
> D
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

I know its been a while... but when trying this I am unable to get it to
work.

Just inserted a basic protection function (see below)

Im still having the same problem ie cells that were not locked, become
locked when I run a macro Say range a10:c50.

I thought inserting the UserInteraceONly option would resolve this?

Help...



Sub ProtectSheet()

ActiveSheet.Protect , UserInterfaceOnly:=True,
AllowFormattingCells:=True

End Sub







*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!