Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheets.select doesn't work
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheets.select doesn't work
Seems to work on my end, with Excel 2003. Check to make sure you have the
references needed. Brent "Darren Hill" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheets.select doesn't work
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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheets.select doesn't work
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 On Tue, 03 Apr 2007 08:00:28 +0100, IanKR wrote: 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). -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheets.select doesn't work
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't get VBA to work without select | Excel Discussion (Misc queries) | |||
.End(xlRight)).Select Doesn't Work | Excel Programming | |||
Cells.Select won't work!!! | Excel Programming | |||
Why doesn't Selection.End(xlDown).Select always work? | Excel Programming | |||
Why doesn't Selection.End(xlDown).Select always work? | Excel Programming |