Prev: Can't break out of code help!
Next: If the cell is empty, copy the value from a specific cell.
From: Martin Parker on 11 Apr 2010 22:47
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
From: ozgrid.com on 11 Apr 2010 22:56
Private Sub Workbook_Open()
'Sheet CodeName
'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Sheet1.Activate
End Sub



"Martin Parker" <MartinParker(a)discussions.microsoft.com> wrote in message
news:DCA62FAD-48C6-4E9A-9768-53B9006A95B1(a)microsoft.com...
>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

From: JLatham on 12 Apr 2010 01:52
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
From: Martin Parker on 12 Apr 2010 09:20
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
From: Martin Parker on 12 Apr 2010 09:46
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
 |  Next  |  Last
Pages: 1 2
Prev: Can't break out of code help!
Next: If the cell is empty, copy the value from a specific cell.