none
For Each - Continue For Error RRS feed

  • Question

  • I am trying to protect several of the worksheets, but not all of them. I added the If statement for the sheets I want to exclude.

    I get an expected expression error and I can't figure out why.

    Sub ProtectAllSheets3()
     Dim ws As Worksheet
      For Each ws In ActiveWorkbook.Worksheets
          If ws = "Sheet1" or "Sheet2" Then Continue For
          End If
        ws.Protect Password:="happy"
      Next ws
    End Sub

    • Moved by KareninstructorMVP Tuesday, October 13, 2015 6:09 PM Moved from VB.NET for better visibility
    Tuesday, October 13, 2015 5:31 PM

Answers

  • >>> If I use "Or" code below the macro runs but it protects everything and doesn't exclude either of the Sheets 1 or 2, I wanted excluded.

    According to your recent description, I have reproduced your issue, and I find out that Continue statement is new in VB.NET. It doesn't exist in VB (6 or earlier), or in VBA in Office.

    So I suggest that you could change your code like below:

    Sub ProtectAllSheets3()
    
      Dim ws As Worksheet
    
       For Each ws In ActiveWorkbook.Worksheets
    
           If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then
    
              ws.Protect Password:="happy"
    
           End If
    
       Next ws
    
     End Sub
    

    • Marked as answer by m3looper Wednesday, October 14, 2015 8:26 PM
    Wednesday, October 14, 2015 9:10 AM

All replies

  • Your If statement is a mess.

    Try something like this:

    If ws.Name <> "Sheet1" AndAlso ws.Name <> "Sheet2" then

    <your code>

    End If


    David M. Nichols software engineer

    Tuesday, October 13, 2015 5:43 PM
  • Try this:

        If ws.Name = "Sheet1" OrElse ws.Name = "Sheet2" Then Continue For

    Also remove End If.

    If it is not VB.NET, then use Or instead of OrElse.


    • Edited by Viorel_MVP Tuesday, October 13, 2015 5:44 PM
    Tuesday, October 13, 2015 5:44 PM
  • Is this a .NET project or code in an Excel file?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.
    Microsoft Developer tools
    Developer’s Guide to Windows 10 video series

    Tuesday, October 13, 2015 6:06 PM
  • For an Excel file.
    Tuesday, October 13, 2015 6:08 PM
  • I get an error "Expected Then or GoTo" on the "AndAlso"
    Tuesday, October 13, 2015 6:13 PM
  • I get an error "Expected Then or GoTo" on the "OrElse"

    Tuesday, October 13, 2015 6:15 PM
  • Viorel,

    I couldn't get your reply to work for multiple or a single sheet. Seems there is a problem with the OrElse and the Continue For. I did try removing the End If and using the Or.

    Tuesday, October 13, 2015 7:05 PM
  • David,I cleaned up the mess, and removed the code for the second sheet and it works for one sheet, but when I tried adding back in the second Sheet2 using "AndAlso", "OrElse", it won't work. If I use "Or" code below the macro runs but it protects everything and doesn't exclude either of the Sheets 1 or 2, I wanted excluded.

    If ws.Name <> "Sheet1" Or ws.Name <> "Sheet2" Then

    Tuesday, October 13, 2015 7:12 PM
  • >>> If I use "Or" code below the macro runs but it protects everything and doesn't exclude either of the Sheets 1 or 2, I wanted excluded.

    According to your recent description, I have reproduced your issue, and I find out that Continue statement is new in VB.NET. It doesn't exist in VB (6 or earlier), or in VBA in Office.

    So I suggest that you could change your code like below:

    Sub ProtectAllSheets3()
    
      Dim ws As Worksheet
    
       For Each ws In ActiveWorkbook.Worksheets
    
           If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then
    
              ws.Protect Password:="happy"
    
           End If
    
       Next ws
    
     End Sub
    

    • Marked as answer by m3looper Wednesday, October 14, 2015 8:26 PM
    Wednesday, October 14, 2015 9:10 AM
  • David, This worked, thank you.

    Wednesday, October 14, 2015 8:28 PM