Excel 2007 : Combo/List boxes to navigate to different worksheets...

[Finalfrontier1976]

Hi All,

I would like to produce a list or combo box so that I can select say a name in the drop down box and click a button or whatever to get Excel to go to that worksheet (all in the same workbook) that corresponds with the name I've selected.

Can someone point me in a direction as I'm a bit new at this side of Excel.

Thanks

[Paul]

One option, using a Data Validation drop-down list in a cell. The code below should go into the ThisWorkbook module and will run when the workbook is opened. It will update the data validation list in Sheet1 cell A2 with the current list of worksheet names.

Private Sub Workbook_Open()
Dim str As String, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    str = str & ws.Name & ","
Next ws
With Sheets("Sheet1").Range("A2").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=str
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Invalid Sheet"
    .InputMessage = ""
    .ErrorMessage = "Don't type here, just select a sheet name from the list."
    .ShowInput = True
    .ShowError = True
End With
End Sub

When you select a sheet name from the drop-down list, the code shown below - which should go into the Sheet module for the sheet containing the data validation list - will activate the chosen sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then Sheets(Range("A2").Value).Activate
End Sub

[Marcol]

Possibly use a lisbox on a UserForm.

This is very basic but will work on any amount of sheets, chart sheets will also be included but they will need a command button to show the listbox, ln the meantime select any sheet to continue

To show the list box select Range A1

Cheers

Demo attached

P.S.

Change sheet names as you go and watch the result.

[Finalfrontier1976]

Thanks Marcol,

How did you do this, so that I can do it on other sheets in the future?

Thanks

[Marcol]

Hi

Are you familiar with VBa?

Got to ask, it will depend on your reply how I answer.

[Finalfrontier1976]

Hi,

I have experience with Access VBA, yet to venture fully with Excel though but I'm quite quick on the uptake.

[Marcol]

In the vbEditor, in the project pane (Ctrl+R if it isn't showing), right click on ThisWorkbook select View Code (or just DoubleClick) and Paste all of the following code.

Option Explicit

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim i As Integer
    
    UserForm1.ListBox1.Clear
    For i = 1 To ThisWorkbook.Sheets.Count
        UserForm1.ListBox1.AddItem Sheets(i).Name
    Next
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If TypeName(Sh) = "Chart" Then Exit Sub
    Range("A2").Select
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim isect As Range
    
    Set isect = Application.Intersect(Target, Range("A1"))
    If Not isect Is Nothing Then
        UserForm1.Show
    End If
End Sub

1/. Workbook_NewSheet
This is activated when a new sheet is added.
It clears the ListBox then loops through the sheets in the workbook and adds all the names, including the new one, back into it.

2/. Workbook_SheetActivate
This is activated when a sheet is selected
All this does is check that the sheet is not a chart sheet (a chart sheet has no cells).
If it isn't it selects Range("A2").
This can be any cell except, in this case, "A1" where we want to trigger the Listbox, the reason for this is purely to select any cell other than A1.
It also means that you don't need a button on every sheet, all you need to do is copy the sheet when you need a new one, or format cell A1 on any sheet you import. You'll soon workout how to change the location of this activating cell.

3/. Workbook_SheetSelectionChange
This is activated when a new cell is selected
It uses the Intersect function, possibly the most useful function you can use for triggering code on this event.

Highlight Intersect and press f1 to see how it works (Excel Help)

In this case it is not really needed but it is a good method to know about.
All that is done here is that if A1 is selected then the userform shows'

The code could simply have been

If Target.Address  =  "$A$1" Then UserForm1.Show

4/.The UserForm with one ListBox is very basic.
All it does is read all the sheet names when it is loaded and activate the chosen sheet when clicked.
Because it is unloaded every time it is used, it subsequently is automatically updated when a sheet is added deleted or renamed.
To use this in other existing sheets
a/. Highlight it in the project window Then File > Export File.... Select your directory & save, (call it something identifiable)
b/. To add to someother file. Then in the VB Editor File > Import File....Find the file ( whatever.frm) > Open

That's it! Simples! .........

However you could in future simply use the Demo File as a template for a new file.

Cheers

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.