worksheets.select doesn't work

[Darren Hill]

The following code keeps failing on the last line, the one after On Error:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case Left(ws.Name, InStr(ws.Name, "_"))
Case "GM_"
ws.Visible = xlSheetVisible
Case "Data_"
ws.Visible = xlSheetHidden
Case "VBA_"
ws.Visible = xlSheetVeryHidden
End Select
Next ws
On Error Resume Next
ThisWorkbook.Worksheets("Front Page").Select ' <<< - fails here
End Sub

If I don't have the On Error statement in there, it crashes with a
Runtime error '57121"
Application-defined or object-defined error

Darren

 

[Guest]

Seems to work on my end, with Excel 2003. Check to make sure you have the
references needed.

Brent

 

[IanKR]

The following code keeps failing on the last line, the one after On

Error:
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case Left(ws.Name, InStr(ws.Name, "_"))
Case "GM_"
ws.Visible = xlSheetVisible
Case "Data_"
ws.Visible = xlSheetHidden
Case "VBA_"
ws.Visible = xlSheetVeryHidden
End Select
Next ws
On Error Resume Next
ThisWorkbook.Worksheets("Front Page").Select ' <<< - fails here
End Sub

If I don't have the On Error statement in there, it crashes with a
Runtime error '57121"
Application-defined or object-defined error

Darren

Try preceding

ThisWorkbook.Worksheets("Front Page").Select

with

ThisWorkbook.Worksheets("Front Page").Activate

(or use the .Activate line instead).

 

[Darren Hill]

I'm sorry it took so long to get back to you, I've had a frustrating
couple of days with excel constantly crashing.

Now that it's stopped crashing, I haven't been able to reproduce my
original error.
I've switched over to using the Activate method anyway, just to be on the
safe side.

Thanks for pointing it out to me,

Darren

 

[IanKR]

I'm sorry it took so long to get back to you, I've had a frustrating

couple of days with excel constantly crashing.

Now that it's stopped crashing, I haven't been able to reproduce my
original error.
I've switched over to using the Activate method anyway, just to be on
the safe side.

Thanks for pointing it out to me,

You're welcome. It may not be what was causing your problem. I do know that
if you try and select a range without having first activated the containing
worksheet, VB throws up an error. I know your code selects a worksheet
rather than a range, but it might be relevant!
Ian