Macro deactivated in specific worksheet

**graiggoriz** · 09-21-2014, 02:50 PM

Hello,

Thanks to all! I have compiled the macros and got that:

Sub PRIINT()


ActiveSheet.Unprotect Password:="p@ssw0rd!"
Dim rng As Range, sh As Worksheet, breaks_count As Long, i As Long
 
Set sh = ActiveSheet

'ADD NEW LINE FOR NOT RUNNING THE MACRO IN CASE SHEET EQUAL TO CURRENT1,2,3,4,5,6:

If Not (sh.Name Like "CURRENT[1234]") Then


sh.Range("a" & sh.ListObjects(1).ListRows.Count).Activate

breaks_count = sh.HPageBreaks.Count

For i = 1 To breaks_count
    If rng Is Nothing Then Set rng = sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7) Else Set rng = Union(sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7), rng)
Next
    
If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).LineStyle = xlContinuous
    
ActiveSheet.Protect Password:="p@ssw0rd!"

'PRINT VERSION
toprint = MsgBox("Would you like to print?", vbYesNo)
If toprint = vbYes Then
     ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
End If

End Sub

I have the following error:
Compile error:
Block If without End If

Can you please help me fixing that error?

Many thanks,
Graig

**HaHoBe** · 09-21-2014, 03:21 PM

Hi, graiggoriz,

indention could be one way to find the error, this is untested (I shifted some of the lines):

Sub PRIINT()

Dim rng As Range, sh As Worksheet, breaks_count As Long, i As Long
 
Set sh = ActiveSheet

'ADD NEW LINE FOR NOT RUNNING THE MACRO IN CASE SHEET EQUAL TO CURRENT1,2,3,4,5,6:

If Not (sh.Name Like "CURRENT[1234]") Then
  sh.Unprotect Password:="p@ssw0rd!"
  sh.Range("a" & sh.ListObjects(1).ListRows.Count).Activate
  breaks_count = sh.HPageBreaks.Count
  
  For i = 1 To breaks_count
    If rng Is Nothing Then
      Set rng = sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7)
    Else
      Set rng = Union(sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7), rng)
    End If
  Next i
      
  If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).LineStyle = xlContinuous
      
  'PRINT VERSION
  If MsgBox("Would you like to print?", vbYesNo) = vbYes Then
    sh.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
  End If
  sh.Protect Password:="p@ssw0rd!"
End If

Set sh = Nothing

End Sub

Ciao,
Holger