VBA: Compile error

[Briggy719]

I've been working with the same bit of code for a while now and I'm comming up across a new error on code that hasn't been changed. I get the compile error: only comments allowed after End Sub... and I've checked my code and I'm fairly certain that there isn't anything after my End Sub comment besides the start of a new procedure. I've used this code in the past without a problem but now it's basically telling me I can only have procedure in a module. Has anyone else come across this glitch?

THanks!

 

[JackDanIce]

Yes, but I can see my own code to work it out; be helpful if you posted yours!

 

[Briggy719]

Well there was a lot more to it but here is the beginning:

Sub ACurrentYours()
If Backend2.[B93].Value = False Then
[E13:J13].Borders(xlTop).LineStyle = xlNone
[E13:J13].Borders(xlRight).LineStyle = xlNone
[E13:J13].Borders(xlBottom).LineStyle = xlNone
[E13:J13].Borders(xlLeft).LineStyle = xlNone
[E13,G13,I13].ClearContents
[E13,G13,I13].Locked = True
[E13:J13].Font.ColorIndex = 2
[G13].Value = 0
ElseIf Backend2.[B93].Value = True Then
[E13,G13,I13].Borders(xlTop).LineStyle = xlSolid
[E13,G13,I13].Borders(xlRight).LineStyle = xlSolid
[E13,G13,I13].Borders(xlBottom).LineStyle = xlSolid
[E13,G13,I13].Borders(xlLeft).LineStyle = xlSolid
[E13:J13].Font.ColorIndex = 1
[G13].Value = 75#
Backend2.[D93].Value = 1
[E13,G13,I13].Locked = False
[E13].Select
End If
End Sub

Sub BCurrentYours()
If Backend2.[B95].Value = False Then
[E15:J15].Borders(xlTop).LineStyle = xlNone
[E15:J15].Borders(xlRight).LineStyle = xlNone
[E15:J15].Borders(xlBottom).LineStyle = xlNone
[E15:J15].Borders(xlLeft).LineStyle = xlNone
[E15,G15,I15].ClearContents
[E15,G15,I15].Locked = True
[E15:J15].Font.ColorIndex = 2
[G15].Value = 0
ElseIf Backend2.[B95].Value = True Then
[E15,G15,I15].Borders(xlTop).LineStyle = xlSolid
[E15,G15,I15].Borders(xlRight).LineStyle = xlSolid
[E15,G15,I15].Borders(xlBottom).LineStyle = xlSolid
[E15,G15,I15].Borders(xlLeft).LineStyle = xlSolid
[E15:J15].Font.ColorIndex = 1
[G15].Value = 75#
Backend2.[D95].Value = 1
[E15,G15,I15].Locked = False
[E15].Select
End If
End Sub

So the first procedure works just fine, but I get the compile error when I run the second one. Just now I started from scratch and rewrote the code, it worked perfectly the first time but when I started playing around with adding unprotect/protect functions, the error came back up.

Thanks for your help!

 

[MrKowz]

I assume Backend2 is a public worksheet variable?

Edit: Upon reading your OP a bit closer, it sounds like you have a procedure that has "End Sub" in it prematurely. As the error message said, you can only have comments outside of a procedure, so somewhere in your entire module, there is an area where there is code outside of a procedure.

Try:

Sub BCurrentYours()
If Backend2.Range("B95").Value = False Then
    With Range("E15:J15")
        .BorderAround LineStyle:=xlNone
        .Font.ColorIndex = 2
    End With
    With Range("E15", "G15", "I15")
        .ClearContents
        .Locked = True
    End With
    Range("G15").Value = 0
ElseIf Backend2.Range("B95").Value = True Then
    With Range("E15", "G15", "I15")
        .BorderAround LineStyle:=xlSolid
        .Locked = False
    End With
    Range("E15:J15").Font.ColorIndex = 1
    Range("G15").Value = 75
    Backend2.Range("D95").Value = 1
    Range("E15").Select
End If
End Sub

 

[JackDanIce]

Try this instead:

Sub ACurrentYours()
If Not Backend2.Range("B93") Then
    With Range("E13:J13")
        .Borders(xlTop).LineStyle = xlNone
        .Borders(xlRight).LineStyle = xlNone
        .Borders(xlBottom).LineStyle = xlNone
        .Borders(xlLeft).LineStyle = xlNone
        .Font.ColorIndex = 2
    End With
    Range("E13", "G13", "I13").ClearContents
    Range("G13") = 0
    Range("E13", "G13", "I13").Locked = True
Else
    With Range("E13", "G13", "I13")
        .Borders(xlTop).LineStyle = xlSolid
        .Borders(xlRight).LineStyle = xlSolid
        .Borders(xlBottom).LineStyle = xlSolid
        .Borders(xlLeft).LineStyle = xlSolid
    End With
    Range("E13:J13").Font.colorindext = 1
    Range("G13") = 75#
    ' is this part correct??
    
    Backend2.Range("D93") = 1
    Range("E13", "G13", "I13").Locked = True
    Range("E13").Select
End If
End Sub
 
Sub BCurrentYours()
If Not Backend2.Range("B95") Then
    With Range("E15:J15")
        .Borders(xlTop).LineStyle = xlNone
        .Borders(xlRight).LineStyle = xlNone
        .Borders(xlBottom).LineStyle = xlNone
        .Borders(xlLeft).LineStyle = xlNone
        .Font.ColorIndex = 2
    End With
    Range("E15", "G15", "I15").ClearContents
    Range("G15") = 0
    Range("E13", "G13", "I13").Locked = True
Else
    With Range("E15", "G15", "I15")
        .Borders(xlTop).LineStyle = xlSolid
        .Borders(xlRight).LineStyle = xlSolid
        .Borders(xlBottom).LineStyle = xlSolid
        .Borders(xlLeft).LineStyle = xlSolid
    End With
    Range("E15:J15").Font.colorindext = 1
    Range("G13") = 75#
    ' is this part correct??
    
    Backend2.Range("D95") = 1
    Range("E15", "G15", "I15").Locked = True
    Range("E15").Select
End If
End Sub

 

[Briggy719]

Ahhh...thank you. That code is much nicer! I'll see if that works.

 

[Briggy719]

Quick follow-up question: Do you think it makes a difference if you use Range("B93") as opposed to [B93]? I picked up the latter through some other threads and just thought it was better for the sake of being concise but I'm wondering if the different notation could be causing some of my issues. Thanks again!

 

[JackDanIce]

A whilte ago there was a discussion on this and most people seemed to side with Range("A1") being a preferable option to [A1]. Unfortunately, I can't remember what thread that was on and I took it off my subcriptions so can't redirect you to it. In my opinion it makes the code easier to understand, especially when you start using variables in your objects, e.g.
Range("A" & i) when 'i' is taking some variable say from a loop

Each to their own preference though.