protecting/unprotecting worksheets in XL97 vba
protecting/unprotecting worksheets in XL97 vba
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: protecting/unprotecting worksheets in XL97 vba

  1. #1
    Guest

    Default

     
    Hi. I need help!

    I'm using a userfrom to place data onto a worksheet, and want to make sure that the worksheet cannot be changed by a user.

    If I have the worksheet protected, I can't place the info gathered in the userform onto the worksheet (gives me an error telling me to unprotect the sheet first).

    I have tried using the Activesheet.UnProtect method, but keep on getting an error. The error is:

    "Run-Time Error 1004"
    "Unprotect method of worksheet class failed"

    Can anybody help me? I use a click of an OK button to hold all the coding for placing the info onto the worksheet.

    When the button is pressed, I want to unprotect the sheet, enter the data, then reprotect the sheet.

    Thanks,
    Corey D.

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try defining your worksheet differently. E.g.,

    Worksheets(1).Unprotect

    Hope this helps. Cheers,

    Nate

    [ This Message was edited by: NateO on 2002-03-13 12:36 ]

  3. #3
    Guest

    Default

    Hmmmm. That doesn't seem to help. This is my excat coding:

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Worksheets(1).Unprotect earth
    Application.EnableEvents = True
    Application.DisplayAlerts = True

    ' Coding to paste info goes here

    Application.EnableEvents = False
    Worksheets(1).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
    Application.EnableEvents = True

    I get the same error regardless of whether I use activesheet or worksheet(1).

    Anyone other ideas? Anyone?

    Thanks,
    Corey D


  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try selecting the sheets first, i.e.:

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    worksheets(1).select
    Worksheets(1).Unprotect earth
    Application.EnableEvents = True
    Application.DisplayAlerts = True

    ' Coding to paste info goes here

    Application.EnableEvents = False
    worksheets(1).select
    Worksheets(1).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
    Application.EnableEvents = True


    HTH. Cheers, Nate

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You should be able to get away with just using:

    Sheets("Mysheet").Unprotect ("admin")

    then later,
    Sheets("Mysheet").Protect ("admin")

    assuming admin is the password.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Friend,
    Excel will generate a error if you try to
    protect sheet for a protected sheet and same is true for unprotection.
    sub prot()
    on error resume next
    Sheets("Mysheet").Unprotect ("admin")

    on error resume next
    Sheets("Mysheet").Protect ("admin")

    end sub

    you can also trap your error and perform the code or convey customise messages

    assuming admin is the password.




  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    In excel97 ActiveX buttons try changing
    the commandbuttons [takefocus*******]
    property to False.



    Ivan

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com