How to use IF/ This (combo box value) Then That (Range Value) [Wildcards]
Category: [Excel] Demo Available
How to use IF/ This (combo box value) Then That (Range Value) [Wildcards]
Requirement:
The user has a script as follows:
Code:
Private Sub cmdContact1_Click()
Set DataSH = Sheet1
DataSH.Range("O8") = Me.cboSelect.Value
DataSH.Range("O9") = Me.txtSearch.Text
'DataSH.Range("O9") = "*" & Me.txtSearch.Text & "*"
DataSH.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"phonelist!Criteria"), CopyToRange:=Range("phonelist!Extract"), Unique:= _
False
ListBox1.RowSource = Sheet1.Range("outdata").Address(external:=True)
End Sub
Since the wildcards are only usable for text, the user is left with 3 cbo. Select values that will not search because they're numbers.
So if the combo box value:
Code:
DataSH.Range("O8")=Me.cboSelect.Value
Selected is any of these - NAME, DEPARTMENT, TITLE, UNIT, SHIFT, SUPERVISOR - the user needs to use the DataSH.Range that includes wildcards:
Code:
DataSH.Range("O9")="*"&Me.txtSearch.Text &"*"
But if the combo box value selected is any of these - EXTENSION, BUILDING, ROOM - the user need sto use the DataSH.Range without wildcards instead:
Code:
DataSH.Range("O9")=Me.txtSearch.Text
Solution:
Code:
Dim Ary As Variant
Ary = Array("NAME", "DEPARTMENT", "TITLE", "UNIT", "SHIFT", "SUPERVISOR")
If UBound(Filter(Ary, DataSH.Range("O8").Value, True, vbTextCompare)) >= 0 Then
DataSH.Range("O9") = "*" & Me.txtSearch.Text & "*"
Else
DataSH.Range("O9") = Me.txtSearch.Text
End If
Obtained from the OzGrid Help Forum.
Solution provided by la333.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to convert date format |
| How to use a code to display the current date based on certain criteria |
| How to set dynamic dates for pivot table grouping |
| How to use VBA code to obtain date from cell, then calculate 3 months later |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.