How to open a specific worksheet via code from workbook_open

  • Thread starter Thread starter Martin Parker
  • Start date Start date
M

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
 
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
 
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!
 
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.
 
Back
Top