ComboBox Value in VBA

[bruce21]

Hello all,

I have the following code below, I want to be able to set some kind of autofilter on the drop down box to show only the given part number of "217230*".

I tried including it in the Criteria1: field but did not work. I only want to see the part numbers "217230*" when you click on the drop down.

Private Sub ComboBox1_Change()

Sheets("RITE AID").Range("p1:z2000").ClearContents
uf1.Caption = Me.ComboBox1.Text
Application.ScreenUpdating = False
Sheets("BOM").ListObjects("Table_BoM_Report___mi").Range.Copy
Sheets("TRANS").Range("A1").PasteSpecial xlPasteValues
Set src = Sheets("TRANS").Range("A1").CurrentRegion
Set ws = Sheets("TRANS")
ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=src, xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium6").Name = "Table2"
Set rtf = Sheets("TRANS").ListObjects("Table2").Range
rtf.AutoFilter field:=1, Criteria1:=Me.ComboBox1.Value
For Each Row In Range("Table2[#All]").Rows
If Row.EntireRow.Hidden = False Then
If rng Is Nothing Then Set rng = Row
Set rng = Union(Row, rng)
End If
Next Row
Set ws = Sheets("RITE AID")
rng.Copy Destination:=ws.Range("P1")
Set src = ws.Range("P1").CurrentRegion
ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=src, xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium6").Name = "Table3"
Me.ListBox1.Clear
With Me.ListBox1
.List = Sheets("RITE AID").ListObjects("Table3").DataBodyRange.Value
.ColumnCount = 6
.ColumnWidths = "0,0,0,70,150,20"
End With
Sheets("RITE AID").Range("P1:z2000").ClearContents
Sheets("RITE AID").Range("p1:z2000").Clear
Sheets("RITE AID").Range("P1:Z2000").Interior.ColorIndex = 2
Application.ScreenUpdating = True

End Sub

Thank you to all who respond,
BLD

 

[Fluff]

Cross posted https://www.excelforum.com/excel-programming-vba-macros/1294707-combobox-value.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[bruce21]

Fluff,

Why do you assume I am keeping this a secret to cross posting?

I was unaware it is not acceptable to post to other sites asking for help to solve a problem. I am a new user as you probably know and did not realize this was a violation of your forum.

BLD

 

[Fluff]

I never said you were keeping it secret, I simply pointed the rule on the assumption that you were not aware of it.
It also enables any potential helper to check what, if any, help you have already received.