Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Creating a new Excel Worksheet Event! (Excel 2000 >)

    Hi All,

    My question is simple and I recall this being asked in the Excel/VBA forum in the past, but I still did not understand the jargon then. I need a very laymens step by step explanation. Maybe I should firstly ask if my request is at all possible! "How do I create a new worksheet event?"
    Ie: If the person attempts to protect the worksheet, I want a macro to trigger! - Worksheet_OnSheetProtect()

    Please lead me through a process to help me understand how to do this - if its at all possibel!
    Big Tx
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a new Excel Worksheet Event! (Excel 2000 >)

    You cannot. You are stuck with whatever the development team of the sheet object has prepared for us.

    You can however intercept the menu click that gets you there.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a new Excel Worksheet Event! (Excel 2000 >)

    <img src=/S/sad.gif border=0 alt=sad width=15 height=15> - Tx for the suggesion! I'll give it a bash and ask for help if I cannot get this working.

    PS: I just posted a thread on modifying the context menu this morning. Maybe this will come in handy!! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a new Excel Worksheet Event! (Excel 2000 >)

    Jan Karel,
    Could you help me to achieve what you proposed. In a search for disabling menu items I came up with this thread: <post:=448,852>post 448,852</post:> , where to disable the Print command is easy as (being and event) you can simply say Cancel = True. However, I would like the Protection Command to be disabled for a specific workbook. Any ideas how to achieve this?
    Tx
    Regards,
    Rudi

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a new Excel Worksheet Event! (Excel 2000 >)

    IN the Thisworkbook module:

    <pre>Option Explicit


    Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    DisableProtect
    End Sub

    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    EnableProtect
    End Sub
    </pre>


    And in a normal module:

    <pre>Option Explicit

    Sub DisableProtect()
    'This disables the entire protection sub menu.
    'The indivdual entries are:
    '893 Protect Sheet...
    '6997 Allow Users to Edit Ranges... (Excel XP and up)
    '894 Protect Workbook...
    '3059 Protect and Share Workbook...

    Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=30029, recursive:=True).Enabled = False
    End Sub

    Sub EnableProtect()
    Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=30029, recursive:=True).Enabled = True
    End Sub

    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a new Excel Worksheet Event! (Excel 2000 >)

    Tx Jan,
    I understand the Menu ID that you used in the code, but what do you mean with:

    'The indivdual entries are:
    '893 Protect Sheet...
    '6997 Allow Users to Edit Ranges... (Excel XP and up)
    '894 Protect Workbook...
    '3059 Protect and Share Workbook...
    Regards,
    Rudi

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a new Excel Worksheet Event! (Excel 2000 >)

    If you only want to disable certain elements of the protect menu, use those Id's.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a new Excel Worksheet Event! (Excel 2000 >)

    Perfect. Tx
    Regards,
    Rudi

Posting Permissions

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