Combo box not returning chosen listindex value

Question

I'm creating a workbook, which tracks available rentals per month. It is divided into 12 sheets, one for each month. The first three columns of each sheet track the type of accommodation, number of bedrooms and what's included in the rental price. The concept there is that there will be a drop-down combo box that allows the user to fill in with a point-and-click option rather than typing things out in order to reduce input errors.

I set up a fixed array, the contents in which changes depending on what column that active cell is in, and then the array is assigned to the combo box. The code lives in the Sheet1 Module under the combo box code and the ThisWorkbook module calls it under SheetSelectionChange, so as to avoid repeating the code in each sheet.

A Standard Module makes the array public

All 12 combo boxes share the same name, cboOptions, and they populate correctly, regardless of what sheet is chosen. My problem is that none of the combo boxes return the listindex value of the choice that's made, regardless of the code telling it to do so. I've been testing to see the value of the position returned against the value of the position chosen, but I have not been able to establish a pattern. I thought about clearing the variables and arrays, thinking that might be what's messing with the code, but it seems to be having no effect. I've read what I could on the issue, but I'm out of ideas on what might be the problem...thank you in advance!

Code in Sheet1 module:

    Private Sub cboOptions_Change()

Erase myarray()
cboOptions.Visible = True
cboOptions.Enabled = True
cboOptions.Clear

n = ActiveCell.Row
 If n >= 3 And n < 10000 Then

    If ActiveSheet.Range(ActiveCell.Address).Address = Range("A" & n).Address Then

        myarray(1) = "Apartment"
        myarray(2) = "Room"
        myarray(3) = "Townhouse"
        myarray(4) = "House"


    ElseIf ActiveSheet.Range(ActiveCell.Address).Address = Range("B" & n).Address Then

        myarray(1) = "1"
        myarray(2) = "2"
        myarray(3) = "3"
        myarray(4) = "4"
        myarray(5) = "5"


    ElseIf ActiveSheet.Range(ActiveCell.Address).Address = Range("C" & n).Address Then

        myarray(1) = "Heat & Water"
        myarray(2) = "All-inclusive"

    Else

        cboOptions.Enabled = False
        cboOptions.Visible = False

    End If

  End If
'ActiveSheet.cboOptions.ListIndex = 0


'Dim x As Long
'MsgBox ActiveSheet.Name

With ActiveSheet
.cboOptions.Left = .Range(ActiveCell.Address).Left
.cboOptions.Top = .Range(ActiveCell.Address).Top

.cboOptions.List = myarray()


With .cboOptions
'the problem is that x needs to get assigned a value from the combo box before it continues to execute

    x = .List(.ListIndex)
    'MsgBox x
    End With
    .Range(ActiveCell.Address) = x 'myarray(x)
.Columns(ActiveCell.Column).ColumnWidth = cboOptions.Width * 0.18
x = 0

Erase myarray()

End With

End Sub

Code in ThisWorkbook:

Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)

Application.Run "Sheet1.cboOptions_Change"

End Sub

Code in Module1:

Option Explicit
Public myarray(0 To 5) As String

Because that's where it needs to be for the combo box to populate. The original combo box sits in Sheet1 and is the reference point for everything else. I suppose I can create it inside a standard module, alternatively... — quadrature
So, are the conditions corrects? Because, if they aren't, you will have nothing in the combobox. — D. O.
They are working, yes. Depending on if the active cell is in Col A, B or C, the selection changes for any activated sheet. — quadrature

quadrature quadrature · Accepted Answer · 2017-01-26T17:41:38

The nature of the problem seems to be that using more than one array for one combo box breaks down how the listindex values are calculated. I broke down the code to its component features to see if the issue persisted

1) Made a new file and put the code in Sheet1 2) Made separate fixed arrays for each kind of input 3) Created a separate routine for each kind of input

Using ON ERROR RESUME NEXT at the beginning of each routine overlooks the error and the code works properly. Alternatively, putting in a break where the integer variable is given the listindex value of the combo box allows the user to make a choice and assign a value to the integer variable, before continuing. Otherwise, its default value is -1 and returns an error; using .list(.listindex) did not make any difference, suggesting that the code needs to wait for user input (using a combobox event other than Change?).

May just need to establish a separate combo box for each column. Anyway, the code below is the sticks-and-stones version of the above, for a single sheet, and it will do the job if applied to each sheet module in the workbook:

Sub monthnames()
'add month names to the first cell of each sheet

Dim n As Integer
'Sheets(1).Activate
For n = 1 To 12
    Sheets.Add After:=ActiveSheet
    ThisWorkbook.Sheets(n).Cells(1, 1) = MonthName(n)
    Next
End Sub

Private Sub cboOptions_Change()

Dim myarray(1 To 4) As String
Dim myarray2(1 To 5) As String
Dim myarray3(1 To 2) As String

cboOptions.Enabled = True
cboOptions.Visible = True

Dim n As Integer

n = ActiveCell.Row
 If n >= 3 And n < 10000 Then

    If Range(ActiveCell.Address).Address = Range("A" & n).Address Then
        myarray(1) = "Apartment"
        myarray(2) = "Room"
        myarray(3) = "Townhouse"
        myarray(4) = "House"
        cboOptions.List = myarray()
        inputdata myarray(), n

    ElseIf Range(ActiveCell.Address).Address = Range("B" & n).Address Then

        myarray2(1) = "1"
        myarray2(2) = "2"
        myarray2(3) = "3"
        myarray2(4) = "4"
        myarray2(5) = "5"
        cboOptions.List = myarray2()
        inputdata2 myarray2(), n

   ElseIf Range(ActiveCell.Address).Address = Range("C" & n).Address Then

        myarray3(1) = "Heat & Water"
        myarray3(2) = "All-inclusive"
        cboOptions.List = myarray3()
        inputdata3 myarray3(), n
    Else

        cboOptions.Enabled = False
        cboOptions.Visible = False

    End If

  End If


End Sub


Sub inputdata(myarray, n) 'myarray3, )

On Error Resume Next
Dim x As Integer

    cboOptions.Left = Range(ActiveCell.Address).Left
    cboOptions.Top = Range(ActiveCell.Address).Top
    Columns(ActiveCell.Column).ColumnWidth = cboOptions.Width * 0.18

    If Range(ActiveCell.Address).Address = Range("A" & n).Address Then

    x = cboOptions.ListIndex + 1

    Range(ActiveCell.Address) = myarray(x)

Else

    Exit Sub

End If

End Sub

Sub inputdata2(myarray2, n)
On Error Resume Next
Dim y As Integer

    cboOptions.Left = Range(ActiveCell.Address).Left
    cboOptions.Top = Range(ActiveCell.Address).Top
    Columns(ActiveCell.Column).ColumnWidth = cboOptions.Width * 0.18



    If Range(ActiveCell.Address).Address = Range("B" & n).Address Then

y = cboOptions.ListIndex + 1

    Range(ActiveCell.Address) = myarray2(y)

Else

    Exit Sub

End If

End Sub

Sub inputdata3(myarray3, n)
On Error Resume Next
Dim z As Integer

    cboOptions.Left = Range(ActiveCell.Address).Left
    cboOptions.Top = Range(ActiveCell.Address).Top
    Columns(ActiveCell.Column).ColumnWidth = cboOptions.Width * 0.18

If Range(ActiveCell.Address).Address = Range("C" & n).Address Then

z = cboOptions.ListIndex + 1

    Range(ActiveCell.Address) = myarray3(z)

Else

    Exit Sub

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call cboOptions_Change
End Sub

Combo box not returning chosen listindex value

1 Answers