Clear Data from Combo Boxes

[eoinymc]

Hi,


I have 3 combo boxes and a macro is run depending on which combo box I choose.


However, if I have chosen something in the first combo box and then choose something from the second combo box, I can still see what I previously chose in combo box 1 and it is confusing some of the users as to the which data they are seeing.


Basically, if I choose something from one combo box, I want to clear the other two.


Here is the code I use for the combo boxes.

Private Sub cmbBase_Change()


    Dim Front As Worksheet, _
    rng As Range
    
    Set Front = Sheets("Front")
    FR = Front.Cells(Rows.Count, 2).End(xlUp).Row
    Set rng = Front.Range("B6:B" & FR)


    If Trim(Len(cmbBase.Value)) = vbNullString Then
        Exit Sub
        
    ElseIf IsError(Application.Match(cmbBase.Value, rng, 0)) Then
        Exit Sub
        
    Else
        PullData (cmbBase.Value)
        Front.Range("I2").Value = cmbBase.Value
        
    End If


End Sub


Private Sub cmbLH_SH_Change()


    Dim Front As Worksheet, _
    rng As Range
    
    Set Front = Sheets("Front")
    FR = Front.Cells(Rows.Count, 3).End(xlUp).Row
    Set rng = Front.Range("C6:C" & FR)


    If Trim(Len(cmbLH_SH.Value)) = vbNullString Then
        Exit Sub
        
    ElseIf IsError(Application.Match(cmbLH_SH, rng, 0)) Then
        Exit Sub
        
    Else
        PullData (cmbLH_SH.Value)
        Front.Range("I2").Value = cmbLH_SH.Value
        
    End If


End Sub


Private Sub cmbNMF_Change()


    Dim Front As Worksheet, _
    rng As Range
    
    Set Front = Sheets("Front")
    FR = Front.Cells(Rows.Count, 4).End(xlUp).Row
    Set rng = Front.Range("D6:D" & FR)


    If Trim(Len(cmbNMF.Value)) = vbNullString Then
        Exit Sub
        
    ElseIf IsError(Application.Match(cmbNMF.Value, rng, 0)) Then
        Exit Sub
        
    Else
        PullData (cmbNMF.Value)
        Front.Range("I2").Value = cmbNMF.Value
        
        
    End If


End Sub

Do I make sense or have I just talked around in a circle?


Thanks,


E

 

[hippiehacker]

to clear a combobox

combobox1.Value=""

 

[eoinymc]

Thanks hippiehacker,

But if I put the following code in:

Private Sub cmbNMF_Change()




    Dim Front As Worksheet, _
    rng As Range
    
    Set Front = Sheets("Front")
    FR = Front.Cells(Rows.Count, 4).End(xlUp).Row
    Set rng = Front.Range("D6:D" & FR)




    If Trim(Len(cmbNMF.Value)) = vbNullString Then
        Exit Sub
        
    ElseIf IsError(Application.Match(cmbNMF.Value, rng, 0)) Then
        Exit Sub
        
    Else
        PullData (cmbNMF.Value)
        Front.Range("I2").Value = cmbNMF.Value
        [B]cmbBase_Change.Value = ""
        cmbLH_SH_Change.Value = ""
[/B]        
        
    End If

End Sub

It doesn't clear the other 2...any other ideas? Or am I doing something wrong?

I get a compile error.

Thanks,

E

 

[eoinymc]

I sorted it...

I just took out the _Change part..

Thanks again for your help!

 

[hippiehacker]

replace

cmbBase_Change.Value = ""
cmbLH_SH_Change.Value = ""

with

cmbBase.Value = ""
cmbLH_SH.Value = ""

as cmbBase_Change is the name of the macro and not of the name from the combobox