How to open a specific worksheet via code from workbook_open

[Martin Parker]

I have a combobox which is populated with all the worksheets within the
workbook. The controlsource of the combobox is linked to cell c5 on Sheet1

My question is how do i make the Excel workbook open at the worksheet which
is detailed within the combobox linked cell.

I've been previously using in the Workbook_Open procedure:-

Worksheets(Worksheets("sheet1").Range("c5").Value)

but the above code doesnt seem to work.

Hope you can help!

Cheers

 

[ozgrid.com]

Private Sub Workbook_Open()
'Sheet CodeName
'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Sheet1.Activate
End Sub

 

[JLatham]

Worksheets(Worksheets("sheet1").Range("c5").Value).Activate

 

[Martin Parker]

Thanks that works great, however, one problem, if the user has previously
closed the app on the worksheet the code is referring to, I get a runtime
error on workbook open.

How would i code to check if the worksheet requested to open up at workbook
open is already activated?

Hope this makes sense!

Cheers

 

[Martin Parker]

This is the code i'm using that gets a runtime error application defined or
object defined error:-

If ActiveSheet.Name = Worksheets("sheet1").Range("c5") Then
MsgBox "Already here on... " & ActiveSheet.Name
Else
'MsgBox "Not here!"
Worksheets(Worksheets("sheet1").Range("c5").Value).Activate
End If

Hope you guys can help!

 

[Dave Peterson]

I'd use something like:

Option Explicit
Private Sub Workbook_Open()
On Error Resume Next
Me.Worksheets(cstr(Me.Worksheets("sheet1").Range("c5").Value)).Select
If Err.Number <> 0 Then
Err.Clear
MsgBox "Couldn't go to the sheet!"
End If
On Error GoTo 0
End Sub

There are a couple of reasons this could break.

You could have a bad name in Sheet1!c5 -- maybe the sheet has been deleted or
the value in the cell isn't legal (like a date mm/dd/yyyy) or you the worksheets
are named 1, 2, 3, ... and the value in the cell is numeric.

Or maybe Sheet1 doesn't exist anymore.