Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    protect worksheet but run macros (Excel 2000)

    Hi,
    I am wondering is there a way were I can protect certain cells in a worksheet so they are not overwritten but still run macros using the protect worksheet fuction?

    Thanks ahead

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

    Re: protect worksheet but run macros (Excel 2000)

    I'm not sure what you mean. It is possible to run macros while in a protected worksheet.

    If you want to maniplulate locked cells in your macro, you can temporarily unprotect the worksheet:

    Sub MyMacro()
    ActiveSheet.Unprotect "MyPassword"
    Range("D10") = 37
    ActiveSheet.Protect "MyPassword"
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: protect worksheet but run macros (Excel 2000)

    I should have mentioned that my macros are located in the worsheet that I want to protect

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

    Re: protect worksheet but run macros (Excel 2000)

    Apparently I'm missing something. Do you mean that the "macros" are event procedures in the worksheet module?

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: protect worksheet but run macros (Excel 2000)

    yes, Hans, and when I protect the worksheet they can not work, is there something I can do to keep the worksheet protected yet have the event macros working?

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

    Re: protect worksheet but run macros (Excel 2000)

    Event procedures still work in a protected worksheet, but if you want to manipulate locked cells, you must temporaily unprotect the worksheet, as indicated in my first reply in this thread. If that does not do what you want, or is not what you meant, could you please provide more specific information?

  7. #7
    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: protect worksheet but run macros (Excel 2000)

    Which events do they respond to? If you have prevented changes to the worksheet by protecting it, then obviously the Worksheet_Change event will not fire! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Otherwise, Hans' suggestion should do what you need.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: protect worksheet but run macros (Excel 2000)

    Yes its the worksheet change event,
    I will need to use what Hans provided in the first message
    Thank you

  9. #9
    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: protect worksheet but run macros (Excel 2000)

    If you do not want them to change (ie they are protected) then they will not trigger any events since they are not changing.

    Would it be more appropriat to put your code in the "Worksheet_SelectionChange" event to trigger events not when the value changes, but when certain cells are selected?

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: protect worksheet but run macros (Excel 2000)

    Hi Steve,

    "Would it be more appropriate to put your code in the "Worksheet_SelectionChange" event"

    Yes and No,
    I have a rather long about 30 lines of code to run behind the worksheet, some of this code would work great with the Worksheet_SelectionChange event but some pieces would calculate prematuraly and cause circular refs,

    I also have a toggle switch which activates a cetain procedure which is also affected by the protecting.

    I think I am left to just leave the worksheet unprotected and cross my fingers or use what hans suggested and place that code in each sub that has code behind the worksheet.

    Thanks

Posting Permissions

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