EXCEL VBA: Combo box to list active workbook sheets

Posted on 2003-03-28
Medium Priority
Last Modified: 2007-12-19
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.
Question by:BarbMpls
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 44

Expert Comment

ID: 8225266
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


Author Comment

ID: 8225568

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?


Accepted Solution

MikeC03 earned 500 total points
ID: 8226058
  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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 44

Assisted Solution

bruintje earned 500 total points
ID: 8226095
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

LVL 11

Expert Comment

ID: 8226252
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.


Expert Comment

ID: 8226285
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.

Author Comment

ID: 8226871
Thanks, everyone, for your suggestions.  I'll try them out in the next hour or two and post my successes or challenges with them.

Author Comment

ID: 8232789
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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question