Option buttons to use for searching

**Qppg** · 01-13-2010, 03:03 PM

Okay,
I know that there is an ocean of information out there with regards to option buttons, but for the life of me I am unable to find what it is I am looking for. So I will try to explain what I am trying to do as best as possible. In my workbook I have 4 sheets. Sheet 1 contains all the data. The remaining sheets have taken all the data and broken it down by machine type. IE(carton errector, Carrier errector, etc) I have a series of user forms that you would navigate through to perform a search. For This example frmMain_menu is where the user would start out. I only have code added for the first button (Pearson). This will bring up another form with more options for the user to select. Currently I only have code for the Carton Errector button. Once the user has selected that button frmCarton_Errector pops up and it has a series of 12 option buttons to choose from. What I am trying to accomplish is say the user selects cylinders, I would like once the search button is clicked for the program to is search sheet2 col f for the caption value of the option button selected. Any hits that it might find I would like to display certain datafrom the rows in a list box.(IE col A,B,G)
I apologize upfront for the length of the post....but I want to provide as much detail as possible. I am also attaching a copy of my workbook for refrence. Thanks in advance for your help. You all have helped me out considerably in the past.

Bob

**Leith Ross** · 01-13-2010, 05:35 PM

Hello Bob,

This wasn't a small order. The attached workbook does what you want now. Once the search is done, the results are stored in an array. This array called "ListBoxData" is actually a Public Variant declared in Module1. This allows other UserForms to freely access search results.

Each OptionButton calls a procedure that stores it caption in the variable "OptBtn" on the UserForm. The search routine uses this to search for matches in column "F". If no matches are found then a message is displayed. I tried to keep this as simple as possible. However, I am sure you will have additional questions about the code. Just ask and I'll answer them.

frmCarton_Erector Code

Please Login or Register  to view this content.

frmSearch_Results

Please Login or Register  to view this content.

**Qppg** · 01-13-2010, 06:19 PM

Leith,
That is fantastic. So far I am following your logic in the code. I want to try a few things before I mark this solved to see if it pops any more questions. Thank you so much for your time and efforts on this.

Bob