Selecting a sheet 
Author Message
 Selecting a sheet

Hi
I'm trying to figure out how to select a sheet in a
workbook.  I have a dialog box asking which workbook to
open.  I would also like a dialog box to ask which
worksheet to select (make active).  This is also to
prevent against a sheet being renamed and the macro not
being able to find it and giving an End-Debug error
message.

Thanks
Art



Thu, 24 Nov 2005 17:29:27 GMT  
 Selecting a sheet
Art,

To select a worksheet, just use
    Worksheets(1).Select
or
    Worksheets("Sheet1").Select.

But I don't think you need to worry about sheets being renamed. As well as a
Name property, worksheets have a Codename property. You cannot change the
Codename except through VBA, so you could use this. If you go into the VB
IDE, against each sheet you will see its codename. The first name is its
codename, the name in brackets is the name you see in Excel and which a user
can change. When a  worksheet is first created, its worksheet name and its
code name are the same, but either can be changed.

To access via its codename, use
    Sheet1.Select
i.e. no reference to the worksheets collection.

--
    HTH

    -------

    Bob Phillips
    ... looking out across Poole Harbour to the Purbecks


Quote:
> Hi
> I'm trying to figure out how to select a sheet in a
> workbook.  I have a dialog box asking which workbook to
> open.  I would also like a dialog box to ask which
> worksheet to select (make active).  This is also to
> prevent against a sheet being renamed and the macro not
> being able to find it and giving an End-Debug error
> message.

> Thanks
> Art



Thu, 24 Nov 2005 17:49:24 GMT  
 Selecting a sheet
Thanks Bob.  It wasn't quite what I was looking for, but
then again, there is no dialog box in Excel to choose from
sheet tabs.  This does work.  

Art

Quote:
>-----Original Message-----
>Art,

>To select a worksheet, just use
>    Worksheets(1).Select
>or
>    Worksheets("Sheet1").Select.

>But I don't think you need to worry about sheets being

renamed. As well as a
Quote:
>Name property, worksheets have a Codename property. You
cannot change the
>Codename except through VBA, so you could use this. If
you go into the VB
>IDE, against each sheet you will see its codename. The
first name is its
>codename, the name in brackets is the name you see in

Excel and which a user
Quote:
>can change. When a  worksheet is first created, its

worksheet name and its
Quote:
>code name are the same, but either can be changed.

>To access via its codename, use
>    Sheet1.Select
>i.e. no reference to the worksheets collection.

>--
>    HTH

>    -------

>    Bob Phillips
>    ... looking out across Poole Harbour to the Purbecks



>> Hi
>> I'm trying to figure out how to select a sheet in a
>> workbook.  I have a dialog box asking which workbook to
>> open.  I would also like a dialog box to ask which
>> worksheet to select (make active).  This is also to
>> prevent against a sheet being renamed and the macro not
>> being able to find it and giving an End-Debug error
>> message.

>> Thanks
>> Art

>.



Thu, 24 Nov 2005 20:07:48 GMT  
 Selecting a sheet
Do you mean this Art

Sub SheetList_CP()
  'Chip Pearson, 2002-10-29, misc.
  On Error Resume Next
  Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
  Application.CommandBars("Workbook Tabs").ShowPopup
  On Error GoTo 0
End Sub

If you protect (Tools>Protection>Protect Workbook) your workbook the sheetnames can't be changed or
sheets can't be delete.

--
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl

Quote:

> Thanks Bob.  It wasn't quite what I was looking for, but
> then again, there is no dialog box in Excel to choose from
> sheet tabs.  This does work.

> Art

> >-----Original Message-----
> >Art,

> >To select a worksheet, just use
> >    Worksheets(1).Select
> >or
> >    Worksheets("Sheet1").Select.

> >But I don't think you need to worry about sheets being
> renamed. As well as a
> >Name property, worksheets have a Codename property. You
> cannot change the
> >Codename except through VBA, so you could use this. If
> you go into the VB
> >IDE, against each sheet you will see its codename. The
> first name is its
> >codename, the name in brackets is the name you see in
> Excel and which a user
> >can change. When a  worksheet is first created, its
> worksheet name and its
> >code name are the same, but either can be changed.

> >To access via its codename, use
> >    Sheet1.Select
> >i.e. no reference to the worksheets collection.

> >--
> >    HTH

> >    -------

> >    Bob Phillips
> >    ... looking out across Poole Harbour to the Purbecks



> >> Hi
> >> I'm trying to figure out how to select a sheet in a
> >> workbook.  I have a dialog box asking which workbook to
> >> open.  I would also like a dialog box to ask which
> >> worksheet to select (make active).  This is also to
> >> prevent against a sheet being renamed and the macro not
> >> being able to find it and giving an End-Debug error
> >> message.

> >> Thanks
> >> Art

> >.



Thu, 24 Nov 2005 20:23:38 GMT  
 Selecting a sheet
That is pretty much what I mean.  The only problem is that
every time I run it, Excel crashes.  Oh well.  I'll try it
on another computer and see what happens.  Thanks!

Art

Quote:
>-----Original Message-----
>Do you mean this Art

>Sub SheetList_CP()
>  'Chip Pearson, 2002-10-29, misc.
>  On Error Resume Next
>  Application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
>  Application.CommandBars("Workbook Tabs").ShowPopup
>  On Error GoTo 0
>End Sub

>If you protect (Tools>Protection>Protect Workbook) your

workbook the sheetnames can't be changed or
Quote:
>sheets can't be delete.

>--
>Regards Ron de Bruin
>(Win XP Pro SP-1  XL2002 SP-2)
>www.rondebruin.nl




Quote:
>> Thanks Bob.  It wasn't quite what I was looking for, but
>> then again, there is no dialog box in Excel to choose
from
>> sheet tabs.  This does work.

>> Art

>> >-----Original Message-----
>> >Art,

>> >To select a worksheet, just use
>> >    Worksheets(1).Select
>> >or
>> >    Worksheets("Sheet1").Select.

>> >But I don't think you need to worry about sheets being
>> renamed. As well as a
>> >Name property, worksheets have a Codename property. You
>> cannot change the
>> >Codename except through VBA, so you could use this. If
>> you go into the VB
>> >IDE, against each sheet you will see its codename. The
>> first name is its
>> >codename, the name in brackets is the name you see in
>> Excel and which a user
>> >can change. When a  worksheet is first created, its
>> worksheet name and its
>> >code name are the same, but either can be changed.

>> >To access via its codename, use
>> >    Sheet1.Select
>> >i.e. no reference to the worksheets collection.

>> >--
>> >    HTH

>> >    -------

>> >    Bob Phillips
>> >    ... looking out across Poole Harbour to the
Purbecks



>> >> Hi
>> >> I'm trying to figure out how to select a sheet in a
>> >> workbook.  I have a dialog box asking which workbook
to
>> >> open.  I would also like a dialog box to ask which
>> >> worksheet to select (make active).  This is also to
>> >> prevent against a sheet being renamed and the macro
not
>> >> being able to find it and giving an End-Debug error
>> >> message.

>> >> Thanks
>> >> Art

>> >.

>.



Fri, 25 Nov 2005 00:51:38 GMT  
 Selecting a sheet
Hi Art,

If you want to show in built dialogbox for sheet activate, here is some
method

1. Find control & execute.

Sub moresheets()
CommandBars.FindControl(Type:= _
        msoControlButton, ID:=957).Execute
End Sub

2. Run this macro to add control to standard toolbar &
afterword you have to click this button to activate dialog.

Sub AddControl()
'add button to commandbar for sheet activate dialogbox.

Set cnt = CommandBars("Standard").Controls.Add _
(Type:=msoControlButton, before:=1, ID:=957)

End Sub

3. Using sendkeys: More then 16 sheet then show activate
dialogbox else popup sheet name.

Sub SheetActivate()
'assigne shortcut Key

If Application.CommandBars("workbook tabs").Controls
(16).Caption Like "More Sheets*" Then
Application.SendKeys "{end}~"

Application.CommandBars("workbook tabs").ShowPopup

End Sub

Regards,
Shah Shailesh

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Fri, 25 Nov 2005 10:20:06 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. How to quickly select a sheet in workbook with many sheets

2. Why I need to select a sheet before selecting a range

3. sort without selecting the sheet

4. Selecting Multiple Sheets

5. Select several sheets in Excel from VBA

6. Select multiple sheets ?

7. a program to select many sheets

8. Select Each Sheet in File

9. Can you filter a list without selecting the sheet that contains it

10. Select Partial Sheet


 
Powered by phpBB® Forum Software © phpBB Group