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

la333

New Member
Joined
May 14, 2018
Messages
27
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>
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top