Use list box to navigate to different sheets...
 

Is it possible to use a List Box within a spreadsheet (this would be on each
page) - that by selecting the first option in the list (e.g. Sheet 1, then
Sheet 2, etc.) it would navigate to each sheet?

Trying to put the finishing touches to a spreadsheet - navigating round the
different sheets needs to be 'easier' for everyone - this way I can have a
descriptive title within the List box for each area.

As always your time, and expertise is greatly appreciated.

Many Thanks, Al. ( )

Use list box to navigate to different sheets...
 

If you're using an ActiveX type listbox you can catch the Click event with
something like:

Worksheets(lstErrors.ListIndex).Select

Where lstErrors is the name of the listbox you're using
--

Regards,


Bill Lunney
www.billlunney.com

"MrAlMackay" wrote in message
...
Is it possible to use a List Box within a spreadsheet (this would be on
each
page) - that by selecting the first option in the list (e.g. Sheet 1, then
Sheet 2, etc.) it would navigate to each sheet?

Trying to put the finishing touches to a spreadsheet - navigating round
the
different sheets needs to be 'easier' for everyone - this way I can have a
descriptive title within the List box for each area.

As always your time, and expertise is greatly appreciated.

Many Thanks, Al. ( )

Use list box to navigate to different sheets...
 

Mr AL,

If you right-click on the arrows to the left of the sheet tab names, you get
a sheet name list, effectively a listbox. Your users can select from there,
and if you give the sheets descriptive names all solved without code or
trickery.

--
HTH

-------

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


"MrAlMackay" wrote in message
...
Is it possible to use a List Box within a spreadsheet (this would be on
each
page) - that by selecting the first option in the list (e.g. Sheet 1, then
Sheet 2, etc.) it would navigate to each sheet?

Trying to put the finishing touches to a spreadsheet - navigating round
the
different sheets needs to be 'easier' for everyone - this way I can have a
descriptive title within the List box for each area.

As always your time, and expertise is greatly appreciated.

Many Thanks, Al. ( )

Use list box to navigate to different sheets...
 

How do I actually use this? Should this be part of a macro, or through VBA?

How do I know if it is an ActiveX listbox type?

Thanks for your help on this.

Al.

Use list box to navigate to different sheets...
 

OK, Application.goto seems to be adversely affected by being in the sheet
module even though the range is qualified.

try it this way:

Private Sub ListSheets_Click()
On Error Resume Next
Set sh = ThisWorkbook.Worksheets(Listsheets.Value)
On Error GoTo 0
If Not sh Is Nothing Then
sh.Activate
sh.Range("A1").Select
End If
End Sub

--
Regards,
Tom Ogilvy

Fede Querio wrote in message
...
Tom,
I'm afraid it didn't work....are you sure the code is ok?
Federico


"Tom Ogilvy" wrote in message
...
If you add it from the control toolbox toolbar, then it is an activeX
listbox. Right after you add it, then you will be in design mode.
Double
click on the listbox and you will be taken to the click event (if you
are
going to rename the listbox, do that first by right clicking on it an
selecting properties. Then type in the new name. Let's assume your
name
it
listSheets)

Private Sub ListSheets_Click()
On Error Resume Next
set sh = thisWorkbook.worksheets(ListSheets.Value)
On Error goto 0
if not sh is nothing then
Application.goto sh.Range("A1")
End sub

You can also populate the listbox with the activate event in the same
module.

Private Sub Worksheet_Activate()
Listsheets.clear
for each sh in thisworkbook.Worksheets
if sh.name < me.name then
listsheets.additem sh.name
end if
Next
End sub


On the control toolbox toolbar, the upper left icon toggles design mode.

You need to get out of design mode for the listbox code to work.

Regards,
Tom Ogilvy


MrAlMackay wrote in message
...
How do I actually use this? Should this be part of a macro, or
through
VBA?

How do I know if it is an ActiveX listbox type?

Thanks for your help on this.

Al.

Use list box to navigate to different sheets...
 

Once I added a continuation character, your original code worked ok for me in my
special version of xl2002 <bg.



Tom Ogilvy wrote:

OK, Application.goto seems to be adversely affected by being in the sheet
module even though the range is qualified.

try it this way:

Private Sub ListSheets_Click()
On Error Resume Next
Set sh = ThisWorkbook.Worksheets(Listsheets.Value)
On Error GoTo 0
If Not sh Is Nothing Then
sh.Activate
sh.Range("A1").Select
End If
End Sub

--
Regards,
Tom Ogilvy

Fede Querio wrote in message
...
Tom,
I'm afraid it didn't work....are you sure the code is ok?
Federico


"Tom Ogilvy" wrote in message
...
If you add it from the control toolbox toolbar, then it is an activeX
listbox. Right after you add it, then you will be in design mode.
Double
click on the listbox and you will be taken to the click event (if you
are
going to rename the listbox, do that first by right clicking on it an
selecting properties. Then type in the new name. Let's assume your
name
it
listSheets)

Private Sub ListSheets_Click()
On Error Resume Next
set sh = thisWorkbook.worksheets(ListSheets.Value)
On Error goto 0
if not sh is nothing then
Application.goto sh.Range("A1")
End sub

You can also populate the listbox with the activate event in the same
module.

Private Sub Worksheet_Activate()
Listsheets.clear
for each sh in thisworkbook.Worksheets
if sh.name < me.name then
listsheets.additem sh.name
end if
Next
End sub


On the control toolbox toolbar, the upper left icon toggles design mode.

You need to get out of design mode for the listbox code to work.

Regards,
Tom Ogilvy


MrAlMackay wrote in message
...
How do I actually use this? Should this be part of a macro, or
through
VBA?

How do I know if it is an ActiveX listbox type?

Thanks for your help on this.

Al.





--

Dave Peterson

Use list box to navigate to different sheets...
 

It didn't for me in Excel 97 (and the syntax was correct)

Guess they must have fixed it.

Also, can you spell word wrap. <g I didn't leave off the end if

Regards,
Tom Ogilvy



Dave Peterson wrote in message
...
Once I added a continuation character, your original code worked ok for me
in my
special version of xl2002 <bg.



Tom Ogilvy wrote:

OK, Application.goto seems to be adversely affected by being in the
sheet
module even though the range is qualified.

try it this way:

Private Sub ListSheets_Click()
On Error Resume Next
Set sh = ThisWorkbook.Worksheets(Listsheets.Value)
On Error GoTo 0
If Not sh Is Nothing Then
sh.Activate
sh.Range("A1").Select
End If
End Sub

--
Regards,
Tom Ogilvy

Fede Querio wrote in message
...
Tom,
I'm afraid it didn't work....are you sure the code is ok?
Federico


"Tom Ogilvy" wrote in message
...
If you add it from the control toolbox toolbar, then it is an
activeX
listbox. Right after you add it, then you will be in design mode.
Double
click on the listbox and you will be taken to the click event (if
you
are
going to rename the listbox, do that first by right clicking on it
an
selecting properties. Then type in the new name. Let's assume your
name
it
listSheets)

Private Sub ListSheets_Click()
On Error Resume Next
set sh = thisWorkbook.worksheets(ListSheets.Value)
On Error goto 0
if not sh is nothing then
Application.goto sh.Range("A1")
End sub

You can also populate the listbox with the activate event in the
same
module.

Private Sub Worksheet_Activate()
Listsheets.clear
for each sh in thisworkbook.Worksheets
if sh.name < me.name then
listsheets.additem sh.name
end if
Next
End sub


On the control toolbox toolbar, the upper left icon toggles design
mode.

You need to get out of design mode for the listbox code to work.

Regards,
Tom Ogilvy


MrAlMackay wrote in message
...
How do I actually use this? Should this be part of a macro, or
through
VBA?

How do I know if it is an ActiveX listbox type?

Thanks for your help on this.

Al.





--

Dave Peterson