PDA

View Full Version : Solved: Showing filtered range in TextBox on UserForm?



Simon Lloyd
02-10-2007, 09:33 AM
Hi all, i am trying to use a userform to query a date chosen from a list in combobox1 then pass this date to the autofilter criteria and show all the results in TextBox1 on the same userform....for instance, if i chose 01/01/07 in the combobox make this the filter criteria once filtered show the selection in the textbox.

Problems i have: 1, the date isn't being passed properly to the filter, or if it is then it isn't showing filtered results its filtering everything out!

2, i cant pass any kind of result to the textbox i either get mismatch error or a very long error number using any other means.

Any ideas?, here's the code!

Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Value, "dd/mm/yyyy")
End Sub
Private Sub CommandButton1_Click()
With Sheets("Sheet1")
Columns("A:C").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=ComboBox1.Value = Format(ComboBox1.Value, "dd/mm/yyyy")
UserForm1.TextBox1.Text = Selection
Selection.AutoFilter
End With
End Sub
Regards,
Simon

mdmackillop
02-10-2007, 10:10 AM
Hi Simon.
Private Sub ComboBox1_afterupdate()
ComboBox1.Value = Format(ComboBox1.Value, "dd/mm/yyyy")
End Sub
Private Sub CommandButton1_Click()
Dim Col As Long
Dim Rng As Range

'Set column to be returned
Col = 3

With Sheets("Sheet1")
Set Rng = Intersect(.Columns("A:C"), .UsedRange)
Rng.AutoFilter Field:=2, Criteria1:=ComboBox1.Value
UserForm1.TextBox1.Text = Rng(Col)
Rng.AutoFilter
End With
End Sub

Simon Lloyd
02-10-2007, 10:54 AM
Malcom thanks for the response, i'm not able to test the code right now as i am about to leave for home, but a couple of questions!



'Set column to be returned
Col = 3

can i set a range of columns? or does this


Set Rng = Intersect(.Columns("A:C"), .UsedRange)
Show everything in the filtered range?, what i need to show really is all filtered results so if that meant 60 rows over 6 columns i would like that to appear in the textbox (think i need to do something here over adding scroll bars and perhaps resizing the cells or text being inserted!), it would never be more than 6 columns but the amount of rows could be a max of 500!

I understand that showing the results on a new sheet and then deleting the sheet may be preferable, but i have put together i nice little set of userforms that can be used for data entry, retrieval or printing none of which the user (my girlfriend!) has to do any typing in, its all point and click!

Regards,
Simon

mdmackillop
02-10-2007, 11:01 AM
Hi Simon
You can really only returm one record to a textbox. If you're expecting multiple returns in multiple columns I think you need a listbox. I'll adjust my code for this.

Simon Lloyd
02-10-2007, 11:05 AM
Speedy reply Malcom!, i thought of a list box but decided to use a textbox with perhaps scroll bars!, but i didnt realise the limitations with it - a listbox will be just fine.....is its still possible to return 6 columns in the listbox?

Regards,
Simon

mdmackillop
02-10-2007, 11:54 AM
I can't quite get this too work. The date autofilter works manually, but not in the code. Here's my test example

Bob Phillips
02-10-2007, 12:49 PM
.

Simon Lloyd
02-10-2007, 12:59 PM
Malcom, thanks for that, in you're initialise code you use this loop
For Each cel In Range(Cells(2, 1), Cells(Rows.Count, 1))
d.Add cel.Text, cel.Text
Nexti understand that you are passing all the results to a variable for use later, however the code does not stop at row 37 it continues on to the end of the sheet, is there a way of using xlUP or something like it to only pass results for the used range?

your example returns the header row each time regardless of date selected......wierd isnt it!, when i originally started i could only filter to the next blank row at the end of the list!

Regards,
Simon

Simon Lloyd
02-10-2007, 01:07 PM
Bob - Clever as usual!, works like a charm but of course you know that already, i noticed this
Set oDates = CreateObject("Scripting.Dictionary")can you explain the use of this please?

Regards,
Simon

Bob Phillips
02-10-2007, 01:13 PM
I stole that directly from Malcolm's code Simon. What he is doing there is using a dictionary object to load all the dates, but because a dictionary key has to be unique, adding duplicate dates fails, but by judicious use of error handling, it carries on just loading unique dates, and the dictionary of unique dates is used to populate the combobox.

Bob Phillips
02-10-2007, 01:22 PM
BTW, working on a Saturday! What's up?

mdmackillop
02-10-2007, 01:31 PM
Hi Simon,
Here's the correction of my mistake in Bob's code, to include the xlUp. I couldn't think why the form was so slow to open! I've also added a line to remove the autofilter.

Bob,
Thanks for the DateFilter function. I'll remember that one.



Option Explicit

Private Sub ComboBox1_Change()
Dim rng As Range
Dim oRow As Range
Dim i As Long

With Sheets("Sheet1")
Set rng = Intersect(.Columns("A:F"), .UsedRange)
Set rng = FilterDate(CDate(Me.ComboBox1.Value))
For Each oRow In rng
Me.ListBox1.AddItem oRow.Cells(1, 1)
For i = 1 To 5
Me.ListBox1.List(Me.ListBox1.ListCount - 1, i - 1) = oRow.Cells(1, i)
Next i
Next oRow
End With
rng.AutoFilter

End Sub

Private Sub UserForm_Activate()
Dim oDates As Object
Dim i As Long
Dim aryDates
Dim cell As Range

Set oDates = CreateObject("Scripting.Dictionary")

On Error Resume Next
For Each cell In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
oDates.Add cell.Text, cell.Text
Next

aryDates = oDates.items

For i = 0 To oDates.Count - 1
ComboBox1.AddItem aryDates(i)
Next

End Sub

Simon Lloyd
02-10-2007, 02:49 PM
Cool, joint effort! well thank you both, nothing wrong with Plagiarism in the right place LOL!

Again thats a first for me again a "dictionary object!", Malcom i found when i ran your code that the UF took around 5 or 6 secs to be useable.....i'm having a beer or three now so i will test ur code i the morning Bob, and as for working saturday..well sadly i work 12 shifts so each day is pretty much like the nesst

Simon Lloyd
02-10-2007, 02:49 PM
Cool, joint effort! well thank you both, nothing wrong with Plagiarism in the right place LOL!

Again thats a first for me again a "dictionary object!", Malcom i found when i ran your code that the UF took around 5 or 6 secs to be useable.....i'm having a beer or three now so i will test ur code i the morning Bob, and as for working saturday..well sadly i work 12 shifts so each day is pretty much like the next!

Very kind Regards,
Simon