Handling many comboboxes and textboxes

I've designed a userform in Excel and it has many comboboxes and textbox inputs, once the user is done it puts ticks in appropriate columns depending on the ComboBox selection and also writes the value of the TextBox to the sheet.

At the moment I feel as though the sub could be refined but am unsure how to proceed as the ComboBoxes are not regular in the number of options each have.

I was thinking of using .listindex in place of the offset number. Any thoughts on proceeding with that?

```
Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Projects.EnableMisc = False And Cells(ActiveCell.Row, 1).Value = "New" Then 'sets the save data for misc if it's not turned, so that if it is enabled it has some settings and the checkboxes are not set to triple state.
Cells(ActiveCell.Row, Range("SaveMisc1").Column).Value = 0
Cells(ActiveCell.Row, Range("SaveMisc2").Column).Value = 0
Cells(ActiveCell.Row, Range("SaveXMisc1").Column).Value = False
Cells(ActiveCell.Row, Range("SaveXMisc2").Column).Value = False
End If

Dim RangeName As String
Dim dblTBox As Double

Range(ActiveCell, Cells(ActiveCell.Row, Range("CostBuilding").Column)).ClearContents 'clears contents incase changes are made, this prevents multiple ticks showing in a single section

'writes the project name into the first column
Cells(ActiveCell.Row, 1).Value = Me.TextBoxName

'writes the ticks to the sheet
RangeName = "ProjFeasibility"
dblTBox = Me.TextBoxFeasibility.Value
Cells(ActiveCell.Row, Range(RangeName).Column).Offset(0, 2).Value = dblTBox

Select Case Me.ComboBoxFeasibility.Value
Case "Yes"
Cells(ActiveCell.Row, Range(RangeName).Column).Value = "ü"
Case Else
End Select

RangeName = "ProjConcept"
dblTBox = Me.TextBoxConcept.Value
Cells(ActiveCell.Row, Range(RangeName).Column).Offset(0, 4).Value = dblTBox

Select Case Me.ComboBoxConcept.Value
Case "Review"
Cells(ActiveCell.Row, Range(Ra

It looks like using ListIndex could cut down your code considerably, however this may reduce the readability of your code, and you may need to reorder the options in the ComboBox or where the data is saved.

Select Case Me.ComboBoxOffset.Value
    Case "Large"
        Cells(ActiveCell.Row, Range(RangeName).Column).Value = "ü"
    Case "Medium"
        Cells(ActiveCell.Row, Range(RangeName).Column).Offset(0, 1).Value = "ü"
    Case "Small"
        Cells(ActiveCell.Row, Range(RangeName).Column).Offset(0, 2).Value = "ü"
    Case Else
End Select

By using ListIndex you could cut this function down to:

Cells(ActiveCell.Row, Range(RangeName).Column).Offset(0, ComboBoxOffset.ListIndex).Value = "ü"

ListIndex returns the index number of the option selected, in this case:

0 - Large

1 - Medium

2 - Small

(This assumes that the options are ordered in this way in your ComboBox)

Note how the index begins at 0 like Arrays, this is useful in your case as your first option "Large" requires no Offset. By using ListIndex within Offset the option "Large" is effectively Offset(0, 0), in other words no offset is made, and so you don't need to write an If statement to handle the first option exclusively.

Select Case Me.ComboBoxConcept.Value
    Case "Review"
        Cells(ActiveCell.Row, Range(RangeName).Column).Value = "ü"
    Case "Large"
        Cells(ActiveCell.Row, Range(RangeName).Column).Offset(0, 1).Value = "ü"
    Case "Medium"
        Cells(ActiveCell.Row, Range(RangeName).Column).Offset(0, 2).Value = "ü"
    Case "Small"
        Cells(ActiveCell.Row, Range(RangeName).Column).Offset(0, 3).Value = "ü"
    Case Else
End Select

So how could we use ListIndex to reduce this one? Well...

Cells(ActiveCell.Row, Range(RangeName).Column).Offset(0, ComboBoxConcept.ListIndex).Value = "ü"

It's the same one line of code, only the name of the ComboBox has been changed, pretty simple stuff.

Potential issue

Something to note with ListIndex is that if no option has been selected in the ComboBox, ListIndex will return a value of -1. This is an issue in your case as the offset will be (0, -1), meaning it will write in a column it shouldn't.

The easiest way to get around this is to check that an option has been selected before changing any cell values:

If ComboBoxOffset.ListIndex <> -1 then
    Cells(ActiveCell.Row, Range(RangeName).Column).Offset(0, ComboBoxOffset.ListIndex).Value = "ü"
End If

StackExchange Code Review Q#148289, answer score: 2