VBA: Error 9; subscript is out of reach Solved/Closed

Question

Hello,

Depending on the value of a specific cell (sheet "gegevensblad" cell L10), I would like to hide 1 out of 2 sheets (Either Overzichtsgrafieken 1 or Overzichtgrafieken 2).

The value in cell L10 can be either "Ja" or "Nee".
If value is "Ja" then hide sheet "Overzichtsgrafieken 1"
Else hide sheet "Overzichtsgrafieken 2" (or: if value is "Nee" then hide sheet "Overzichtsgrafieken 2")

I have come up with the following code:
Sub HideGraphicSheet()
If Sheets("Gegevensblad").Range("L10") = Ja Then Sheets("Overzichtsgrafieken 1").Visible = False Else Sheets("Overzichtsgrafieken 2").Visible = False 
End Sub

(in VBA the code from "If sheets" till "= False" is on one line)

When running the code it gives error 9: subscript is out of reach. The part after Else is coated in yellow.

What am I doing wrong?
Can anyone provide me with a code that works?

Thanks in advance,
Trowa

Dean · Answer

Noticed this and I think I can help.  Your trying to read a sheet that is not there or your not calling it correctly. Say your reading Sheet 3 which is 2 in the sheets collection, or check the spelling.

send a sinpit of code if you cannot get it form here
Dean

Dean · Answer

If Sheets("Sheet1").Range("A1") = "JA" Then Sheets(1).Visible = False '"Sheet2" is Sheets(1)

This works for me, I did notice that your JA is not in Quotes

Please dbl check your Sheet ref.

Dean

Trowa · Answer

Thanks Dean,

Both comments were spot on: I forgot the quotes (thought they were not necessary) and made a typo in one of the sheets name.

Thanks again,
Trowa

VBA: Error 9; subscript is out of reach

3 responses

Similar discussions

Subscribe To Our Newsletter!