If This (combo box value) Then That (Range Value) [Wildcards]

la333

New Member
Joined
May 14, 2018
Messages
15
I have a script I'm working on, I have very little actual knowledge of VBA Script except for a college class years ago...

I have this snippet of script -

Code:
[COLOR=#101094][FONT=inherit]Private[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] cmdContact_Click[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()
[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]      Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] DataSH [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Sheet1
            DataSH[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"O8"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Me[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]cboSelect[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value
            DataSH[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"O9"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Me[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]txtSearch[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Text
            [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]'DataSH.Range("O9") = "*" & Me.txtSearch.Text & "*"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            DataSH[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"B8"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]CurrentRegion[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AdvancedFilter Action[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlFilterCopy[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] CriteriaRange[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
                [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"phonelist!Criteria"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] CopyToRange[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"phonelist!Extract"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] 
Unique[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
                [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
                ListBox1[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]RowSource [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] 
Sheet1[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"outdata"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Address[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]external[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit])

[/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR]
Since the wildcards are only usable for text, I am left with 3 cbo.Select values that will not search because they're numbers.



So if the combo box value -

Code:
[COLOR=#303336][FONT=inherit]DataSH[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"O8"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Me[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]cboSelect[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR]
selected is any of these - NAME, DEPARTMENT, TITLE, UNIT, SHIFT, SUPERVISOR - I need to use the DataSH.Range that includes wildcards -

Code:
[COLOR=#303336][FONT=inherit]DataSH[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"O9"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"*"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Me[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]txtSearch[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Text [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"*"[/FONT][/COLOR]
But if the combo box value selected is any of these - EXTENSION, BUILDING, ROOM - I need to use the DataSH.Range without wildcards instead -

Code:
[COLOR=#303336][FONT=inherit]DataSH[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"O9"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Me[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]txtSearch[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Text[/FONT][/COLOR]
Any help is greatly appreciated.<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">
</code>
 

la333

New Member
Joined
May 14, 2018
Messages
15
Hey! I'm answering my own question! I figured it out with trial and error over the course of a few hours. :)

Code:
If DataSH.Range("O8").Value = "NAME" Or DataSH.Range("O8").Value = "DEPARTMENT" Or DataSH.Range("O8").Value = "TITLE" Or DataSH.Range("O8").Value = "UNIT" Or DataSH.Range("O8").Value = "SHIFT" Or DataSH.Range("O8").Value = "SUPERVISOR" Then DataSH.Range("O9") = "*" & Me.txtSearch.Text & "*" Else DataSH.Range("O9") = Me.txtSearch.Text
Works great but if anyone wants to help me clean it up I'd appreciate it. It seems I can't get it to work unless it's all on one line?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,402
Office Version
365
Platform
Windows
How about
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
 

la333

New Member
Joined
May 14, 2018
Messages
15
How about
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
Even better! Thanks a lot!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,402
Office Version
365
Platform
Windows
You're welcome
 

Forum statistics

Threads
1,082,510
Messages
5,365,993
Members
400,865
Latest member
LuciaRomo

Some videos you may like

This Week's Hot Topics

Top