Use list box to navigate to different sheets...

  • Thread starter Thread starter MrAlMackay
  • Start date Start date
M

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) )
 
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
 
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.
 
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.
 
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
 
Once I added a continuation character, your original code worked ok for me in my
special version of xl2002 <bg>.
 
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
 
Back
Top