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

Greetings,

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

I'd like the user to step through maybe 3 ddms, with the selection of
ddm1 applying a filter and limiting the options of ddm2 (and similarly
for ddm2 and ddm3). For example, if ddm1 chooses a date, then the
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
WD


--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=550457

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

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


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=550457

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

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.

William DeLeo wrote:

Greetings,

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

I'd like the user to step through maybe 3 ddms, with the selection of
ddm1 applying a filter and limiting the options of ddm2 (and similarly
for ddm2 and ddm3). For example, if ddm1 chooses a date, then the
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
WD

--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=550457

--

Dave Peterson

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

Thanks mrice and Dave,

*mrice:* This is my plan right now but my solution is more involved
that it seems like it needs to be. All I can think of is to turn on an
autofilter with ddm1, then copy/paste the filtered list to somewhere
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 utility
in my products as well.

BUT ... I am not seeing how this can help me directly. I basically
want the following functionality that autofilter has but for ddms on a
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 is
turned on, the options on an adjacent one are limited accordingly.

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

Thank again,
WD


--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=550457

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

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.



William DeLeo wrote:

Thanks mrice and Dave,

*mrice:* This is my plan right now but my solution is more involved
that it seems like it needs to be. All I can think of is to turn on an
autofilter with ddm1, then copy/paste the filtered list to somewhere
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 utility
in my products as well.

BUT ... I am not seeing how this can help me directly. I basically
want the following functionality that autofilter has but for ddms on a
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 is
turned on, the options on an adjacent one are limited accordingly.

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

Thank again,
WD

--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=550457

--

Dave Peterson

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

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

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

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

WD


--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=550457

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

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.




William DeLeo wrote:

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

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

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

WD

--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=550457

--

Dave Peterson