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

hermannjt

New Member
Joined
Jun 20, 2022
Messages
2
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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".
wsControlRange.JPG


The following is the original code I am trying to modify.
VBA Code:
    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 :
VBA Code:
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top