Combo Box Change Event Problem

[Gauraw]

Hi Experts,


I am using one of the sheet of Excel as DB (Sheet Name - Database) and another sheet as User Interface (Sheet Name - UI). The User as flexibility to search the data based on filter selection. There are 5 Combo Box filters. Everything is working fine. Apart from one unusual problem, based on selection of 4th filter (i.e Company) the required data is getting populated but The Excel size is dramatically getting increased from few KBs to 15-16 MB.




I am calling the 4th filter (i.e Company) in similar manner as other filters. The problem is happening for these contents of the Company filter - Foamix Pharmaceuticals,Novartis,Orexigen Therapeutics etc.


Here is the piece of code that is written :

[/B]


Sub Clear()


Application.ScreenUpdating = False


    'clear the data
    UI.ComboBox1 = ""
    UI.ComboBox2 = ""
    UI.ComboBox3 = ""
    UI.ComboBox4 = ""
    UI.ComboBox5 = ""
'    UI.ComboBox1.Clear
'    UI.ComboBox2.Clear
'    UI.ComboBox3.Clear
'    UI.ComboBox4.Clear
'    UI.ComboBox5.Clear
    Sheets("UI").Visible = True
    Sheets("UI").Select
    Range("dataSet").Select
    Range(Selection, Selection.End(xlDown)).ClearContents


Range("A1").Select
Application.ScreenUpdating = True
End Sub


Private Sub ComboBox1_Change()
UI.ComboBox2 = ""
Call cmdShowData_Click
Range("A1").Select
End Sub


Private Sub ComboBox2_Change()
UI.ComboBox3 = ""
Call cmdShowData_Click


Select Case UI.ComboBox2
Case "Dermatology"
    UI.ComboBox3.ListFillRange = "Dermatology"
Case "Immunology and Inflammatory"
    UI.ComboBox3.ListFillRange = "Immunology"
Case Else
    'Do Nothing
End Select
Range("A1").Select
End Sub


Private Sub ComboBox3_Change()
UI.ComboBox4 = ""
Call cmdShowData_Click
Range("A1").Select
End Sub


Private Sub ComboBox4_Change()
UI.ComboBox5 = ""
Call cmdShowData_Click
Range("A1").Select
End Sub


Private Sub ComboBox5_Change()
Call cmdShowData_Click
Range("A1").Select
End Sub


Private Sub cmdShowData_Click()


Application.ScreenUpdating = False


    'populate data
    strSQL = "SELECT [Tilte],[Body],[Source],[Published Date] FROM [Database$] WHERE "
    If ComboBox1.Text <> "" Then
        strSQL = strSQL & " [Geography]='" & ComboBox1.Text & "'"
    End If
    
    If ComboBox2.Text <> "" Then
        If ComboBox1.Text <> "" Then
            strSQL = strSQL & " AND [Therapy Area]='" & ComboBox2.Text & "'"
        Else
            strSQL = strSQL & " [Therapy Area]='" & ComboBox2.Text & "'"
        End If
    End If


    If ComboBox3.Text <> "" Then
        If ComboBox1.Text <> "" Or ComboBox2.Text <> "" Then
            strSQL = strSQL & " AND [Indication]='" & ComboBox3.Text & "'"
        Else
            strSQL = strSQL & " [Indication]='" & ComboBox3.Text & "'"
        End If
    End If
    
    If ComboBox4.Text <> "" Then
        If ComboBox1.Text <> "" Or ComboBox2.Text <> "" Or ComboBox3.Text <> "" Then
            strSQL = strSQL & " AND [Company]='" & ComboBox4.Text & "'"
        Else
            strSQL = strSQL & " [Company]='" & ComboBox4.Text & "'"
        End If
    End If
    
    If ComboBox5.Text <> "" Then
        If ComboBox1.Text <> "" Or ComboBox2.Text <> "" Or ComboBox3.Text <> "" Or ComboBox4.Text <> "" Then
            strSQL = strSQL & " AND [News Category]='" & ComboBox5.Text & "'"
        Else
            strSQL = strSQL & " [News Category]='" & ComboBox5.Text & "'"
        End If
    End If
    
    If ComboBox1.Text <> "" Or ComboBox2.Text <> "" Or ComboBox3.Text <> "" Or ComboBox4.Text <> "" Or ComboBox5.Text <> "" Then
        'now extract data
        closeRS
        
        OpenDB
        
        rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
        If rs.RecordCount > 0 Then
            Sheets("UI").Visible = True
            Sheets("UI").Select
            Range("dataSet").Select


            Range(Selection, Selection.End(xlDown)).ClearContents
            
            'Now putting the data on the sheet
            ActiveCell.CopyFromRecordset rs
            
            'Add this to have the same format
            With Range("dataSet")
            .Select
            .Copy
            End With
            Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormats)
            Application.CutCopyMode = False
            Range("dataSet").Select
         
        


        Else
            MsgBox "No Matching Recoreds Found!.", vbExclamation + vbOKOnly
            Exit Sub
        End If


        
    End If
    
Application.ScreenUpdating = True


End Sub


[B]

Kindly let me know what is cause of this problem & how I can rectify this.
Please note the database sheet has only 9 records for testing purpose.


I have posted the same question in below forum but till now its hard luck

Combo Box Change Event Problem

Excel as DB - Combo Box Change Event Problem


With Regards,
Gauraw

 

[Gauraw]

Finally able to solve the Issue

.... The problem was with this part of code.

[/COLOR]Add this to have the same format
            With Range("dataSet")
            .Select
            .Copy
            End With
            Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormats)
            Application.CutCopyMode = False
            Range("dataSet").Select

As I have removed this part of code, the data is getting copied from database sheet is in different format. How I can pull/copy the data in same format in UI sheet.
Can any one please suggest me the same. Thanks.