Code to set specific Cells to be used in a combo box for a search criteria

[larrysdime]

Hello All,
I have an application where on the user form a combo box exist and allows a user to select which field they would like to search by. The combo Box is populate with a list of choices using the following code:

Private Sub UserForm_Initialize()

Me.cboxSearchBy.List = WorksheetFunction.Transpose(Sheet7.Range("B1:O1"))

End Sub

Using this produces 15 column Headings that can be used as the search Criteria when really only three logical Search criteria exist. That said, I am trying to alter the above code to only allow the selection of Cells B1, C1 an O1 only. I have searched and not been able to modify the code to produce this result

Can anyone provide me guidance on How to modify the code above to only use cells B1, C1 & O1 and not the entire range of B1:O1?

Thanks for any help,
Larry

[JieJenn]

Can you post your work file?

[larrysdime]

Yes, I am attaching it to this thread.

Basically, take a Look at the user form call FindPart. There is a combobox that is populated with 15 or so search criteria choices that are a result of the code I illustrated in my previous thread.

I am trying to Limit the Search Criteria to one of three criteria Located on Worksheet7. Currently it is using the Range of B1:O1. I need it to only allow one of
three choices which are B1, C1 or O1, Which are the following, Part Number (B1), Part Description (C1) or Key Search Word (O1).

I've been looking around and not had any success finding the right answer and am stuck trying to figure it out with my very limited code skills.

Here is the file: Perfect Degree Service Ticket Test.xlsm

Thanks for responding,
Larry

[JieJenn]

I don't see worksheet 7 from your attachment.

[JieJenn]

Do you mean tab: Part Table?

[JieJenn]

Ok never mind. Change your code to

Please Login or Register  to view this content.

[larrysdime]

Sorry about that. Just slip away for dinner. I just inserted the code and it worked like a charm.

Thank you so much for your help! I am very new to code and very weak at it. This would have taken me forever to figure out.

Your help was greatly appreciated. Do you mind if I call on you again if I get hung up? I still have a ways to go.

Thanks,
Larry

[JieJenn]

Feel free to send me PM in my inbox if you have question.

[larrysdime]

I am stuck again and am reaching out for some help. Basically when I Select the Search Criteria in the combo box and enter the Item in the Search For Box and execute the Search Command Button I get the message Box that says the item can not be found.

On Sheet 7 The Search Criteria established on Cells AH2 & AH 3 all seem to be working fine. My guess is that what I am entering is working fine and the execution of the Search for Part is working ok. Or at least I believe it is. My thinking is that maybe it is not coming back correctly to the output data and causing the "Can Not Find Part Error".

The problem is in my code for the Search Command Button on the Find Part Form. Here is my Code and I will attached the latest version of the project.

Once the Search is executed the List Box on the Find Part Data should be drilling into that part number. I'm not looking for an exact match. My thinking is if they enter a partial part number (not case Sensitive) the list box will narrow down to their search and they can then scroll to the exact part and select it. Once that is done I will then start doing what I Believe will be vlookup functions to populated the rest of the form with the part details selected in the List Box.

The problem may lie in the multiple work sheets. The example I was using was something using one work sheet. My project is utilizing multiple worksheets and I may be running into trouble Identifying the Sheets to use and the sheets to output to properly.

I hope its not asking too much to have you take a look at the attached and tell me where I am going wrong in my code.

Hope Your Day is Great,

Larry Perfect Degree Service Ticket Test.xlsm