Hide sheets macro - "Run-time error '9': Subscript out of range" error

[Cflaursen]

Hi all

I need to program a macro that will hide certain sheets on workbook startup depending on a certain cell and if that certain cell is changed while the workbook is open. To accomplish this I have written the following macro.


Private Sub Workbook_Open()
'Macro to hide either Clarity or CTR related sheets'
If Sheets("Sheet2").Range("BD2") = "CTR" Then
Sheets("Sheet7").Visible = xlVeryHidden
Sheets("Sheet17").Visible = xlVeryHidden
Sheets("Sheet16").Visible = xlVeryHidden
Sheets("Sheet26").Visible = xlVeryHidden
Sheets("Sheet27").Visible = xlVeryHidden

ElseIf Sheets("Sheet2").Range("BD2") = "Clarity" Then
Sheets("sheet8").Visible = xlVeryHidden
Sheets("sheet19").Visible = xlVeryHidden
Sheets("sheet18").Visible = xlVeryHidden
Sheets("sheet23").Visible = xlVeryHidden
Sheets("sheet22").Visible = xlVeryHidden
Else
Exit Sub
End If
End Sub


However, when I run the macro I get the following error "Run-time error '9': Subscript out of range". I can't figure out why - hope someone can help me out?

I have tried using the "Auto_open" instead but gets the same problem. Can it be because I just use the generic sheet names or the total (real) sheet names?

Br, CFL

 

[jkpieterse]

I expect one of the sheets you are trying to hide is not in your workbook.

 

[WhoCanDo]

Use this:

On Error Resume Next

before "if" statement and:

On Error Goto 0

after "end if" if the sheet names are changing.

 

[Cflaursen]

Hi all

Thank you for the replies.

I have double checked that sheets exist.

Furthermore sheet names are static!

WhoCanDo: What exactly does your suggestion do? I have implemented your solution are don't get the error anymore but the macro doesn't perform the wanted action...

Br, CFL

 

[WhoCanDo]

"On Error Resume Next" stops Excel telling you there is an error and continues on.

"On Error goto 0" activates error reports again for the rest of your code.

It is not a solution to why there is an error but the error is not causing a problem so why not ignore it.

I agree with jkpieterse but if you say that all names are correct and you can't solve it then, dare I say, ignore it.

 

[jkpieterse]

Which line causes the error?

 

[Marcelo Branco]

Maybe there is a space in some name. Something like
Sheet 7 instead of Sheet7

M.

 

[WhoCanDo]

Can it be because I just use the generic sheet names or the total (real) sheet names?

Br, CFL

Sorry Cflaursen, just read your question properly.

Yes it is because you need to use the names of the sheets when you use the "Sheets" function or you can use "Sheets(1)" as Excel creates them but that can be confusing. eg. Sheet1 (test) - Sheets("test") or Sheets(1)

Regards