Non-Standard "Conditional" configuration of ComBox1

Question

My problem is how to populate the cmbSelProp ComboBox with propIDs from a workbook scoped Named Range called PropLocs. The columns of interest Column "A" which contains the list of propIDs and Column "K" which contains the ActiveStatus of each propID. I also have Named Ranges for both columns of interest, propIDs and actStatus respectively. The actStatus, Column "K", portion of the range are set to True/False when the properties are added and subsequently initialized and annual worksheets are automatically generated.
I only want cmbRptPrpID populated with propIDs having an actStatus = True.

I have spent many hours over the past few weeks going blind doing Web Searches, looking at links, and trying suggestions from the various links without success. I,m as "Lost as a blind man in a snow storm!"

There are broader issues associated with the problem I am dealing with and understanding how to solve, and understand, the issue at hand will help me in the future development of my application.

UPDATE

the 2 mentioned additional ranges are in columns "A" and "K"

Update 2

Business Logic The application I am developing utilizes a multipage object and there are pairs of dynamic comboboxes, cmbSelProp and cmbYears for example, used to select the active worksheet and enter Monthly expenses, view/generate Reports, etc. cbmSelPropselects the property ID, and the cbmSplProp_Change() event configures cmbYears list values based on the variable wsStartYr in column "K" and wbCurYear which is the Current Calendar Year. the annual worksheets have a Worksheet TabName of propId_Year and are selected using a variable wsA = pID & "_" & wsYr. I do not want propIDs with an Inactive status to appear as a part of the cmbSelProp list values.

This is a link to My First Question here on StakOverflow which is related to this question.

I really need some help figuring this out! Thanks in advance for your assistance. here is the code I have been trying to work with.

        selectedRow = cmbSelProp.ListIndex + 3
        
        For Each cPart In wsCntrl.Range(Range("propIDs"), Range("A" & Rows.Count).End(xlUp))
         pAct = wsCntrl.Cells(selectedRow, "K").Value
          With Me.cmbSelProp
            If pAct = "True" And cPart.Value <> "" Then cmbSelProp.AddItem cPart.Value
          End With
        Next cPart

@Tim Williams, initially I tried to use the `PropLocs` range in the code but it populated the List with the values of every cell in the range. This is why I set up the "propIDs' and `actStatu` ranges that appear in the code. Sometimes it's difficult to provide COMPLETE clarity. Sorry for the confusion. — hermannjt, May 02 '22 at 18:07
What's the connection between `cmbRptPrpID` and `cmbSelProp` ? A screenshot of your data would be useful here. — Tim Williams, May 02 '22 at 18:53
Description and and `Code Snippet` updated. I'm not sure how `cmbRptPrpID` got in there. that `ComboBox` uses the same code for configuration as does several other `ComboBoxes` in the project. I'm very new to VB - began self learning almost 2 months ago. — hermannjt, May 02 '22 at 20:00
OK I'm still not following the logic sequence here - your code only reads one value from ColK, based on the selected list index and then - if the ColK value is True - loops over the table and adds all non-blank ColA values to the same list? Not sure why you'd do that. It would be helpful to explicitly describe the business logic for populating the list. — Tim Williams, May 02 '22 at 20:10
that is the crux of the problem. I have not been able to read `Columns "A" and "K"` of each row in the range,, determine if Column "K" in each row is `True` and populate the `List` ONLY with the values from `Column "A"` when `Column "K"` on that row has a value of `True". — hermannjt, May 02 '22 at 20:50

score 0 · Accepted Answer · answered May 03 '22 at 03:15

0

There are a number of issues in your code. Rather than breaking down all the errors/issues, I'll show you how I would do it.

From your previous Q, I take it that your Combo Box is on a Userform.

I've created a Userform with a combo box and a button to load it. You can change how you call the load function to suit your needs.

The UserForm code

Option Explicit

Private Sub btnTest_Click()
    Load_cmbSelProp
End Sub

Private Sub Load_cmbSelProp()
    Dim propIDs As Range
    Dim actStatus As Range
    Dim rw As Long

    Set propIDs = ThisWorkbook.Names("propIDs").RefersToRange
    Set actStatus = ThisWorkbook.Names("actStatus").RefersToRange
    Me.cmbSelProp.Clear
    For rw = 1 To propIDs.Count
        If propIDs.Cells(rw, 1).Value2 <> vbNullString Then
            If actStatus.Cells(rw, 1).Value2 = True Then
                Me.cmbSelProp.AddItem propIDs.Cells(rw, 1).Value2
            End If
        End If
    Next
End Sub

The Named ranges

The result

answered May 03 '22 at 03:15

chris neilsen

52,446
10
84
123

Chris, I only had to glance at your code to know why I was `chasing my tail` trying to solve my issue. Your answer not only solved my immediate issue, it gave me insight into how to address a dozen other potential issues during my `app` development . I do have one question about this line of code for future reference: `propIDs.Cells(rw, 1).Value2 `. What is the significance of ` .Value2 `? I've always seen this expressed as `.Value ` and during my testing using either `.value` or `.value2` return the same results. – hermannjt May 03 '22 at 08:44
@hermannjt [have a read of this](https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) – chris neilsen May 03 '22 at 11:00
your link and the link on that page provided a great deal of insight into programming structure. I'll do an experiment with one of my Annual Worksheets which has 20+ cells to update using the `Text` format . I notices that it takes several seconds to show the update. Thanks again for the assistance. – hermannjt May 03 '22 at 12:19

Non-Standard "Conditional" configuration of ComBox1

1 Answers1