EXCEL VBA: Combo box to list active workbook sheets

I'm working on a workbook template to be used for project profitability tracking.  Users will use one sheet ("Plan Input") to input and update planned costs.  Periodically, they will click a button to run a VBA procedure that asks the user for a sheet name ("Proposal," "Worst Case," "Scope Change 1," etc.), copies the Plan Input sheet, applies the name, converts all formulas to values, and protects the new sheet.  Other sheets will allow the user to input actual costs and actual billings to the client.

On an Analysis sheet, I have a series of formulas that compare actual costs and billings to plan.  I want to allow users to select which version of the plan they want to use for comparison.  My thought is to provide a combo box that lists all of the sheets in the active workbook.  I would then use the selected value in that combo box to modify the formulas that depend on it.

I have two requests:  First, could someone help me with the code to build that combo box?  Second, if there's a more elegant approach to this problem, please let me know what you recommend.

I always get such good advice, and add a few new techniques to my skillset, every time I ask this forum for advice.  Thanks in advance for your help on this question.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hello BarbMpls,

just listening here for others to jump in

seems a bit tricky
-building that combo shouldn't be the problem
-writing the replace in the formula's can be done with a named cell that holds the chosen value in the combo

BarbMplsAuthor Commented:

Actually, for me, the combo box IS the problem.  I'm sure I could work it out eventually, but was hoping to save myself a few hours of false starts.  Do you (or does someone else) have suggested code?

  Add a combo box control to the selected sheet, and then you can modify my simplified code to fit your needs:

Private Sub Workbook_Open()
  Dim varSelect
  Set varSelect = Sheet1.cboSelect
  With varSelect
    .AddItem "Sheet1", 0
    .AddItem "Sheet2", 1
    .AddItem "Sheet3", 2
  End With
End Sub

Private Sub cboSelect_Change()
  Select Case cboSelect.Text
    Case "Sheet1"
    Case "Sheet2"
    Case "Sheet3"
  End Select
End Sub

Hopefully this will get you started.  If you have any other questions, feel free to ask.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

just going forward on that code from Mike

Private Sub Workbook_Open()
Dim s As Worksheet
Dim varSelect
Dim i As Integer
  i = 0
  Set varSelect = Sheet1.cboSelect
  With varSelect
    For Each s In Worksheets
      .AddItem s.Name, i
      i = i + 1
    Next s
  End With
End Sub

I'm not sure I understand the problem. But I'll give it a try anyway.

If I were in that situation, I would place an ActiveX combo box on top of a cell. (say, "C2") Then I would set the "LinkedCell" property of the combobox to "C2". (That would mean that it would populate "C2" with the value that you select from the combo box. Then I would use INDIRECT in all my formulas and they would read from "C2", so every time I changed the combo box, all the formulas would update to the value I chose.

Then, to populate the combo box, I would trigger this script. It would clear out all the old items, and add in the name of each sheet in the current workbook to the list.

Sub Test()

    Dim ShtNm As String
    ComboBox1.Value = ""
        ComboBox1.RemoveItem (ListCount)
    Loop Until ComboBox1.ListCount = 0
    For I = 1 To Sheets.Count
        ShtNm = Sheets(I).Name
        ComboBox1.AddItem ShtNm
    Next I

End Sub


To trigger this,.. you can use a Workbook_Open sub (so it runs whenever you open the workbook)... or you can have it run any time one of the sheets get updated, or you can trigger it manually. (whatever you want)

Let me know if this is what you're talking about. Thanks.

Rather than use a combo, I would use a list box. Not for any other reason than it's something I've used before.

I would guess that you could build a user form ok? Assuming you can, create a user form with a list box and poupulate the list thus:

Private Sub UserForm_Initialize()
  Private Sub UserForm_Initialize()
    Dim data() As String
    Dim ShtCnt As Integer
    Dim Shtnum As Integer
    Dim Sht As Object
    Dim ListPos As Integer
    Set OriginalSheet = ActiveSheet
    ShtCnt = ActiveWorkbook.Sheets.Count
    ReDim data(1 To ShtCnt, 1 To 2)
    Shtnum = 1
    For Each Sht In ActiveWorkbook.Sheets
        If Sht.Name = ActiveSheet.Name Then _
          ListPos = Shtnum - 1
        data(Shtnum, 1) = Sht.Name
        Shtnum = Shtnum + 1
    Next Sht
    With ListBox1
        .ColumnWidths = "100 pt;30 pt"
        .List = data
        .ListIndex = ListPos
    End With
End Sub

To use the selection ;

Private Sub OKButton_Click()
    Dim UserSheet As Object
    Set UserSheet = Sheets(ListBox1.Value)
     Unload Me
End Sub

The above codes need to reside in the User form code section. A seperate code will trigger the entry into the code and that'll need to reside in a seperate module.

If you need more code, let me know.
BarbMplsAuthor Commented:
Thanks, everyone, for your suggestions.  I'll try them out in the next hour or two and post my successes or challenges with them.
BarbMplsAuthor Commented:
My apologies to all who suggested solutions; my desk blew up on Friday, and I've spend a couple of days trying to put out fires.  I will try your solutions and report on Sunday or Monday AM at the latest.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.