User Choice and loops vba

0
votes

I'm trying to establish the logic for creating a navigation menu for a budget tracking system: it has 12 sheets for each budget line with 12 monthly tables per sheet.

The navigation menu is based on two combo boxes, one listing the sheets, and the other the names of the months - when a user selects where to go, the sheet and first cell in the chosen table activate.

What I'm looking for is a more effective way to organize this than writing 144 distinct if-then conditions accounting for every possible listindex combination the user might choose. The Select Case approach also works, but it is equally voluminous in scope...

I have been investigating using loops for the purpose - e.g. ListIndex values can be defined in a loop, but I'm coming up short on ideas for the overarching concept.

Thank you in advance!

3
vbaexcel
Could you make the list items the same as the sheet names, then no matter what is picked it can go right to it? Then just loop through a range of cells until it finds the month, that is if there is a cell with the month name. If you kept the sheet names relevant to the list items, you could even loop through the sheet names to generate the list items, that way sheets could be added and removed without affecting the code. - Michael Russo

3 Answers

1
votes

Doing the sheet selection is pretty straightforward. Just create an array that will hold the sheet name that corresponds to the ListIndex. Something like this

Dim myArray(11) As String
myArray(0) = "a"
myArray(1) = "b"
myArray(2) = "c"
...
myArray(10) = "k"
myArray(11) = "l"

Worksheets(myArray(ComboBox1.ListIndex)).Activate

If the person selects the 5th ComboBox element, sheet "e" would be activated.

Selecting the table cell is a bit more problematic since it depends on where on the sheet the tables are located. If they are spaced equidistantly apart, you can use a simple math formula. That is, if the January table starts at E7, Feb at E27, Mar at e47, then it is a simple matter of using the listindex to calculate the starting row. Eg:

Worksheets(myArray(ComboBox1.ListIndex)).Cells(7 + ComboBox2.ListIndex * 20, "E").Select

Hope this helps. :)

1
votes

Here I set up a workbook with 12 worksheets one for each month. Each worksheet has 12 tables on it. When the user selects a worksheet from the dropdown (cboWorkSheets) the second drop down (cboTables) list is cleared and then all the table names from the selected worksheet is added to back to the list.

When a user selects a table name from cboTables the worksheet referenced by cboWorkSheets is searched for that table. The first cell in the table's databody range is then selected.

enter image description here

Option Explicit

Private Sub cboTables_Change()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Set ws = Worksheets(cboWorkSheets.Value)
    Set tbl = ws.ListObjects(cboTables.Value)
    ws.Activate
    tbl.DataBodyRange.Cells(1, 1).Select
End Sub

Private Sub cboWorkSheets_Change()
    Dim ws As Worksheet
    Dim tbl As ListObject

    Set ws = Worksheets(cboWorkSheets.Value)
    cboTables.Clear
    For Each tbl In ws.ListObjects
        cboTables.AddItem tbl.Name
    Next

End Sub

Private Sub UserForm_Initialize()
    cboWorkSheets.List = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10", "Sheet11", "Sheet12")
End Sub
0
votes

As general interest, this is the functional version of the code for a proof of concept file I built around @Tim's example, given above. Here goes:

In Module1:

Sub ComboBox1_Change()

Dim sheets_array(0 To 2) As Variant

sheets_array(0) = "Sheet1"
sheets_array(1) = "Sheet2"
sheets_array(2) = "Sheet3"

With UserForm1.ComboBox1
    .Clear
    .List = sheets_array
    .Style = fmStyleDropDownCombo

    End With

    Call ComboBox2_Change

    UserForm1.Show
End Sub

Sub ComboBox2_Change()

Dim monthsarray(0 To 3) As Variant

monthsarray(0) = "April"
monthsarray(1) = "May"
monthsarray(2) = "June"

With UserForm1.ComboBox2
    .Clear
    .List = monthsarray
    .Style = fmStyleDropDownCombo
    End With

End Sub

In the UserForm1 code window:

Private Sub ComboBox1_Change()

With UserForm1.ComboBox1
        Worksheets(.List(.ListIndex)).Activate
    End With
End Sub


Private Sub ComboBox2_Change()

With Worksheets(UserForm1.ComboBox1.ListIndex)
     .Select
     .Cells(7 + UserForm1.ComboBox2.ListIndex * 20, "E").Select
End With

End Sub

@Thomas Inzina, your solution is considerably more elegant and I hope I can think about programming at your level at some point.