If combo box contains text like multiple criteria then disable text boxes below-CodePudding

I'd like to disable text boxes if the SponName combo box contains certain company names. I thought I could try a for loop for this, but I'm not sure if I created the right variables or used the for loop properly. Please help!

I've tried cobbling together the following code from the threads below but can't quite adapt it to my problem. I'm still learning VBA and am not great with declaring variables. There's no error message, but the text fields just won't disable after updating SponName. VBA code for if like multiple criteria

Private Sub SponName_AfterUpdate()
    Dim sponcontains As Variant
    sponcontains = SponName.Text
    Criteria = Array("Company1*", "Company2*", "Company3*", "Company24*")
    For i = LBound(Criteria) To UBound(Criteria)
        If InStr(sponcontains, Criteria(i)) > 0 Then
            cps_manufsite_name.Enabled = False
            cps_manufsite_city.Enabled = False
            cps_manufsite_st.Enabled = False
            cps_manufsite_ctry.Enabled = False
        Else
            cps_manufsite_name.Enabled = True
            cps_manufsite_city.Enabled = True
            cps_manufsite_st.Enabled = True
            cps_manufsite_ctry.Enabled = True
        End If
    Next i
End Sub

CodePudding user response：

Use Text property only when control still has focus. You need Value property which is default for data controls so don't have to reference. Explicitly declare all variables. Should have Option Explicit at top of all modules. Set the VBA editor > Tools > Options > Require Variable Declaration to automatically add to new modules - will have to type into existing. Use of * wildcard not appropriate with InStr() function - it's just another literal character.

Like this - default value for Boolean variable is False:

Option Compare Database
Option Explicit
___________________________________________________________________________________

Private Sub SponName_AfterUpdate()
    Dim sponcontains As Variant, criteria As Variant, booE As Boolean, i As Integer
    sponcontains = Me.SponName
    criteria = Array("Company1", "Company2", "Company3", "Company24")
    For i = LBound(criteria) To UBound(criteria)
        If InStr(sponcontains, criteria(i)) > 0 Then
            booE = True
            Exit For
        End If
    Next
    cps_manufsite_name.Enabled = Not booE
    cps_manufsite_city.Enabled = Not booE
    cps_manufsite_st.Enabled = Not booE
    cps_manufsite_ctry.Enabled = Not booE
End Sub

But your solution without loop is just as valid and most likely shorter. Again, use Boolean variable to set Enabled state instead of duplicating.

booE = InStr("Company1,Company2,Company3,Company24", Me.SponName) > 0

Consider what would happen if you had to modify this list. Would have to modify code and distribute new db version to users. Alternatively, use a table with an attribute (can be a yes/no field) for company status. Code can do a lookup to table. Better to use company ID value to search. Your combobox should have that as a column in its RowSource and usually that would be the combobox's value.

booE = DLookup("Status", "Companies", "CompanyID = " & Me.SponName)

Use of Conditional Formatting can often avoid VBA altogether.