Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2213
  • Last Modified:

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.
0
BarbMpls
Asked:
BarbMpls
2 Solutions
 
bruintjeCommented:
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
0
 
BarbMplsAuthor Commented:
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
0
 
MikeC03Commented:
Barbara,
  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()
  Sheet1.Activate
  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"
      Sheet1.Activate
    Case "Sheet2"
      Sheet2.Activate
    Case "Sheet3"
      Sheet3.Activate
  End Select
End Sub

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

Regards,
Mike
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
bruintjeCommented:
just going forward on that code from Mike

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

.......
0
 
WATYFCommented:
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
0
 
tesserhexCommented:
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.
0
 
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.
0
 
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now