Use list box to navigate to different sheets...

[MrAlMackay]

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. ( (e-mail address removed) )

 

[Bill Lunney]

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

 

[Bob Phillips]

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.

 

[MrAlMackay]

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.

 

[Tom Ogilvy]

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

 

[Dave Peterson]

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

 

[Tom Ogilvy]

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