worksheets.select doesn't work

D

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
 
B

Brent

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

Brent
 
I

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).
 
D

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
 
I

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top