Prev: Can't break out of code help!
Next: If the cell is empty, copy the value from a specific cell.
From: Dave Peterson on 12 Apr 2010 10:07
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 procedure:-
> > > >
> > > > Worksheets(Worksheets("sheet1").Range("c5").Value)
> > > >
> > > > but the above code doesnt seem to work.
> > > >
> > > > Hope you can help!
> > > >
> > > > Cheers

--

Dave Peterson
First  |  Prev  | 
Pages: 1 2
Prev: Can't break out of code help!
Next: If the cell is empty, copy the value from a specific cell.