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 procedu-

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

but the above code doesnt seem to work.

Hope you can help!

Cheers

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



"Martin Parker" wrote in message
...
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 procedu-

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

but the above code doesnt seem to work.

Hope you can help!

Cheers

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


"Martin Parker" wrote:

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 procedu-

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

"JLatham" wrote:

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


"Martin Parker" wrote:

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 procedu-

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

but the above code doesnt seem to work.

Hope you can help!

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!

"Martin Parker" wrote:

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

"JLatham" wrote:

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


"Martin Parker" wrote:

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 procedu-

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

but the above code doesnt seem to work.

Hope you can help!

Cheers

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.



Martin Parker wrote:

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!

"Martin Parker" wrote:

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

"JLatham" wrote:

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


"Martin Parker" wrote:

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 procedu-

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

but the above code doesnt seem to work.

Hope you can help!

Cheers

--

Dave Peterson