How to create a Userform with dependant comboboxes?

[Danaf]

i have created a userform and the part I am having problems is with 3 combo boxes and 1 command box. The 3 combo boxes are using a drop down list Combo Box Type = 8 different parts, Combo Box Status = 3, and Combo Box Universal =3.

comboboxtype= Pulling from the 8 different type of products 1 of these have to be selected.

Comboboxstatus= Current, Out of Date and if nothing is entered in the master spreadsheet it is Blank

comboboxuniversal= yes, no and if nothing is entered in the master spreadsheet it is blank

how do i in VBE create the code to do a search based on
type= new
Status=Current
Universal="blank" (nothing entered)

The type is a worksheet that is in the master workbook and the status and universal are fields in the worksheets. Each worksheeet is layed out identical.

This is what I have so far. I was able to get it to work by using a worksheet area and doing the drop down list based on that field

Please Login or Register  to view this content.

I am checking in the first part to make sure something is entered in the cbotype box first. The other 2 boxes I dont really need to make sure that there is anything entered. the 2nd part of the code is looking up in A2 where I orginally entered the Cbotype info before i made a userform. and then was copying to a output spreadsheet and the range was B1:C2 which was.
1 2 3

A Type Universal Status
B New Yes

Any help is greatly appreciated

[Leith Ross]

Hello Danaf,

You're problem is with the code below. The syntax is all wrong, and I can't piece together what you are trying to do. Is the ComboBox "Type" still linked to cell "A2" ? Does "A2" return a worksheet name or index number to a worksheet? Help me to understand what is happening and I can help you with code.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Danaf]

The Combo Box type is not linked to anything at this time. Orginally I had it as a user input and everything was working good. BUT I decided to create a user form and Want to delete that part of my spreadsheet so everything is handled throught the userform.

The part that you have in your response was my orginal code for the Bottom part of my message where I showed./ This was on my worksheet called "reportgenerations"

1 2 3
A Type Universal Status
B New Yes

The B1 to B3 are my list boxes that I moved to the user form.
I want to be able to do a sort now based upon my Userform info. Just cant figure out how to convert my OLD code to the new code. Especially where on the OLD code A2=New (Selected from Drop down list on reportgenerations)

1 thing to consider the Drop down in B1=Worksheets in my workbook. A person needs to select a Type or if they dont it has to pull data from all 8 of the worksheets that equal the criteria set forth in B2 and b3.

OLD B1=cbotype in userform
OLD B2=cbouniversal in userform
OLD B3=cbostatus in userform

Hope this clarifies what I am trying to do.

Eventually I will probably have 10 or so Combo boxes that I will have someone be able to select whether in combination or not. BUT if that happens a selection will have to be made in the cbotype part.

Thanks for the help

[Leith Ross]

Hello Danaf,

From your last post, this looking to be a rather complex issue. If you can post your workbook, it would be a big help. It wouild make easier and faster for me to answer your questions.

Sincerely,
Leith Ross

[Danaf]

I can not post because of too much company proprietary information.

Here is how the workbook is setup

1. 6 tabs labeled Part1, Part2, Part3, Part4, Part5, Part6 = cbotype=userform Something must be entered in here or all will be selected to do the search.
2. Under each tabs which is identical I have the following colums set up
Procedure = Description
Training date = Date done or Blank
Work Instructions = Hyperlink to work instructions or Blank
Signed off = another hyperlink or Blank
Universal = Yes, No, or Blank
Status = Current, Out of Date, or Blank
Platforms = Drop down list of 8 different combos
Comments = text or blank
3. 1 tab is reports = Userform output
4. 1 tab is reportgenerations= current search functions in here.
5. On my Userform I have the following
Platform = Drop down list = 1 for each of the Tabs in section1 (cbotype)
Status = Current, Out of Date or Blank (Drop Down List) (cboStatus)
Universal = Yes, No or Current (Drop Down list) (cbouniversal)
Continue = this is where the search has to be down with on the
command button (cmdcontinue)

I need to be able to select a "part" from the Platform drop down list. Then select an item for the Status and the Universal OR leave blank and then when I hit the continue button. I need for it to do a search 1. Based on what was selected in Platform, Then Status then Universal

ie. We select the Platform= Part5, Status=Current, Universal=blank
it then will search and copy the search results into the reports Tab. Once it is finished with the search I need for it to do a sort on Column A(Procedure)

Then I have another button (which works) which allows me to view the results.

If Nothing is selected in platform, but Status=Out of Date, Universal= Blank
I need for it to search all 6 tabs(Part1-Part6) and copy the results off all records that Status=Out of Date and Universal=Blank

Hope this clarifies it more. Wish I could post the spreadsheet to you.

[Danaf]

I think I have this figured out thanks all for the help on this. I have 1 question that I am working on.

I am using a drop down box in my userform. In the box I have Out of Date, Current, or some other information and those that have not been addressed are BLANK. I can figure out how to do the Out of Date and Current But how do i show those that are blank also?

Thanks for the help again