ComboBox AutoFilter results to different sheet 4

[alwayslrN]

Hello, first let me start by saying am very new to VBA and I've searched countless sites, including this one, to get to what I need but I am not sure what to do at the same time as being overwhelmed.

I am using Excel 2000 on a Windows 2000 machine.

What I have is a worksheet currently just called sheet2 that holds source data. I have called that data training_database using the OFFSET function so the named range can grow dynamically.

On another sheet I hold records for three other named ranges, Group, Level, and Type. Each of these is also set up to grow dynamically.

On a third sheet, sheet6, I have three comboboxes, cmbGroup, cmbLevel, and cmbType. I populated these using their ListFillRange.

The database I mentioned above has the following headings. Group, Level, Type, Course, and Organization. What I want to do in the sheet that contains the comboboxes is select the records that correspond to the records for the value selected in the list, but on that page I only want to display the Organization and Course.

I recorded a macro using AutoFilter to see the results, but I am not sure how to tie this to what I need.

In the cmbGroup change event, for example, I put the following, but I know this is not going to do what I want, but I really do not know enough to do what I want to do.

Private Sub cmbGroup_Change()
Range("training_database").AutoFilter Field:=1, _ Criteria1:=cmbGroup.Text
End Sub

I really would appreciate some guideance. I've purchased several books and have looked online, but I am lost.

Thanks

 

[alwayslrN]

I have a funny feeling doing an autofilter is not what I want.

When I changed the above to

Private Sub cmbGroup_Change()
Sheet4.Select
Sheet4.Range("training_database").AutoFilter Field:=1, _ Criteria1:=cmbGroup.Text
End Sub

and added the following for the other two comboboxes

Private Sub cmbLevel_Change()
Sheet4.Select
Sheet4.Range("training_database").AutoFilter Field:=2, Criteria1:=cmbLevel.Text
End Sub

Private Sub cmbType_Change()
Sheet4.Select
Sheet4.Range("training_database").AutoFilter Field:=3, Criteria1:=cmbType.Text
End Sub

It put an autofilter drop-down button on the the heading titled Group, but I get the following error

Autofilter Method of range class failed and the row with the heading of Group in the named range training_database now has a autofilter drop-down list button. This (having a filter drop-down button) is not what I want. I was using the combobox to do that, I just wanted the comboboxes to select the records based on the selections from the comboboxes and then copy the results (which I have not even begun to work on yet) to the other page mentioned above.

The other issue is the other two comboboxes, when selected, yield the same error, but no filter drop-list appears on these headers. I suspect that is because I am not tying the code to those rows?

I'm going to keep trying to work through this. I'm new, but I am determined.

 

[PHV]

Have you tried to play with the VLookUp worksheet function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alwayslrN]

Thanks for the reply, but a vlookup will not help me, at least I do not think it will.

The training_database range has the fields I mentioned above and under each of those headers there are several records where Group, for example, has 5 different groups, with several records.

Here is a simple example with made up data

Group Level Type Course Organization
Ops Manager Pro sssss ABC
Ops Sup Am xxxxx ABC
CR Manager Pro sddood XYZ
Ops Train Pro uuuuu INT
Adm Train Tech ddddd INT
Ops Train Pro sssssd INT

I want if the user selects Ops (Group), Train (Level), and Pro(Type) to return in a different sheet

Course Organization
uuuuu INT
sssssd INT

Eventually, I would also want each of the 3 to have an ALL option to select everything from the table that contains all the records, but I need to be able to tackle this portion of the learning curve first.

 

[Fenrirshowl]

The Gurus can correct me if I'm wrong but I think you need to look at using Visual Basic to query your table referring to parameter cells.

I've copied your table to Sheet1, range A1 and surrounding of a workbook called Autofilter.xls.

I've set up two list, one for Group and one for Organisation called GRPLIST and ORGLIST (simply lists of the unique entries in each list)

Ranges G1 & H1 are validated lists referring to GRPLIST and ORGLIST respectively. (See Data Menu / Validation - set as lists as acceptable entries and type "=GRPLIST" to refer to a named range)

I recorded and amended setting up a query (Data Menu / Get External Data / Database Query etc)

Sub Macro1()
Application.EnableEvents = False
Range("J2").CurrentRegion.Clear
txt1 = Range("G1")
txt2 = Range("H1")
arrytxt = "SELECT `Sheet1$`.Course, `Sheet1$`.Type" & Chr(13) & "" & Chr(10) & "FROM `H:\TekTips2\Autofilter`.`Sheet1$` `Sheet1$`" & Chr(13) & "" & Chr(10) & "WHERE (`Sheet1$`.Group='" & txt1 & "') AND (`Sheet1$`.Organization='" & txt2 & "')"
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Excel Files;DBQ=H:\TekTips2\Autofilter.xls;DefaultDir=H:\TekTips2;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("J1"))
        .CommandText = Array(arrytxt)
        .Name = "Query from Excel Files_14"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwritedeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
Application.EnableEvents = True
End Sub

Then, under the worksheet change event for sheet2:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call Macro1
End Sub

For some reason I get a read only version of the workbook opening (only one though) but apart from that it seems to work. You will need to include the additional LEVEL parameter In a similar way. Note - don't put this in the I column on Sheet2 as the currentregion would then clear G1 to I1 in addition to the old query results.

Hopefully the above should give you some ideas.

Fen

 

[Fenrirshowl]

Sorry, should have been worksheet change event, not selection change

Private Sub Worksheet_Change(ByVal Target As Range)
Call Macro1
End Sub

Fen

 

[alwayslrN]

Wow Fenrirshowl - I suddenly feel overwhelmed and that I have gotten myself into something that is way over my head.

I did not realize this would be so complicated. I just thought it was something that was somewhat simple, but my being a novice was hendering from seeing the simple stuff.

Can you explain to my why you are suggesting this method?

Thanks for taking the time to help.

 

[Fenrirshowl]

Autofilter is a useful tool for summarising data using parameters. Advanced filter is very useful for this as well, but you are unfortunately limited to summarising the data on the same sheet.

You said that you are new to VB so in the early days it usually easier to record a macro and do what you require "long hand". The above was written by recording a macro and amending a couple of key lines.

What you want to do would be easier in MS Access where queries can be set up far more easily & data summarised. (The aim being to summarise existing data rather than create a second table.)

All that being said, you could just use VB. Assuming your source data is on Sheet1 and the user will be amending and viewing the summarised data on Sheet2 with cells G1 and H1 on Sheet2 validated as before, place this code into sheet2's change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Sheets("Sheet2").Range("A1").CurrentRegion.ClearContents
Sheets("Sheet2").Range("A1") = "Type"
Sheets("sheet2").Range("B1") = "Course"
txt1 = Sheets("Sheet2").Range("G1")
txt2 = Sheets("Sheet2").Range("H1")
z = 2 '  row reference for destination sheet
For x = 2 To Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    If Sheets("Sheet1").Cells(x, 1) = txt1 And Sheets("Sheet1").Cells(x, 5) = txt2 Then
        Sheets("Sheet2").Cells(z, 1).Value = Sheets("Sheet1").Cells(x, 3).Value
        Sheets("Sheet2").Cells(z, 2).Value = Sheets("Sheet1").Cells(x, 4).Value
        z = z + 1
    End If
Next x
Application.EnableEvents = True
End Sub

The downside with this is that this macro creates a second set of data based on the original rather than summarising what already exists. However, it is easier to understand and amend as required!

Fen

 

[xlbo]

Whilst you can do this in a query, you can certainly use autofilter as well

You seem to have the beginnings correct - you need to use yourt comboboxes to manage the autofilter but once this is done, all you need to do is copy the filtered data to a new spreadsheet - this is easy as you can copy the whole "training_database" range to the new sheet and because you have hidden rows using the filter, only the visible rows will be pasted across. The final action is then to remove the autofilter

Your best bet is to record yourself performing the filter and copy and then post the code back here so that we can help you modify it...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[alwayslrN]

Thank you xlbo and fenrirshowl for your responses.

xlbo - This is the recorded macro using the sheet that contains what is being considered the database. I know what I want is the sheet that holds the combo boxes to supply the criteria for the autofilter. I the only way I know how to have the macro below reference the values from the combo boxes does not work, because the module does not recognize those objects.

Sub macAutoFilter()
'
' macAutoFilter Macro
' Macro recorded 10/10/2006

'
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Operations"
Selection.AutoFilter Field:=2, Criteria1:="Manager"
Selection.AutoFilter Field:=3, Criteria1:="Professional/Leadership"
Range("D106:E135").Select
Selection.Copy
Sheets("Sheet6").Select
ActiveSheet.Paste
Sheets("Training_Database").Select
Application.CutCopyMode = False
Selection.AutoFilter
End Sub

Fenrirshowl - I understand the query statement, my problem is because I have set up what would be considered the table with a range that uses the OFFSET function to handle the table as it grows, the ODBC setting thinks that no table exists.

The kicker about all of this is, I have a pivot table that gets me 98% where I want to be. The page function of the pivot table takes care of what I want from the combo boxes, the problem I have is, the user would like the data elements in the pivot table to have hyperlinks. From all indications that I have read so far there is no way for me to do that. I did find a link online that states using the hyperlink function outside of the pivot table, but my problem with that is the pivot table size changes with each change in filtering.

The biggest plus in what I was trying to accomplish here is that I would learn a WHOLE lot at one time, that's if I do not shot myself first.

 

[DirkStruan]

Did you not get the dropdowns to trigger the autofilter?

I rarely use comboboxes from the Control Toolbox Toolbar (normally sticking with those from the Forms Toolbar)but I've had trouble with this before as the code breaks for some unknown reason, however with an error handling line it all seems to go ok. Try:

Private Sub cmbGroup_Change()
On Error Resume Next
Range("training_database").AutoFilter Field:=1, Criteria1:=cmbGroup.Text
End Sub

This will allow you to set the autofilter from the other sheet. You can then use xlbo's idea of copying over the filtered data.
I would initially clear the data you have on the target sheet (some code along the lines of: Range(targetcell).currentregion.clear
and then copy it over, pasting to the target cell. Regarding the hyperlinks - if they are part of your table you can simply copy them over as well.

D

 

[alwayslrN]

Hi D - well that helped to stop the errors, my problem now is it only does an autofilter for the first combo box. I am wondering if that happens because the first combo box changes the available range? I also wonder what would happen if none of the possible items in each list actually exists in the target database?

Private Sub cmbGroup_Change()
On Error Resume Next
shTraining_Database.Range("training_database").AutoFilter Field:=1, Criteria1:=cmbGroup.Text
End Sub

Private Sub cmbLevel_Change()
On Error Resume Next
shTraining_Database.Range("training_database").AutoFilter Field:=2, Criteria1:=cmbLevel.Text
End Sub

Private Sub cmbType_Change()
On Error Resume Next
shTraining_Database.Range("training_database").AutoFilter Field:=3, Criteria1:=cmbType.Text
End Sub

 

[DirkStruan]

Amend each Sub so it applies all the filters:

Private Sub cmbGroup_Change()
On Error Resume Next
If Len(cmbGroup.Text) > 0 Then Range("training_database").AutoFilter Field:=1, Criteria1:="=" & cmbGroup.Text
If Len(cmbLevel.Text) > 0 Then Sheets(2).Range("training_database").AutoFilter Field:=2, Criteria1:="=" & cmbLevel.Text
etc
End Sub

Better yet, write one generalised macro that does this and then CALL it within the dropdown's code.

If you select a combination that doesn't exist, no records will be returned.

I don't quite understand

I am wondering if that happens because the first combo box changes the available range?

can you please expand?

Afraid I have to leave you now - it's getting late in the UK and I've an early start tomorrow. If I get time I'll try to pick this up before I leave, but hopefully you are now nearly there.

 

[Gavona]

Advanced filter is very useful for this as well, but you are unfortunately limited to summarising the data on the same sheet.

Actually that is not the case. Use named ranges and the results can be on another sheet.

Sounds like you have about cracked the problem. However, the approach using advanced filter would be something like this:

Range("training_database").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Range("Criteria").CurrentRegion, _
        CopyToRange:=Range("output"), _
        Unique:=False

range("Output") would contain just the two column headings "Organisation" and "course"

Criteria range would have headings of Group, Level and Type on the first row. On the next row set Data,Validation to allow list and specify your named ranges Group, Level and Type.

Finally, use events to trigger the routine above whenever there is a change in the criteria range.



Gavin

 

[alwayslrN]

OK - I want to first by saying Thank You to all of you

Next I want to inform you that I now have the filtering portion working by doing the following:

Private Sub cmbGroup_Change()
On Error Resume Next
If Len(shCourses.cmbGroup.Text) > 0 Then shTraining_Database.Range("A1").AutoFilter Field:=1, Criteria1:="=" & shCourses.cmbGroup.Text
End Sub

Private Sub cmbLevel_Change()
On Error Resume Next
If Len(shCourses.cmbLevel.Text) > 0 Then shTraining_Database.Range("B1").AutoFilter Field:=2, Criteria1:="=" & shCourses.cmbLevel.Text
End Sub

Private Sub cmbType_Change()
On Error Resume Next
If Len(shCourses.cmbType.Text) > 0 Then shTraining_Database.Range("C1").AutoFilter Field:=3, Criteria1:="=" & shCourses.cmbType.Text
End Sub

My next steps are:
(1) Add an All option to each combo box and display the information accordingly.

(2) Do the copy paste only copying the two columns from the original list of 5

(3) For that copy I need to be sure the macro code only copies the number of rows that result from the filter. I've found when I record macros it records the range that I select, but that range will be different each time. Always the same two columns, but the number of rows can be different.

(4) In doing the paste I need the column width to remain the same as the copied row. When I recorded the macro it was defaulting to the size of the cell of the combo boxes.

Oh...I went away from using the named range of training_database at first I had done the offset for incorrectly. It was
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Hence, why only the Group column was getting a filter object as mentioned in one of my earlier posts. I changed it to

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$1:$1))

but the filters were not happening at all.

I still have to review how to do some things in these posts so I do not know how to do the quotes thing that Dirk did, but when I made the comment about the range I was tired and grasping at straws.

Again thank you all - I will continue to work on this.

 

[alwayslrN]

Yipeee!!

I have successfully finished the first item from the next steps thanks to taking the suggestion of reviewing what running a macro procduces.

The code now looks like:

Private Sub cmbGroup_Change()
On Error Resume Next
'If shCourses.cmbGroup.Text = "All" Then shTraining_Database.Range("A1").AutoFilter Field:=1 Else
If Len(shCourses.cmbGroup.Text) > 0 And shCourses.cmbGroup.Text <> "All" Then
shTraining_Database.Range("A1").AutoFilter Field:=1, Criteria1:="=" & shCourses.cmbGroup.Text
Else
shTraining_Database.Range("A1").AutoFilter Field:=1
End If
End Sub

Private Sub cmbLevel_Change()
On Error Resume Next
If Len(shCourses.cmbLevel.Text) > 0 And shCourses.cmbLevel.Text <> "All" Then
shTraining_Database.Range("B1").AutoFilter Field:=2, Criteria1:="=" & shCourses.cmbLevel.Text
Else
shTraining_Database.Range("B1").AutoFilter Field:=2
End If
End Sub

Private Sub cmbType_Change()
On Error Resume Next
If Len(shCourses.cmbType.Text) > 0 And shCourses.cmbType.Text <> "All" Then
shTraining_Database.Range("C1").AutoFilter Field:=3, Criteria1:="=" & shCourses.cmbType.Text
Else
shTraining_Database.Range("C1").AutoFilter Field:=3
End If
End Sub

 

[alwayslrN]

The next step of copy paste is not complete, but I do not like how the screen flashes as it runs the macro. I am done for the night. It's 11:00 est and I need to get up for work in a few short hours.

Private Sub cmbGroup_Change()
On Error Resume Next
'If shCourses.cmbGroup.Text = "All" Then shTraining_Database.Range("A1").AutoFilter Field:=1 Else
If Len(shCourses.cmbGroup.Text) > 0 And shCourses.cmbGroup.Text <> "All" Then
shTraining_Database.Range("A1").AutoFilter Field:=1, Criteria1:="=" & shCourses.cmbGroup.Text
Else
shTraining_Database.Range("A1").AutoFilter Field:=1
End If

'copy and paste results
Range("A4:B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("Training_Database").Select
Range(Range("D1").Offset(1, 0), Range("E1").Offset(1, 0)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet6").Select
Range("A4").Select
ActiveSheet.Paste

End Sub

Private Sub cmbLevel_Change()
On Error Resume Next
If Len(shCourses.cmbLevel.Text) > 0 And shCourses.cmbLevel.Text <> "All" Then
shTraining_Database.Range("B1").AutoFilter Field:=2, Criteria1:="=" & shCourses.cmbLevel.Text
Else
shTraining_Database.Range("B1").AutoFilter Field:=2
End If

'copy and paste results
Range("A4:B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("Training_Database").Select
Range(Range("D1").Offset(1, 0), Range("E1").Offset(1, 0)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet6").Select
Range("A4").Select
ActiveSheet.Paste

End Sub

Private Sub cmbType_Change()
On Error Resume Next
If Len(shCourses.cmbType.Text) > 0 And shCourses.cmbType.Text <> "All" Then
shTraining_Database.Range("C1").AutoFilter Field:=3, Criteria1:="=" & shCourses.cmbType.Text
Else
shTraining_Database.Range("C1").AutoFilter Field:=3
End If

'copy and paste results
Range("A4:B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("Training_Database").Select
Range(Range("D1").Offset(1, 0), Range("E1").Offset(1, 0)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet6").Select
Range("A4").Select
ActiveSheet.Paste

End Sub

 

[DirkStruan]

Use Application.ScreenUpdating = False at the start of the macro and then return it to True at the end - this should stop the flashing.

D

P.S. Gavin - thanks for the tip on named ranges / autofilter. I shall have a play to see what can be done.

 

[Fenrirshowl]

Gavin

Please can you explain how you would use named ranges to use autofilter to summarise data held on another sheet. I can't figure out how you would do this.

If the data is on sheet1 I can understand you could set an autofilter on the sheet1 and set the filter parameters to cells on another sheet but (if I understand you correctly) I can't see how you would have an autofilter working on sheet2 based on the data in sheet1 even if it is named.

Fen

 

[xlbo]

Fen - ADVANCED filter - not autofilter

Advanced filter has an option to display results on a seperate sheet but there is a bu because it doesn't like the bang (!) in a sheet name so you can't directly reference another sheet. If you use a range name however, there is no need for a bang and it works

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question