+ Reply to Thread
Results 1 to 3 of 3

Worksheet_Activate & Workbook_SheetActivate events error 1004 when several sheets selected

Hybrid View

  1. #1
    Registered User
    Join Date
    11-06-2012
    Location
    Texas
    MS-Off Ver
    O365
    Posts
    27

    Worksheet_Activate & Workbook_SheetActivate events error 1004 when several sheets selected

    I'm attempting to Protect/Unprotect worksheets based on various criteria when the user switches to them. When the user switches sheets everything works as expected. When the user right clicks on only 1 sheet tab (to hide/delete) everything works as expect. However, when multiple sheet tabs are selected, the event code throws a 1004 error at the Protect/Unprotect code.

    Is there a better way of handling the Protection updates, or handling the multiple sheet tab error?


    Code for reference:
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
            If criteria Then
                Sh.Unprotect
            Else
                Sh.Protect
            End If
    End Sub
    Private Sub Worksheet_Activate()
            If criteria Then
                Me.Unprotect
            Else
                Me.Protect
            End If
    End Sub

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Worksheet_Activate & Workbook_SheetActivate events error 1004 when several sheets sele

    Hello Daishiknyte,

    This is interesting. I have never selected multiple worksheets and added or removed protection from them. Error 1004 is useless. It is a "general fault" in VBA with no specific cause.

    My question is what is the "criteria" that are being checked?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    11-06-2012
    Location
    Texas
    MS-Off Ver
    O365
    Posts
    27

    Re: Worksheet_Activate & Workbook_SheetActivate events error 1004 when several sheets sele

    The criteria is a set of global values I use to hold project status. The workbook itself isn't protected, just the sheets.

    I found a solution (work around?) by cycling through each of the selected sheets, protecting them, then reselecting them at the end. Here's the stripped down relevant code:

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        Dim selSheets As Variant: Set selSheets = ActiveWindow.SelectedSheets
        Dim ws As Worksheet
        
        If criteria Then
            For Each ws In selSheets
                ws.Select
                ws.Unprotect
            Next
        Else
            For Each ws In selSheets
                ws.Select
                ws.Protect
            Next
        End If
        
        For Each ws In selSheets
            ws.Select False
        Next
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Error 1004 when no sheets are protected
    By brucemc777 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-04-2018, 09:54 AM
  2. [SOLVED] Error 1004 no cell selected
    By HXIO in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-17-2018, 05:38 AM
  3. [SOLVED] Simple error: List multiple sheet names in Workbook_SheetActivate event
    By onmyway in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-01-2015, 05:44 AM
  4. Run Time Error 1004 & also how to add more sheets to not copy
    By benoj2005 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-08-2014, 06:52 AM
  5. SOLVED: Same code in two different sheets, yet 1004 error in one
    By caracolesa in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-07-2012, 09:20 AM
  6. Copy sheets causes Error 1004??
    By gpsmith in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-12-2009, 05:47 PM
  7. Error '1004' when adding sheets
    By broncoern in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2007, 04:04 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1