Use dd menu choice to filter input for next ddm, etc.

W

William DeLeo

Greetings,

I'd like to set up a form with several drop down menus. The form wil
be designed to search for and pull up a particular record (row on
worksheet) for editing (I just need to identify which record needs t
be edited with this form).

I'd like the user to step through maybe 3 ddms, with the selection o
ddm1 applying a filter and limiting the options of ddm2 (and similarl
for ddm2 and ddm3). For example, if ddm1 chooses a date, then th
options for ddm2 would only correspond to the records with that date.

I can go formula and/or VBA for this, but I'm not sure how to start.

Any direction would be greatly appreciated.

TIA
W
 
M

mrice

Using VBA, I would suggest that you populate the second drop down with a
macro linked to the change event of the first.
 
D

Dave Peterson

How about another option.

Apply Data|Filter|autofilter to the range.

Then use the dropdown arrows to limit the visible rows to what you want.

One thing about using the built-in features of excel (like
data|Filter|autofilter) is that if your users (including you???) learn it,
you'll see how useful it is for other worksheets.
 
W

William DeLeo

Thanks mrice and Dave,

*mrice:* This is my plan right now but my solution is more involve
that it seems like it needs to be. All I can think of is to turn on a
autofilter with ddm1, then copy/paste the filtered list to somewher
else, then (with formulae) reduce it to "only unique" and "no blanks"
and finally use that range for ddm2, and so on.

*Dave:* I do use autofilter myself, and like to incorporate its utilit
in my products as well.

BUT ... I am not seeing how this can help me directly. I basicall
want the following functionality that autofilter has but for ddms on
sepparate sheet (or form) from the data:

1. only list each unique record once in the ddm
2. link ddm input ranges to each other so that if one ddm filter i
turned on, the options on an adjacent one are limited accordingly.

Are there VBA functions that could be configured to do what autofilte
does? If not, I may just go with the (albeit undesirable) plan
described to mrice above.

Thank again,
W
 
D

Dave Peterson

The only thing that will stop you from accomplishing your plan is your coding
ability. There are enough tools to do what you want.

But...

It sure sounds like you're just duplicating the same effect that autofilter
already provides. The dropdown for an autofilter shows unique values.
 
W

William DeLeo

But, can I use autofilter without allowing the user to be inside th
particular sheet that holds the data? My client doesn't want the use
to have such access (only one record accessed at a time for data inpu
corrections). I just need to allow the user to identify one record an
then pull that info to another part of my workbook. But the user need
three menu choices to adequately narrow down the list.

Regarding code ... I'm happy to go that route ... any suggestions fo
functions that I could start with?

Thanks and sorry to drag this out ... I'll stop asking questions no
and taking advantage of your hospitality. Your help is greatl
appreciated.

W
 
D

Dave Peterson

I built a userform with 3 comboboxes and 2 command buttons on it.

This is the code behind the userform:

Option Explicit
Private Sub ComboBox1_Change()

Dim iRow As Long
Dim NoDupes As Collection
Dim iCtr As Long

Set NoDupes = New Collection

With Me.ComboBox2
.Clear
.Enabled = False
End With

With Me.ComboBox3
.Clear
.Enabled = False
End With

If Me.ComboBox1.ListIndex <> -1 Then
With Worksheets("Sheet1")
On Error Resume Next
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If LCase(.Cells(iRow, "A").Value) _
= LCase(Me.ComboBox1.Value) Then
NoDupes.Add .Cells(iRow, "b").Value, _
CStr(.Cells(iRow, "b").Value)
End If
Next iRow
On Error GoTo 0

If NoDupes.Count > 0 Then
For iCtr = 1 To NoDupes.Count
Me.ComboBox2.AddItem NoDupes(iCtr)
Next iCtr
Me.ComboBox2.Enabled = True
Else
MsgBox "No choices for combobox2"
End If
End With
End If

End Sub

Private Sub ComboBox2_Change()

Dim iRow As Long
Dim NoDupes As Collection
Dim iCtr As Long

Set NoDupes = New Collection

With Me.ComboBox3
.Clear
.Enabled = False
End With

If Me.ComboBox2.ListIndex <> -1 _
And Me.ComboBox1.ListIndex <> -1 Then
With Worksheets("Sheet1")
On Error Resume Next
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If LCase(.Cells(iRow, "A").Value) _
= LCase(Me.ComboBox1.Value) Then
If LCase(.Cells(iRow, "B").Value) _
= LCase(Me.ComboBox2.Value) Then
NoDupes.Add .Cells(iRow, "c").Value, _
CStr(.Cells(iRow, "c").Value)
End If
End If
Next iRow
On Error GoTo 0

If NoDupes.Count > 0 Then
For iCtr = 1 To NoDupes.Count
Me.ComboBox3.AddItem NoDupes(iCtr)
Next iCtr
Me.ComboBox3.Enabled = True
Else
MsgBox "No choices for combobox3"
End If
End With
End If

End Sub
Private Sub ComboBox3_Change()

If Me.ComboBox3.ListIndex <> -1 _
And Me.ComboBox2.ListIndex <> -1 _
And Me.ComboBox1.ListIndex <> -1 Then
Me.CommandButton2.Enabled = True
Else
Me.CommandButton2.Enabled = False
End If

End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()

Dim iRow As Long
Dim DestCell As Range

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

If Me.ComboBox3.ListIndex <> -1 _
And Me.ComboBox2.ListIndex <> -1 _
And Me.ComboBox1.ListIndex <> -1 Then
With Worksheets("Sheet1")
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If LCase(.Cells(iRow, "A").Value) _
= LCase(Me.ComboBox1.Value) Then
If LCase(.Cells(iRow, "B").Value) _
= LCase(Me.ComboBox2.Value) Then
If LCase(.Cells(iRow, "C").Value) _
= LCase(Me.ComboBox3.Value) Then
'do the copy
.Rows(iRow).Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(1, 0)
End If
End If
End If
Next iRow
End With
End If

Call UserForm_Initialize

End Sub

Private Sub UserForm_Initialize()

Dim iRow As Long
Dim NoDupes As Collection
Dim iCtr As Long

With Me.ComboBox1
.Enabled = True
.Clear
.Style = fmStyleDropDownList
End With

With Me.ComboBox2
.Enabled = False
.Clear
.Style = fmStyleDropDownList
End With

With Me.ComboBox3
.Enabled = False
.Style = fmStyleDropDownList
End With


Set NoDupes = New Collection

With Worksheets("Sheet1")
On Error Resume Next
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
NoDupes.Add .Cells(iRow, "A").Value, CStr(.Cells(iRow, "A").Value)
Next iRow
On Error GoTo 0

If NoDupes.Count > 0 Then
For iCtr = 1 To NoDupes.Count
Me.ComboBox1.AddItem NoDupes(iCtr)
Next iCtr
End If
End With

With Me.CommandButton1
.Enabled = True
.Caption = "Cancel"
End With

With Me.CommandButton2
.Enabled = False
.Caption = "Ok"
End With

End Sub

I _think_ it does what you want.

And to show the userform you can put this in a general module:

Option Explicit
sub ShowMyForm()
userform1.show
end sub

You can plop a button from the Forms toolbar onto the worksheet and assign that
macro to the button or you can incorporate it into your code someother way.

You may want to review these two pages from Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html
and
http://www.contextures.com/xlUserForm02.html

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

Depending on how advanced you are...

Professional Excel Development
By Stephen Bullen, Rob Bovey, John Green

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.
 
Top