Re: Protected cells

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I don't understand about the not compiling if you recorded in xl2003.

But this one line removed the first range:

ActiveSheet.Protection.AllowEditRanges(1).Delete

This would remove them all:

Dim myAllowEditRange As AllowEditRange
Dim wks As Worksheet
Set wks = ActiveSheet
For Each myAllowEditRange In wks.Protection.AllowEditRanges
myAllowEditRange.Delete
Next myAllowEditRange



Roy wrote:
>
> We are all using Excel 2003. In the macro I'm currently using is there a way
> to disable or remove the users ability to edit the range? I recorded a macro
> and inserted it into my existing macro but it does not work because it can't
> compile AllowEditRanges.
>
> "Dave Peterson" wrote:
>
> > Does this mean you're running xl2002+?
> >
> > If yes, then record a macro when you use
> > tools|protection|allow users to edit ranges.
> >
> > You'll see the code that allows this.
> >
> > But in my simple testing, I could unprotect a worksheet that I applied this
> > "range editting" and then reprotect without losing that ability.
> >
> > Any chance you (and your users) are using multiple versions of excel?
> >
> > This stuff only works in xl2002+.
> >
> > Roy wrote:
> > >
> > > Sorry, what I meant is that I want to remove the users ability to edit the
> > > range. I don't want to delete the range.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Before you do that, just a comment...
> > > >
> > > > xl2002 added a feature that allowed developers to protect a worksheet, but allow
> > > > users to edit ranges. If you use this feature, you may not want to delete
> > > > ranges.
> > > >
> > > > But I'm not sure what you mean by delete a range.
> > > >
> > > > You could use:
> > > >
> > > > wks.range("a1:B9").clear
> > > > wks.range("a1:b9").clearcontents
> > > > wks.range("a1:b9").entirerow.delete
> > > > wks.range("a1:b9").entirecolumn.delete
> > > > wks.range("a1:b9").delete shift:=xlup
> > > >
> > > > or something else???
> > > >
> > > >
> > > >
> > > > Roy wrote:
> > > > >
> > > > > I think I figured out my problem. It appears as if when I run the Protect
> > > > > macro a range of cells is unlocked when the sheet is protected. Can I insert
> > > > > a line in the Protect macro that will delete this range? If not, then I'll
> > > > > have to go in and delete this range in each of my 1200 sheets.
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Those macros you posted don't run automatically.
> > > > > >
> > > > > > Any chance you have a "helpful" addin that's sticking it's head in?
> > > > > >
> > > > > > Since it worked ok in safe mode with macros disabled, I'd keep looking for
> > > > > > macros.
> > > > > >
> > > > > > Chip Pearson has some notes on how to diagnose startup errors at:
> > > > > > http://www.cpearson.com/excel/StartupErrors.htm
> > > > > >
> > > > > > And Jan Karel Pieterse has more notes at:
> > > > > > http://www.jkp-ads.com/Articles/StartupProblems.htm
> > > > > >
> > > > > > Essentially, you move all of the stuff out of XLStart and you turn off all the
> > > > > > addins under Toools|addins.
> > > > > >
> > > > > > Then you start adding things back one at a time and testing each time to see if
> > > > > > the problem is fixed. When you add something back that causes the problem to
> > > > > > come back, you'll have to decide what to do with that addin.
> > > > > >
> > > > > > (Make sure you keep track of all the stuff you turn off and move.)
> > > > > >
> > > > > > Roy wrote:
> > > > > > >
> > > > > > > Dave,
> > > > > > >
> > > > > > > Thanks for all your help with this. I looked at each one of my worksheets
> > > > > > > modules and ThisWorkbook module and they are all blank. The only macros I
> > > > > > > have are in Module1 and they are ones I copied on here on a previous message.
> > > > > > >
> > > > > > > I ran excel in safe mode as you suggested and the sheets were still
> > > > > > > protected after I reopened the file in safe mode.
> > > > > > >
> > > > > > > I'm not sure what else to do. I guess I'll have to run a macro when the
> > > > > > > file is opened that protects the worksheets but how do I do that without
> > > > > > > having to type in the password and having this macro not affect my other
> > > > > > > protect all macro?
> > > > > > >
> > > > > > > Roy
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > And you looked under the ThisWorkbook module for a Workbook_open routine that
> > > > > > > > might call the Unprotect_all subroutine (or even workbook_beforesave)????
> > > > > > > >
> > > > > > > > And remember to check under the each worksheet, too. There could be code inside
> > > > > > > > one of those modules that calls that routine.
> > > > > > > >
> > > > > > > > If that doesn't work, you can try this to eliminate the macro possibility:
> > > > > > > >
> > > > > > > > Close excel
> > > > > > > > windows start button|run
> > > > > > > > excel /safe
> > > > > > > > (this'll start excel in safe mode and won't allow macros to run).
> > > > > > > >
> > > > > > > > File|Open your workbook.
> > > > > > > >
> > > > > > > > Protect the worksheets (manually).
> > > > > > > >
> > > > > > > > File|Save
> > > > > > > > File|Close
> > > > > > > >
> > > > > > > > And then reopen while you're still in safe mode.
> > > > > > > >
> > > > > > > > If the worksheets are still protected, I'd keep looking for a macro problem.
> > > > > > > >
> > > > > > > > Roy wrote:
> > > > > > > > >
> > > > > > > > > I don't think so to either question. I only have one file and eventhough I
> > > > > > > > > do have macros in the workbook to unprotect and protect, I did not make them
> > > > > > > > > run when the workbook is opened. I modified the macros that I found on this
> > > > > > > > > site. Here are the macros I'm using:
> > > > > > > > >
> > > > > > > > > Public Sub Unprotect_All()
> > > > > > > > > Dim wks As Worksheet
> > > > > > > > > Dim vPword As Variant
> > > > > > > > > On Error Resume Next
> > > > > > > > > For Each wks In ActiveWorkbook.Worksheets
> > > > > > > > > With wks
> > > > > > > > > .Unprotect vPword
> > > > > > > > > Do While .ProtectContents
> > > > > > > > > vPword = Application.InputBox( _
> > > > > > > > > Prompt:="Enter password for " & .Name, _
> > > > > > > > > Title:="Unprotect sheets", _
> > > > > > > > > Default:="", _
> > > > > > > > > Type:=2)
> > > > > > > > > If vPword = False Then Exit Sub 'user cancelled
> > > > > > > > > .Unprotect vPword
> > > > > > > > > Loop
> > > > > > > > > End With
> > > > > > > > > Next
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > Public Sub Protect_All()
> > > > > > > > > Dim wks As Worksheet
> > > > > > > > > Dim vPword As Variant
> > > > > > > > > vPword = Application.InputBox( _
> > > > > > > > > Prompt:="Enter Password: ", _
> > > > > > > > > Title:="Protect sheets", _
> > > > > > > > > Default:="", _
> > > > > > > > > Type:=2)
> > > > > > > > > If vPword = False Then Exit Sub 'user cancelled
> > > > > > > > > For Each wks In ActiveWorkbook.Worksheets
> > > > > > > > > wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> > > > > > > > > wks.EnableSelection = xlUnlockedCells
> > > > > > > > > wks.Protect vPword
> > > > > > > > > Next
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > >
> > > > > > > > > > This isn't standard behavior.
> > > > > > > > > >
> > > > > > > > > > Any chance you're opening the wrong workbook?
> > > > > > > > > >
> > > > > > > > > > Or your workbook opens and a macro runs that does the unprotecting?
> > > > > > > > > >
> > > > > > > > > > Roy wrote:
> > > > > > > > > > >
> > > > > > > > > > > I have a sheet with protected cells. Everytime I re-open the file, the cells
> > > > > > > > > > > become unprotected. How do I stop the cells from becoming unprotected?
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > Dave Peterson
> > > > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson
.

Relevant Pages

  • Re: fax from word or outlook
    ... If you want to merge to a faxmodem and your merge is fairly simple you could try the following macro. ... I was unable to make this macro work on client computers connected to the Shared Fax Service that is provided as part of Microsoft's Small Business Server 2003. ... Dim bFaxServerAvailable As Boolean ... ' DisplayName is a "user-friendly" name used ...
    (microsoft.public.word.mailmerge.fields)
  • Re: How to Merge data from an ascii file into a Word Doc
    ... position in turn and run the macro InsertLineVariable. ... Dim oVars As Variables ... Dim vVar As Variant ... Dim iCount As Integer ...
    (microsoft.public.word.docmanagement)
  • Re: How can my macro run faster ?
    ... I'd keep all those "Sub/End Sub" statements. ... ' SortDossierOrder Macro ... Dim wks As Worksheet ... Dim cLastRow As Long ...
    (microsoft.public.excel.newusers)
  • Re: Kill a Macro?
    ... that the execution goes back to the calling routine after the called routine ... first macro in File 1 open File 2? ... Dim pwkbDestination As Workbook ... 'Update button in user file Account Summary Sheet runs this macro ...
    (microsoft.public.excel.programming)
  • Re: How can my macro run faster ?
    ... below still have the Sub name and End Sub included. ... ' SortDossierOrder Macro ... Dim wks As Worksheet ... Dim cLastRow As Long ...
    (microsoft.public.excel.newusers)