Userform to query?
 

Ok, I created a userform with three comboboxes. The drop down has all of the data I need it to (from another tab), such as states, colors, etc. It's set up so when you click on state, color etc. this data is pulled into a new workbook (works pretty darn well, thanks in part to you folks).

Right now it looks like this:

Range ("H:H").Value = Combox1.Text
Range ("C:C").Value = Combox2.Text
Range ("M:M").Value = Combox2.Text

The problem is that in combobox for each of these I have an option for "ALL." So for instance, perhaps I want a state but instead of just green or purple, I want all the colors instead of having to narrow it down. However, if I leave it blank adn don't answer that question, it goes wacky. If I put "ALL" in the combo box drop down, it replaces the column with "ALL" in the cells. Is there a way to do this, or must you pick a drop down/color etc.?

Does this make sense? Thanks.

Sigh. I was wrong. Instead of pulling the data, it's changing it within the main sheet. Back to the drawing board, I think.

Ok, here is what I have for the "search" button on the user form...

Private Sub CommandButton1_Click()
State = Sheet1.Range("H")
Car = Sheet1.Range("C")
Color = Sheet1.Range("M")
Dim TheAnswer As String
Dim workign As Worksheet, dumping As Workbook
Set workign = ActiveSheet
Set dumping = Workbooks.Add
For x = 1 To 17
working.rows(x).EntireRow.copy
dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
Next
For x = 1 To workign.Cells.SpecialCells(x1Cells(x1CellTypeLastC ell).Row
If LCase$(working.Cells(x,8).Value) = TheAnswer Then
dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
End If
Next
End Sub

Userform to query?
 

Ok, here is what I have for the "search" button on the user form...

Private Sub CommandButton1_Click()
State = Sheet1.Range("H")
Car = Sheet1.Range("C")
Color = Sheet1.Range("M")
Dim TheAnswer As String
Dim workign As Worksheet, dumping As Workbook
Set workign = ActiveSheet
Set dumping = Workbooks.Add
For x = 1 To 17
working.rows(x).EntireRow.copy
dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
Next
For x = 1 To
workign.Cells.SpecialCells(x1Cells(x1CellTypeLastC ell).Row If
LCase$(working.Cells(x,8).Value) = TheAnswer Then dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
End If
Next
End Sub

Are you offering this as the solution to this thread? This appears to
be doing similar to what you were looking for help with in your Feb
26th post under to subject "Input, copy, new book?"! I posted a rework
of your code sample there on Mar 18th. Did you read it yet?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

You're right (kind of). I tried your solution in that thread but it didn't work (it was gathering all sorts of data, not just what I asked it to from the input). So I started thinking perhaps a userform would be better (I'm not saying it is...just figured I'd try it). I figured I'd start a new thread since the userform wasn't mentioned previously in the last one (just trying to follow the rules). I apologize for the confusion.

Also, since the userform allows me to choose several variables instead of just one from the input box, it's a little more robust. However, the code I have above for the userform isn't working....there's something fundamentally wrong with it (the first half, I believe).

I'm a beginner, so I'm not sure which way is best (input box with sort or the userform). All I want to do is have the data I ask for to be pulled and placed into a new workbook.

Again, sorry for the confusion!

J-

Private Sub CommandButton1_Click()
ComboBox1.RowSource = "H"
ComboBox2.RowSource = "C"
ComboBox3.RowSource = "M"

I tried this...but it's another error.

You can close this thread...I'm giving up on this one. I don't think userforms/comboboxes were made to query/pull data (more to add data). It's back to the drawing board.

Thanks anyways, folks.

J-