combobox to select worksheet

[Lee24]

Hi,

Can someone point me i the correct direction of how to use a combobox on sheet 1 of excel to automatically go to sheet 2 / 3 / 4 etc etc.

Many Thanks

L

 

[SkipVought]

Hi,

So the Sheet List hint was not good enough?

1. Make a list of worksheets and give it a Range Name. faq68-1331

2. Pick the cell you want the Drop Down in and Data > Validation -- LIST and enter the EQUALS sign, followed by your List Range Name. When users select this cell, a dropdown arrow will appear and you can make a selection.

3. Give this cell a Range Name. I'm calling mine, SelectedSheet

4. COPY this code. Right-Click the Sheet Tab and select View Code and PASTE into the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [SelectedSheet]) Is Nothing Then
        Sheets([SelectedSheet].Value).Activate
    End If
End Sub

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lee24]

Hi, must be missing something here....

Is there anyway that the drop down arrow can be displayed on screen rather than appear when they click on it?

Also followed your instructions and nothing happens when I select an option.

Thanks

L

 

[SkipVought]

Also followed your instructions and nothing happens when I select an option.

Option WHERE?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lee24]

Sorry, when the user (me) clicks on the cell the list appears, when I select something from the list I thought it would open up the relevant worksheet?

sORRY

 

[SkipVought]

Did you paste the code into the sheet object as instructed?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lee24]

Yes...

The drop down works as I can see my worksheet names, just when i select one it changes in the box but that sheet does not open

do you have a working example you could send me?

thanks

 

[SkipVought]

Did you NAME the cell range where the Data Verification Drop Down box is, SelectedSheet?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lee24]

yes, tried again

sorry

 

[SkipVought]

If you did those things AND you selected a valid sheet name. the procedure will activate the SelectedSheet.

Also, have you enabled MACROS in your workbook?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lee24]

ah maybe enable macros, how do i do that?

 

[SkipVought]

Easiest way is to save and close.

Reopen and answer whatever question to enable macros as YES.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lee24]

Bingo......


Thanks, i am just going to a meeting just now i will let you know how i get on

Thanks Again