Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Protecting Sheet (XL2000 SP3)

    I am working on a workbook with a UsageLog sheet. The following writes the time and username when the workbook is saved. It works all right except that the lines:
    ActiveWorkbook.Worksheets("UsageLog").Unprotect
    and
    ActiveWorkbook.Worksheets("UsageLog").Protect
    Do not seemed to have any effect; if the sheet is unprotected to start with the macro runs properly but the sheet is still unprotected when the macro ends; if the sheet is protected before running the macro it gives an error 1004 at the line
    ThisWorkbook.Worksheets("UsageLog").Range("A65536" ).End(xlUp).Offset(0, 3).Value = Time$ & Space(5) & Date$
    Or, if this line is rem’ed out, 2 lines later (the second msgbox shows that ThisWorkbook and ActiveWorkbook are the same, and the macro falls over whichever is used).

    Sub EnterSaveData()
    On Error GoTo ErrorHandler

    MsgBox ("In Macro EnterSaveData")
    'following checks ThisWorkbook and ActiveWorkbook are UsageLogTest.xls
    Dim name$, name2$
    name$ = Application.ThisWorkbook.name
    name2$ = Application.ActiveWorkbook.name
    MsgBox "ThisWorkbook is " & name$ & Chr(13) & Chr(13) & "ActiveWorkbook is " & name2$

    Application.ActiveWorkbook.Save

    ActiveWorkbook.Worksheets("UsageLog").Unprotect
    MsgBox ("In Macro EnterSaveData, have UnProtected Sheet")

    ThisWorkbook.Worksheets("UsageLog").Range("A65536" ).End(xlUp).Offset(0, 3).Value = Time$ & Space(5) & Date$
    MsgBox ("Should have entered save date but not username")
    Application.ActiveWorkbook.Worksheets("UsageLog"). Range("A65536").End(xlUp).Offset(0, 4).Value = Environ("Username")
    MsgBox ("Should have entered save date and username")

    ActiveWorkbook.Worksheets("UsageLog").Protect
    MsgBox ("In Macro EnterSaveData, have Protected Sheet")

    Exit Sub

    ErrorHandler:
    MsgBox ("error number") & Err
    Select Case Err

    Case Is = 9
    MsgBox ("Worksheet UsageLog is missing. Insert & name this sheet. Press OK to exit macro.")

    Exit Sub

    Case Else
    MsgBox ("Error other than UsageLog sheet missing. Error") & Err

    End Select

    End Sub

    Can anyone help? TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting Sheet (XL2000 SP3)

    Your code runs without problems when I try it except that it doesn't enter anything in column A so it writes to the same row in columns D and E each time.
    It works if the sheet UsageLog is unprotected and if the sheet is protected, but of course if the sheet is protected with a password, the code will prompt for the password, and if you cancel the prompt, the code will fail with error code 1004.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Sheet (XL2000 SP3)

    Thank you Hans

    I am not using a password so that is not the problem. What version of XL are you using? The only think I can think of is that Protect is one of those things that does not work in some versions of XL ( I think TRIM was another though I cannot remember which version it was). If anyone else is still using 2000 SP3 so they could check this.

    Thanks again

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting Sheet (XL2000 SP3)

    I tested in Excel 2002 SP3; I don't have Excel 2000.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protecting Sheet (XL2000 SP3)

    I tried in with XL2000 (no SPs added) and it seems to work as expected...

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting Sheet (XL2000 SP3)

    Since Steve (sdckapr) reports that the code works in Excel 2000 too, could you post a sample workbook (with some dummy data) in which the code fails?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Sheet (XL2000 SP3)

    Hans and Steve

    The problem arose at work where I use XL2000 SP3; at home I have XL2000 SR1 and the code works fine.

    I will not be at work again until next week but I will post the workbook then.

    However suppose my theory is right and there is a glitch in 2000 SR3, if I send a workbook with a macro written with 2000 SR3 and someone with a different version of XL opens it and runs it will not the macro run with the VBA in that person's version so if that version is glitch-free the problem will not show up? Or am I misunderstanding how macros, workbooks and versions interact?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting Sheet (XL2000 SP3)

    I can't find any mention of a problem with protecting/unprotecting sheets that is specific to SP3, but I'm sure there will be a Lounger who has Excel 2000 SP3 to test the workbook.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Sheet (XL2000 SP3)

    My theory that Protect does not work in XL2000 SP3 is wrong. Attached is my workbook with macros that Protect and Unprotect the worksheet, and they work fine as long as they are run alone. But when they are called from the other macros, FormatUsageLog, EnterOpenData, or EnterSaveData they seem to do nothing. If the worksheet is unprotected before it is saved everything is fine, but if the worksheet is protected I get Error 1004. Can anyone tell me what is going on?
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting Sheet (XL2000 SP3)

    It has nothing to do with the version of Excel. Your code doesn't ever call the protect or unprotect code because you have lines such as

    ProtectUsageLog: MsgBox ("In Macro FormatUsageLog have Protected Sheet")

    ProtectUsageLog is interpreted as a label, not as a call to ProtectUsageLog. You should change it to

    Call ProtectUsageLog: MsgBox ("In Macro FormatUsageLog have Protected Sheet")

    or to

    ProtectUsageLog
    MsgBox ("In Macro FormatUsageLog have Protected Sheet")

    and the same for all similar lines. The code will then protect and unprotect the sheet as intended.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Sheet (XL2000 SP3)

    Hans
    You are right of course, I had used the colon to join the Protect call with its MsgBox so they could be remarked out together, without realising VB would think it indicated a nonexistant destination.

    However here is a version with that error removed and the problem persists.
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting Sheet (XL2000 SP3)

    It works OK for me in Excel 2002 SP3. I have opened and closed your workbook a few times, and got all the relevant message boxes; the result is attached. I had to enable macros when I opened the workbook, of course.
    Attached Files Attached Files

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting Sheet (XL2000 SP3)

    Hans

    I am at home using XL2000 SR1 and your workbook opens alright for me but crashes when I try to save it.

    Is there someone else using XL2000 who can try Hans' workbook and see if they can save it?

    Thanks to all.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting Sheet (XL2000 SP3)

    For what it's worth, the code works correctly in Excel 2003 SP3 too.

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Protecting Sheet (XL2000 SP3)

    I have XL2k at home so I will try it later on if I remember!
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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