+ Reply to Thread
Results 1 to 9 of 9

Help w/ protecting all sheets

  1. #1
    Registered User
    Join Date
    08-11-2005
    Posts
    6

    Help w/ protecting all sheets

    I have read the threads here on how to protect multiple sheets in a workbook and not having to protect them 1 by 1. I have copied and pasted all the different macro's/visual basic script that has been posted for that purpose, but I can't get it to work and I'm always getting an error. The error from visual basic says "compile error: invalid outside procedure" and then the visual basic screen shows the following:

    Range ("AM52")
    If Ans >= 0 Then
    "+."

    It does not like the "AM52" part because it is highlighted in the error message.

    Also, the error I get from recording my own macro step by step is "Run time error 40036" from visual basic.

    Can anyone help me to get this running and tell me what to do step by step? I looked at this all day last friday and couldn't get it to do right. Thanks.

  2. #2
    Registered User
    Join Date
    05-25-2006
    Posts
    42
    I'm not exactly sure what you want to do, but for the part where in your code that says Range("AM52") ... I think you need to indicate what you want to do with that range (select, copy, etc ...). I'm assuming the command you would want is Range("AM52").Value

  3. #3
    Registered User
    Join Date
    08-11-2005
    Posts
    6
    Well, I have the spreadsheet all set up for my job to document figures from the past and present for the purpose of keeping up with it and building charts from it. I just plugged in the macro/script given in these forums, and they all said to just do that and it would work. Mine does not just work like that and I don't know why. I have a lot of 'ranges' on my sheets, and that AM52 is the only one that pops up in the error, yet there is nothing special about that cell or range. Basically my question is just like the others on here if you were to do a search for 'protecting multiple sheets' and read all the threads and questions. The only difference is that I can't get mine to work, while it appears that others have been able to.

  4. #4
    Gord Dibben
    Guest

    Re: Help w/ protecting all sheets

    cnote

    I would suggest you clean up/delete the code you have tried to implement and
    start over with these two macros. See below for instructions for use.

    Sub ProtectAllSheets()
    Application.ScreenUpdating = False
    Dim n As Single
    For n = 1 To Sheets.Count
    Sheets(n).Protect Password:="justme"
    Next n
    Application.ScreenUpdating = True
    End Sub

    Sub UnprotectAllSheets()
    Application.ScreenUpdating = False
    Dim n As Single
    For n = 1 To Sheets.Count
    Sheets(n).Unprotect Password:="justme"
    Next n
    Application.ScreenUpdating = True
    End Sub

    If not familiar with VBA and macros, see David McRitchie's site for more on
    "getting started".

    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    In the meantime..........

    First...create a backup copy of your original workbook.

    To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

    Hit CRTL + R to open Project Explorer.

    Find your workbook/project and select it.

    Right-click and Insert>Module. Paste the code in there. Save the
    workbook and hit ALT + Q to return to your workbook.

    Run the macros by going to Tool>Macro>Macros.

    You can also assign these macros to a button or a shortcut key combo.


    Gord Dibben MS Excel MVP

    On Mon, 19 Jun 2006 08:20:57 -0500, cnote
    <[email protected]> wrote:

    >
    >I have read the threads here on how to protect multiple sheets in a
    >workbook and not having to protect them 1 by 1. I have copied and
    >pasted all the different macro's/visual basic script that has been
    >posted for that purpose, but I can't get it to work and I'm always
    >getting an error. The error from visual basic says "compile error:
    >invalid outside procedure" and then the visual basic screen shows the
    >following:
    >
    >Range ("AM52")
    >If Ans >= 0 Then
    >"+."
    >
    >It does not like the "AM52" part because it is highlighted in the error
    >message.
    >
    >Also, the error I get from recording my own macro step by step is "Run
    >time error 40036" from visual basic.
    >
    >Can anyone help me to get this running and tell me what to do step by
    >step? I looked at this all day last friday and couldn't get it to do
    >right. Thanks.


    Gord Dibben MS Excel MVP

  5. #5
    Registered User
    Join Date
    08-11-2005
    Posts
    6
    gord,

    thanks for the advice. i did what you said and still having the same exact problem. i always start w/ a clean slate for these macro's/vba script. for some reason there is a problem w/ one of my cells, the one specified, and i'm not familiar enough w/ it to understand what the problem is. it continues to give me the 'compile error: invalid outside procedure' and this is it and am52 is always highlighted:

    Range ("AM52")
    If Ans >= 0 Then
    "+."

    also i get an error for this and the 2nd line is always highlighted:

    Dim mySheet As Worksheet
    For Each mySheet In Worksheets
    mySheet.Protect Password:=password1
    Next mySheet

    this seems to be an error specifically related to my application only for the way my workbook is set up. however, i set all my sheets and charts up properly in the program using correct normal ways and not in a way where it is set up wrongly where you might continuously uncover improper setups if you were to look into it.
    Last edited by cnote; 06-20-2006 at 10:09 AM.

  6. #6
    Gord Dibben
    Guest

    Re: Help w/ protecting all sheets

    Just the one workbook gives this you this problem?

    Have you tried with a new workbook?

    If all workbooks, perhaps you have some add-in that is triggering the error.

    Go to Tools>Add-ins and uncheck everything then start re-checking one at a time.

    If just the one workbook, send me a copy through my email.

    Change the AT and DOT to appropriate character.


    Gord

    On Tue, 20 Jun 2006 08:05:33 -0500, cnote
    <[email protected]> wrote:

    >
    >gord,
    >
    >thanks for the advice. i did what you said and still having the same
    >exact problem. i always start w/ a clean slate for these macro's/vba
    >script. for some reason there is a problem w/ one of my cells, the one
    >specified, and i'm not familiar enough w/ it to understand what the
    >problem is. it continues to give me the 'compile error: invalid outside
    >procedure' and this is it and am52 is always highlighted:
    >
    >Range ("AM52")
    >If Ans >= 0 Then
    >"+."
    >
    >also i get an error for this and the 2nd line is always highlighted:
    >
    >Dim mySheet As Worksheet
    >For Each mySheet In Worksheets
    >mySheet.Protect Password:=password1
    >Next mySheet
    >
    >this seems to be an error specifically related to my application only
    >for the way my workbook is set up. however, i set all my stuff up
    >properly in the normal simple ways and not in a way where it is set up
    >wrongly and you continuously uncover improper ways of setup if you were
    >to look at it.


    Gord Dibben MS Excel MVP

  7. #7
    Registered User
    Join Date
    08-11-2005
    Posts
    6
    its just not working. i am also getting a compile error: invalid outside procedure every time. there's something about that 'am52' cell or range that is not agreeing with anything i do and i don't have a clue what is wrong. there's nothing special about the way its set up. thanks for all the help gord. can anyone tell me now how i can keep this 'personal' workbook from popping up now every single time i open excel? i hate that, i don't run any other macros in any of my other sheets and its never popped up all the time before but it does now.

  8. #8
    Registered User
    Join Date
    02-20-2013
    Location
    Nairobi, Kenya
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    3

    Re: Help w/ protecting all sheets

    Try this simple code!

    To Protect

    '>>>
    Please Login or Register  to view this content.
    '<<<

    To Unprotect


    '
    Please Login or Register  to view this content.
    Last edited by arlu1201; 02-20-2013 at 02:52 PM.

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Help w/ protecting all sheets

    kilonzi,

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1