Complex Excel Problem!!

[direktoren]

Hi

For the sake of ease I'll get right to the thick of it!

What I have is;
2 sheets, start and data.

- On data i have my sourcedata (surprise!), the first five columns are "hierachical" levels, ranging from country, to region to area and all the way down to individual branch - the rest of the columns (6-33) are just data.

- On start I have 5 comboboxes, 28 checkboxes and one commandbutton

What I wanted to happen was;

In Combobox1 I wanted a listing of the countries (column A in data). Then combobox2 should show all off the regions (column A) that is in the country chosen with combobox1. And so forth with the rest of them.

Finally the user should check of which information from data (columns 6 - 33), he wants to include in his table, then press the commandbutton and the specific report should be generated (by copying the selection from the data sheet onto a new sheet).

My Problem is;
1 - I'm a complete rookie when it comes to comboboxes - I only know additem (which I will then need to state manually everytime it changes) and then I just found listfillrange - the problem with this is it doesnt filter the list shown - so I have Ireland showing up some 2000 times!

2 - I don't know how to fill the comboboxes 2-5, as stated previously. I guess what I need is something like autofilter - but I don't know if it can be controlled from VBA?

Any help would be greatly appreciated!!

[si_the_geek]

I thought the Excel combo's didn't use AddItem, perhaps I was thinking of another Office program.

The important thing to realise is that AddItem doesn't need to be a "manual" value - you can read the values from the sheet.

Assuming that the countries are in order, you can use code like this:

Dim strThisCountry as String, strLastCountry as String
Dim lngRowNumber as Long
 
  strLastCountry = ""
  [u]Combo1[/u].Clear  '(change to name of combo to fill)
 
  With WorkSheets("[U]Sheet1[/U]")  '(change to name of sheet with data)
    For lngRowNumber = [u]2[/u] To .UsedRange.Rows.Count  '(change to number of first row)
      strThisCountry = .Cells(lngRowNumber, [u]1[/u])  '(change to column number [A=1, B=2, ...])
      If strThisCountry <> strLastCountry Then
        [u]Combo1[/u].Additem strThisCountry
        strLastCountry = strThisCountry
      End If
    Next lngRowNumber
  End With

See the Excel Tutorial link below for an explanation of the parts of the code I used.

[Static]

hmmm.. My first thought was along the same lines as what Si is saying..
but now im thinking...

Are you doing this all in excel? or are u making a VB front end for this?

[DKenny]

direktoren

Here's a sub and a sample Workbook that will populate a ComboBox based on a Column number - adding only unique instances of each value.
The sub will, optionally, filter the values to add, based on a passed parameter.

There is one caveat with this. If you have the same child value under multiple parents, the filter for the children of that child value will not take the parent value into consideration. For example in my demo WB, if both Ireland and USA had a child called "South", the filtering of combobox3 by combobox2 would not work correctly. This could be corrected fairly easily,.

Option Explicit
 
Sub Populate_Box(ByRef MyCombo As ComboBox, ByVal ColNum As Long, Optional ByVal ParentVal As String)
Dim lRowCount As Long
Dim rngData As Range
Dim lRowNum As Long
Dim sEntry As String
Dim bLoaded As Boolean
Dim lIndex As Long
    
    'Remove all existing values
    'from the combobox
    MyCombo.Clear
    
    'Set the input range for the CB
    With Worksheets("Data")
        lRowCount = .UsedRange.Rows.Count
        Set rngData = .Range(.Cells(2, ColNum), .Cells(lRowCount, ColNum))
    End With
    
    'Loop through all rows in the input range
    For lRowNum = 1 To rngData.Rows.Count
        
        'I have assumed that the Parent (Filtering Value)
        'is in the next column to the left
        
        'If there is no filter..
        If ParentVal = "" Then
            
            '..store the value
            sEntry = rngData.Cells(lRowNum, 1).Value
        
        '..Otherwise...
        Else
            '...Only store the value if the Filter Value
            '...if the preceding column has the parent value
            If rngData.Cells(lRowNum, 1).Offset(0, -1).Value = ParentVal Then
                sEntry = rngData.Cells(lRowNum, 1).Value
            End If
        End If
        
        'Assume that the value has not been loaded
        'into the CB
        bLoaded = False
        
        'Only continue if we have a value to load
        If sEntry <> "" Then
            
            'Loop through the current values in the CB
            For lIndex = 0 To UBound(MyCombo.List)
                
                'If the value is already in the CB - record this fact
                If MyCombo.List(lIndex) = sEntry Then
                    bLoaded = True
                    Exit For
                End If
            Next lIndex
            
            'If the value is not in the CB, Add it
            If Not bLoaded Then
                MyCombo.AddItem sEntry
            End If
            
        End If
    Next lRowNum
 
End Sub

[direktoren]

Hi guys

Thanx for taking time to help me out here - I'm curently going with your idea Declan, but a few questions have arrived:

I tried runnning your worksheet and it works splendidly, however when I try to implement it into my own workbook, I'm getting an error (Unspecified error)at the following line;

'Remove all existing values
    'from the combobox
    MyCombo.Clear

I noticed that on your sheet, the comboboxes are on the data sheet, while at mine it is on the start sheet - is this causing the error? I can't find any sheet references though except for the setrange for the input?

[DKenny]

Hummm.
I 'm not sure what error you are getting or why. Here's a revised copy of the sample book with an extra sheet added and the CB's moved to that sheet and the code still works for me. Can you test this one?

[direktoren]

Hi Declan

I also got yours to function - don't know what is wrong with mine, I can't find any differences, but then again, my eye probably isn't as trained as yours!

hmm... tried running it at my pc at home - now i got a new error!

I've uploaded my workbokk, hoping that you might want to take a look - I've had to use some random data in the data sheet, since the other data is categorized as classified - but it shouldn't matter what names ot numbers are in the data sheet i guess.

regards Nicolaj

[DKenny]

OK, I have found the problem. You have specified a ListFillRange for each of your Comboboxes. Removing these values will solve the problem.

Also I would suggest changing hte line where we set the lRowCount variable to the following. The UsedRange is unrelaible and I never should have included it.

lRowCount = .Range("A1").End(xlDown).Row

[direktoren]

Okay - thanx...It makes sense since I started out by using the listfillrange! I will have a look at tomorrow when I go to work - hopefully that should solve my problems!

Thanks again
Nicolaj

[direktoren]

Hey Declan

just tried it - works as a peach! Thanx

One other problem however, which I was hoping that you could might help me with... Its in my final copy function:

Private Sub CommandButton1_Click()
Dim x As Long
Dim i As Long
Dim oControl As OLEObject
Dim dSheet As Worksheet
Dim sSheet As Worksheet
 
Set dSheet = ActiveWorkbook.Worksheets("Data")
Set sSheet = ActiveWorkbook.Worksheets("Start")
 
dSheet.Copy after:=Sheets(1)
 
If Not ComboBox1.Value = "" Then
For i = 2 To Sheet3.Cells(2, 1).End(xlDown).Row
    If Not Sheet3.Cells(i, 1).Value = ComboBox1.Value Then
        Sheet3.Rows(i).EntireRow.Delete
    End If
Next i
If Not ComboBox2.Value = "" Then
For i = 2 To Sheet3.Cells(2, 1).End(xlDown).Row
    If Not Sheet3.Cells(i, 2).Value = ComboBox2.Value Then
        Sheet3.Rows(i).EntireRow.Delete
    End If
Next i
End If
If Not ComboBox3.Value = "" Then
For i = 2 To Sheet3.Cells(2, 1).End(xlDown).Row
    If Not Sheet3.Cells(i, 3).Value = ComboBox3.Value Then
        Sheet3.Rows(i).EntireRow.Delete
    End If
Next i
End If
If Not ComboBox4.Value = "" Then
For i = 2 To Sheet3.Cells(2, 1).End(xlDown).Row
    If Not Sheet3.Cells(i, 4).Value = ComboBox4.Value Then
        Sheet3.Rows(i).EntireRow.Delete
    End If
Next i
End If
If Not ComboBox5.Value = "" Then
For i = 2 To Sheet3.Cells(2, 1).End(xlDown).Row
    If Not Sheet3.Cells(i, 5).Value = ComboBox5.Value Then
        Sheet3.Rows(i).EntireRow.Delete
    End If
Next i
End If
For Each oControl In sSheet.OLEObjects
        With oControl
            If Left(.Name, 3) = "chk" Then
            If Not .Object.Value = True Then
                For i = 6 To Sheet3.Cells(1, 1).End(xlToRight).Column
                   If Sheet3.Cells(1, i).Value = .Object.Caption Then
                       Sheet3.Columns(i).EntireColumn.Delete
                   End If
                Next i
            End If
            End If
        End With
    Next oControl
Else
    Sheet3.Delete
End If
 
End Sub

So I'm starting by copying my data sheet and thereafter trying to delete the rows and columns which doesn't match my criterias. If nothing is selected then the copy should get deleted again.

I can't find the error -I get an "object required" - but I can't see what's wrong, to me it looks pretty straight forward!

Nicolaj

I