Change label if string contains a ComboBox Value

ivanian · Apr 16th 2016

So I have a label that will depend on the value of ComboBox. If the Value of ComboBox contains a specific string then it'll execute a Match Index from the range that I specified.
I managed to do what I wanted but the problem now is I want to clear the label if the ComboBox does not contain the string that I'm looking for. I tried to add an 'ElseIf' then put the caption to blank but after doing so, if I changed that value of ComboBox it will stay as blank.

Code

Public Sub SetBonus()
    Dim mySet As Range, twoParts As Range, fourParts As Range, sevenParts As Range, cnt As Range
    Set mySet = ThisWorkbook.Names("setBns").RefersToRange
    Set twoParts = ThisWorkbook.Names("twoParts").RefersToRange
    Set fourParts = ThisWorkbook.Names("fourParts").RefersToRange
    Set sevenParts = ThisWorkbook.Names("sevenParts").RefersToRange
                
    For Each cnt In mySet
        If InStr(cbxItem.Value, cnt) <> 0 Then
        With WorksheetFunction
            Label1.Caption = .Index(twoParts, .Match(cnt, mySet, 0))
            Label2.Caption = .Index(fourParts, .Match(cnt, mySet, 0))
            Label3.Caption = .Index(sevenParts, .Match(cnt, mySet, 0))
            End With
        End If
    Next cnt
End Sub

Display More

Anyone please?

Thanks in advance,

skywriter · Apr 17th 2016

Re: Change label if string contains a ComboBox Value

It would be lot easier to understand and help you with the workbook.

When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

ivanian · Apr 17th 2016

Re: Change label if string contains a ComboBox Value

Hello,

Thanks and worksheet attached.

skywriter · Apr 17th 2016

Re: Change label if string contains a ComboBox Value

Let me know if this is what you are looking for.

Code

Public Sub SetBonus()
    Dim mySet As Range, twoParts As Range, fourParts As Range, sevenParts As Range, cnt As Range
    Set mySet = ThisWorkbook.Names("setBns").RefersToRange
    Set twoParts = ThisWorkbook.Names("twoParts").RefersToRange
    Set fourParts = ThisWorkbook.Names("fourParts").RefersToRange
    Set sevenParts = ThisWorkbook.Names("sevenParts").RefersToRange
                
    For Each cnt In mySet
        Select Case InStr(cbxItem.Value, cnt)
        Case 0
            Label1.Caption = ""
            Label2.Caption = ""
            Label3.Caption = ""
        Case 1
        With WorksheetFunction
            Label1.Caption = .Index(twoParts, .Match(cnt, mySet, 0))
            Label2.Caption = .Index(fourParts, .Match(cnt, mySet, 0))
            Label3.Caption = .Index(sevenParts, .Match(cnt, mySet, 0))
            Exit For
            End With
        End Select
    Next cnt
End Sub

Display More

ivanian · Apr 17th 2016

Re: Change label if string contains a ComboBox Value

Hello Skywriter, you really are a life saver. That's exactly what I needed. Thank you so much.

I could've sworn that I tried that too but for some reason the Captions stays blank.

I did a little modification because I realized that I'll be adding more columns in the future and decided to store it in multidimensional array, now I'm back where I started. Could you please take a look which part am I missing? Thanks in advance

Code

Public Sub SetBonus()
    Dim myset As Range,r%, cnt As Range, x%
    
myTbl = Worksheets("DB").ListObjects("tbl_setBonus").DataBodyRange
    Set myset = ThisWorkbook.Names("setBns").RefersToRange


    For Each cnt In myset
        
        Select Case InStr(cbxItem.Value, cnt)
        Case 0
            Label1.Caption = ""
            Label2.Caption = ""
            Label3.Caption = ""
        Case 1
            For r = LBound(myTbl) To UBound(myTbl)
            If myTbl(r, 1) = cnt Then Exit For
            Next r
                Label1.Caption = myTbl(r, 2)
                Label2.Caption = myTbl(r, 4)
                Label3.Caption = myTbl(r, 6)
        End Select
    Next cnt
End Sub

Display More

Change label if string contains a ComboBox Value

Participate now!

Share