VBA Error if more than 1x spreadsheet open

[tlc53]

Hi,

I have code in my main spreadsheet which works fine, no problems at all, until I open another spreadsheet. If two or more spreadsheets are open and I do anything in any of the spreadsheets (select from drop down menu for example) it produces "Run time error 9 - Subscript out of range" referring to my main spreadsheet. It highlights the first line in the below code. Originally I had it referring to ActiveSheet but changed it to the actual sheet name "Q2" but that has not stopped this error from occurring. Does anyone know why this keeps happening and how I can fix it? Thank you

Private Sub Worksheet_Calculate()

    If Sheets("Q2").Range("D8").Value = 1 Then
        Sheets("Q2").Shapes("Check Box 2").Visible = False
    Else
        Sheets("Q2").Shapes("Check Box 2").Visible = True
    End If
    
    
    If Sheets("Q2").Range("D8").Value = 3 Then
        Sheets("Q2").Shapes("Check Box 4").Visible = False
    Else
        Sheets("Q2").Shapes("Check Box 4").Visible = True
    End If
            
            
    If Sheets("Q2").Range("D8").Value = 5 Then
        Sheets("Q2").Shapes("Check Box 6").Visible = False
    Else
        Sheets("Q2").Shapes("Check Box 6").Visible = True
    End If
        
        
    If Sheets("Q2").Range("D8").Value = 7 Then
        Sheets("Q2").Shapes("Check Box 8").Visible = False
    Else
        Sheets("Q2").Shapes("Check Box 8").Visible = True
    End If
        
        
    If Sheets("Q2").Range("D8").Value = 9 Then
        Sheets("Q2").Shapes("Check Box 10").Visible = False
    Else
        Sheets("Q2").Shapes("Check Box 10").Visible = True
    End If
        
        
    If Sheets("Q2").Range("D8").Value = 11 Then
        Sheets("Q2").Shapes("Check Box 12").Visible = False
    Else
        Sheets("Q2").Shapes("Check Box 12").Visible = True
    End If
    
        
    If Sheets("Q2").Range("D8").Value = 13 Then
        Sheets("Q2").Shapes("Check Box 14").Visible = False
    Else
        Sheets("Q2").Shapes("Check Box 14").Visible = True
    End If
    
        
    If Sheets("Q2").Range("D8").Value = 15 Then
        Sheets("Q2").Shapes("Check Box 16").Visible = False
    Else
        Sheets("Q2").Shapes("Check Box 16").Visible = True
    End If
    
        
    If Sheets("Q2").Range("D8").Value = 17 Then
        Sheets("Q2").Shapes("Check Box 18").Visible = False
    Else
        Sheets("Q2").Shapes("Check Box 18").Visible = True
    End If
    
        
    If Sheets("Q2").Range("D8").Value = 19 Then
        Sheets("Q2").Shapes("Check Box 20").Visible = False
    Else
        Sheets("Q2").Shapes("Check Box 20").Visible = True
    End If
End Sub

 

[MARK858]

Make Sheets("Q2") the activesheet, run the code below then copy & paste the result that appears in the Immediate window in the thread (please copy and paste the result, do not retype it).

Sub testit()
Debug.Print "| &"; ActiveSheet.Name; "& |", Len(ActiveSheet.Name)
End Sub

 

[tlc53]

Make Sheets("Q2") the activesheet, run the code below

I hope I did that right! It came back with the following: | &Q2& | 2

 

[MARK858]

What you have posted is correct. When you stated that the first line is highlighted did you mean
This line

Private Sub Worksheet_Calculate()

or
This line

If Sheets("Q2").Range("D8").Value = 1 Then

 

[tlc53]

Sorry, the second line;

    If Sheets("Q2").Range("D8").Value = 1 Then

 

[MARK858]

Try changing the line to

If ThisWorkbook.Sheets("Q2").Range("D8").Value = 1 Then

 

[tlc53]

That's done the trick. Thank you!!

 

[MARK858]

You're welcome, obviously if you haven't already then you will have to append ThisWorkbook to everywhere else that you have Sheets("Q2").

 

[tlc53]

You're welcome, obviously if you haven't already then you will have to append ThisWorkbook to everywhere else that you have Sheets("Q2").

Yes. Originally I just applied it to the first line but I soon gathered it needed to added to every line referring to Sheets("Q2"). Works a charm now.