Trouble configuring 2nd. column of a ComboBox using a variable

[hermannjt]

I began Self-Learning VBA toward the end of February this year and I am still trying to figure out the RULES of VBA.
The application I am working on relies heavily on pairs of ComboBoxes for selecting Annual Worksheets for multiple Property Locations, each having one or more Annual Worksheets based on their Propery ID and the Worksheet Year.

The image below should provide some clarity for want I wish to accomplish. The important Ranges are in columns "A", "L", and "M".

The following is the original code I am trying to modify.

    Private Sub cnfgCmbPropID()  [COLOR=rgb(97, 189, 109)]'configure cmbPropID[/COLOR]
    Dim rw As Long
        Me.cmbPropID.Clear
        For rw = 1 To propIDs.Count
          If propIDs.Cells(rw, 1).Value2 <> vbNullString Then  [COLOR=rgb(97, 189, 109)]'Exclude Blank Rows[/COLOR]
            If actStatus.Cells(rw, 1).Value2 = True Then              [COLOR=rgb(97, 189, 109)]'Only include Properties that are ACTIVE[/COLOR]
                Me.cmbPropID.AddItem propIDs.Cells(rw, 1).Value2
            End If
          End If
        Next
    End Sub

Private Sub cmbPropID_Change() [COLOR=rgb(97, 189, 109)]'This subroutine configures cmbYears[/COLOR]
   Dim i
   Dim rw
   rw = cmbPropID.ListIndex + 1  [COLOR=rgb(97, 189, 109)]'This line has the potential to throw a "Script Out of Range" error  [/COLOR]
   MsgBox "rw = " & rw

   frmElect.Visible = False
   frmGas.Visible = False
   wsStartYr = StartYr.Cells(rw, 1).Value2
   MsgBox "rw = " & rw & vbCrLf & _
                "wsStartYr = " & wsStartYr
   wsCntrl.Activate
      With cmbYears
           .Clear
        If cmbPropID.ListIndex >= 0 Then
          For i = wsStartYr To wbCurYear
              If wbCurYear <> wsStartYr Then
                  .AddItem i
              ElseIf wbCurYear = wsStartYr Then
                  .AddItem wbCurYear
              End If
          Next i
        End If
      End With
   lstDsplyUtil1.RowSource = ""
   End Sub

As one may see from the Code above, the value of rw is determined by the cmbPropID.ListIndex value and I was able to get around the potential "Script Out of Range" error by automatically sorting the entire table so that the Properties with an Active Status were at the top of the range (which chewed up a significant amount of CPU time thereby slowing the processes).

What I am trying to do is add an additional column to the cmbPropID comboBox so that Column 1, the Bound Column contains the Row Number and Column 2 contains the PropertyID. I have adjusted the combobox's properties to reflect that change.

This is the modified code I am trying to implement which :

Sub cnfgPropIDsDDL()

MsgBox "Call made to cnfgPropIDsDDL"
    Me.cmbPropID.Clear
    For rw = 1 To propIDs.Count
      If propIDs.Cells(rw, 1).Value2 <> vbNullString Then
        If actStatus.Cells(rw, 1).Value2 = True Then
          propRW = rw
          MsgBox "propRW = " & propRW & vbCrLf & _
                 "propID = " & propIDs.Cells(rw, 1).Value2
            Me.cmbPropID.AddItem [COLOR=rgb(184, 49, 47)]propRW[/COLOR], propIDs.Cells(rw, 1).Value2 [COLOR=rgb(184, 49, 47)]'this line throws "Type MisMatch" error regardless of how I declare propRW as a variable[/COLOR]
        End If
      End If
    Next
End Sub

What am I doing wrong?
Thanks in advance for the help.
John

 

[hermannjt]

For those interested, I found a solution that works well for the application I have been working on. After testing on the original ComboBox, I created a subroutine that the application could call to configure the other ComboBoxes in the application that require the same configuration.

Option Explicit
'The ranges below exist a opposite ends for the same table as shown in the screenshot above in the original post
Set actStatus = ThisWorkbook.Names("actStatus").RefersToRange 'Workbook Scoped Range for the property location's Status
Set propIDs = ThisWorkbook.Names("propIDs").RefersToRange     'Workbook Scoped Range for the property ID

Sub cnfPropDLL(ByRef dllName As Object)
Dim i As Long        'i increments the DDL Row
i = 0
MsgBox "Call made to cnfPropDDL"
    Me.cmbPropID.Clear
    For rw = 1 To propIDs.Count
      If propIDs.Cells(rw, 1).Value2 <> vbNullString Then 'Ignores Blank Cells within the propIDs Range
        If actStatus.Cells(rw, 1).Value2 = True Then     'Only accepts  rows with actStatus = TRUE
          MsgBox "propROW = " & rw & vbCrLf & _
                 "propID = " & propIDs.Cells(rw, 1).Value2
            dllName.AddItem rw
            dllName.Column(1, i) = propIDs.Cells(rw, 1).Value2
            i = i + 1     'Increment the row count
        End If
      End If
    Next
End Sub

I hope this will help others who are struggling with similar issues in configuring Multi-Column DDLs