Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compatibility Excel 2000/2003 (Excel 2000)

    Hallo

    I have the following problem.
    I inherited a workbook with a macro to update certain sheets in the workbook
    If the sheets are unprotected then the macro run on both Excel 2000 and Excel 2003.
    You will notice that there are commands in the macro to unprotect sheets and then protect them after certain opperations.
    If this protection commands are not commuted out then the macro run fine on Excel 2000, but gets a run time error in Excel 2003.

    Sub LDS1()
    '
    ' LDS1 Macro
    ' Macro recorded 07/04/2006 by Administrator
    '

    '
    With Application
    .Calculation = xlManual
    .MaxChange = 0.001
    End With
    Application.Goto Reference:="copyab"
    'ActiveSheet.Unprotect ("lds")
    Range("copy1").Select
    Selection.Copy
    Range("copy1a").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False

    ActiveWorkbook.PrecisionAsDisplayed = False
    ActiveCell.Offset(0, -1).Range("A1").Select
    'ActiveSheet.Protect ("lds")


    Application.Goto Reference:="vop1"
    'ActiveSheet.Unprotect ("lds")
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.EntireColumn.Insert
    Range("vopcopy1").Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.Goto Reference:="vopcopy2"
    Range("vopcopy3").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveCell.Offset(5, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False
    'ActiveSheet.Protect ("lds")

    Application.Goto Reference:="sov1"
    'ActiveSheet.Unprotect ("lds")
    Range("sovcopy").Select
    Selection.Copy
    Range("sov2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False

    ActiveWorkbook.PrecisionAsDisplayed = False
    ActiveCell.Offset(0, -1).Range("A1").Select
    'ActiveSheet.Protect ("lds")
    With Application
    .Calculation = xlAutomatic
    .MaxChange = 0.001
    End With
    Application.Goto Reference:="applno"
    End Sub

    Any advice will be appreciated.

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

    Re: Compatibility Excel 2000/2003 (Excel 2000)

    Welcome to Woody's Lounge!

    I created a worksheet and protected it with password "lds", and adapted the ranges. The code ran without errors in Excel 2003 SP-2. So there is no intrinsic problem with inprotecting and reportecting in Excel 2003. Could you post a small sample workbook with dummy data?

  3. #3
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compatibility Excel 2000/2003 (Excel 2000)

    Hallo Hans

    Thank you. I am not really a New to the Lounge. Our IT department were working on my computer and deleted all my cookies, after that I could not lock in save registering again.

    I attach the file. the macro run from the red button on the Home sheet.

    When I try to attach the file it tells me the file is to big, but infact it is 2K smaller than the max allowed, any ideas?

    Regards

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

    Re: Compatibility Excel 2000/2003 (Excel 2000)

    The max size is 100,000 bytes, which corresponds to about 97.6 KB. Have you zipped the workbook?

    BTW, if you have forgotten your password, the login screen has a button to ask for a new password.

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compatibility Excel 2000/2003 (Excel 2000)

    The attachment

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

    Re: Compatibility Excel 2000/2003 (Excel 2000)

    Starting with Excel 2002, protecting a worksheet has more options. In your workbook, selection of locked cells has been disabled, and in some sheets selection of unlocked cells too. In Excel 2000, it was possible to select cells in a protected sheet.
    So you must either specify that all cells can be selected (then the macro will run without modification), or you must unprotect the correct sheet before using Application.GoTo.

  7. #7
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compatibility Excel 2000/2003 (Excel 2000)

    Hans

    Thank you very much for the reply.

    How do I specify that all cells can be selected? Is it a Excel setting that must be changed?

    Regards

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

    Re: Compatibility Excel 2000/2003 (Excel 2000)

    Select Tools | Protection | Unprotect Sheet... and provide the password.
    Then select Tools | Protection | Protect Sheet...
    You'll see a dialog in which you can specify what users are allowed to do. Selecting locked cells and selecting unlocked cells are the first two items.
    Enter the password, then click OK. You'll be asked to confirm the password.

  9. #9
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compatibility Excel 2000/2003 (Excel 2000)

    Hans

    Thank you.

    I was thinking that since I do not have Excel2003 on my computer and I do not know who will use the workbook in the future, it might be simpler to unprotect all sheets at the beginning of the macro and then re-protect them at the end.

    Would you be so kind as to help me with the 2 lines of code for un-protecting all sheets in the workbook and then protecting all sheets in the workbook.

    I do appreciate your help and patience.

    Thank you

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

    Re: Compatibility Excel 2000/2003 (Excel 2000)

    You have to loop through the sheets:

    Dim wsh As Worksheet
    ' Unprotect
    For Each wsh In ActiveWorkbook.Worksheets
    wsh.Unprotect Password:="lds"
    Next wsh

    ' your code
    ...

    ' Protect
    For Each wsh In ActiveWorkbook.Worksheets
    wsh.Protect Password:="lds"
    Next wsh

  11. #11
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compatibility Excel 2000/2003 (Excel 2000)

    Hans

    Thank you very much, everything seems to work correct now.


    Regards

Posting Permissions

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