Selection box via a Macro.

[Lord Bowler]

Is there a way to display a selection box of available tabs that fit certain criteria to the user?

I have a workbook that has up to 12 tabs. Most of which are data tabs extracted from a database that is used to populate the "Template" tab. I want to display a list of Template tabs in this book for the user to choose to run the macro against and populate this template with data from the various other tabs in the sheet.

Currently I get the tab name via an Input dialog box, but I'd like to remove user interaction as much as possible, leaving them to just clicking Yes/No/Ok.

Thanks in advance,

 

[Norie]

You want to remove user interaction but you seem to want to allow the user to select worksheets from a list.

Isn't that a bit wrong way round?

If you do want to display worksheet names in some sort of list then a userform is probably the best idea.

 

[Opus]

can you show some code

 

[Lord Bowler]

I'm looking at this code again for the new School Year:

This is the code I'm using to get the sheet so I can set it to active so the Macro does not use the wrong sheet:

    ' Get Sheet Name from User. For generic code.
     templateSheet = InputBox("Enter Sheet Name", "Template Sheet Input")
    ' Public Variable set in lieu of passing this Variable
    ' templateSheet = "AOE Template"
    ' Test for null if Cancel is clicked.
    If templateSheet = "" Then
        Exit Sub
    Else
        Worksheets(templateSheet).Activate
    End If

Examples of sheet names are: sheet1 (currently used for reference), Refkeys (our list of keys used on the Template Tab), 2010-11 (Extract Data), 2009-10 (Extracts), 2008-09 (etc...), AOE Template (Each Template is labeled for the report).

I want to make the last sheet active first without user interaction.