Link to home
Start Free TrialLog in
Avatar of BarbMpls
BarbMpls

asked on

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.
Avatar of Brian Mulder
Brian Mulder
Flag of Netherlands image

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

HAGD:O)Bruintje
Avatar of BarbMpls
BarbMpls

ASKER

Bruintje--

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?

Barbara
ASKER CERTIFIED SOLUTION
Avatar of MikeC03
MikeC03

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 = ""
   
    Do
        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.


WATYF
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)
    UserSheet.Activate
     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.
Thanks, everyone, for your suggestions.  I'll try them out in the next hour or two and post my successes or challenges with them.
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.