Cant Clear by ComboBoxs HELP vba

I

igorski88

New Member
Joined
Jul 26, 2011
Messages
43
I created an invoice tool(I would attache it but dont know how...)
Im trying to creat a "clear all" Cmd button.
But im haveing trouble clearing by combo box's. Im pretty sure its becouse i have created a loop. Once one combo box has a change event then it will change another combo box.
I have tried the following to clear the ComboBox's:
Code: 
ComboBox1.Value = ""
ComboBox1.ListIndex = 0
I have even tried to clear the linked cell
Code: 
Sheets("invoice").Range("C21").Value = ""

Now i will show enclose my code. If you see something wrong or an easier way to do something PLEASE LET ME KNOW! Thanks in advance


Code: 
Private Sub CLEARALLBTN_Click()
   
      ComboBox1.Value = ""
      ComboBox1.ListIndex = 0
      ComboBox15.ListIndex = 0
      ComboBox15.Value = ""
      
   
   
For A = 21 To 34
      Sheets("invoice").Range("C" & A).Value = ""
      Sheets("invoice").Range("G" & A).Value = ""
      Sheets("invoice").Range("H" & A).Value = ""
      
    Next A
  
    
End Sub
Private Sub ComboBox15_CHANGE()
    Dim r As Long
    r = ComboBox15.ListIndex + 2
     'it would be better to specify the worksheet as well
    ComboBox1.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    'fill in the unot pric colomn
    Sheets("invoice").Range("h21").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
  'IF ENTER IS HIT THEN GO TO NEXT COMBOBOX
  If KeyAscii = 13 Then
            ComboBox16.SetFocus
        End If
  
End Sub
Private Sub ComboBox16_CHANGE()
    Dim r As Long
    r = Me.ComboBox16.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox2.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h22").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox17_CHANGE()
    Dim r As Long
    r = Me.ComboBox17.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox3.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h23").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox18_CHANGE()
    Dim r As Long
    r = Me.ComboBox18.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox4.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h24").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox19_CHANGE()
    Dim r As Long
    r = Me.ComboBox19.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox5.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h25").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox20_CHANGE()
    Dim r As Long
    r = Me.ComboBox20.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox6.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h26").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox21_CHANGE()
    Dim r As Long
    r = Me.ComboBox21.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox7.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h27").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox22_CHANGE()
    Dim r As Long
    r = Me.ComboBox22.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox8.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h28").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox23_CHANGE()
    Dim r As Long
    r = Me.ComboBox23.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox9.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h29").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox24_CHANGE()
    Dim r As Long
    r = Me.ComboBox24.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox10.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h30").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox25_CHANGE()
    Dim r As Long
    r = Me.ComboBox25.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox11.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h31").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox26_CHANGE()
    Dim r As Long
    r = Me.ComboBox26.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox12.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h32").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox27_CHANGE()
    Dim r As Long
    r = Me.ComboBox27.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox13.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h33").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
Private Sub ComboBox28_CHANGE()
    Dim r As Long
    r = Me.ComboBox28.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox14.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 1).Value
    
    
        'fill in the unot pric colomn
    Sheets("invoice").Range("h34").Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 3).Value
  
End Sub
''''''''''''''''''''''''''''''''
 
Private Sub ComboBox1_CHANGE()
    Dim r As Long
    r = Me.ComboBox1.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox15.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox2_CHANGE()
    Dim r As Long
    r = Me.ComboBox2.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox16.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox3_CHANGE()
    Dim r As Long
    r = Me.ComboBox3.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox17.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox4_CHANGE()
    Dim r As Long
    r = Me.ComboBox4.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox18.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox5_CHANGE()
    Dim r As Long
    r = Me.ComboBox5.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox19.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox6_CHANGE()
    Dim r As Long
    r = Me.ComboBox6.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox20.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox7_CHANGE()
    Dim r As Long
    r = Me.ComboBox7.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox21.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox8_CHANGE()
    Dim r As Long
    r = Me.ComboBox8.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox22.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox9_CHANGE()
    Dim r As Long
    r = Me.ComboBox9.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox23.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox10_CHANGE()
    Dim r As Long
    r = Me.ComboBox10.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox24.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox11_CHANGE()
    Dim r As Long
    r = Me.ComboBox11.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox25.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox12_CHANGE()
    Dim r As Long
    r = Me.ComboBox12.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox26.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox13_CHANGE()
    Dim r As Long
    r = Me.ComboBox13.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox27.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
Private Sub ComboBox14_CHANGE()
    Dim r As Long
    r = Me.ComboBox14.ListIndex + 2
     'it would be better to specify the worksheet as well
    Me.ComboBox28.Value = ThisWorkbook.Sheets("ITEM data").Cells(r, 2).Value
  
End Sub
 
Sort by date Sort by votes
ComboBox1.ListIndex=0 - will display the first item in the ComboBox.
ComboBox1.Vallue="" - will clear the display in the ComboBox.

If your combo boxes have change events then you will need to disable events before clearing them.

disable events
clear combo box(s)
re-enable events
Click to expand...

See if the link below helps.
Suppressing Events In UserForms
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Loop through range and output to another sheet (VBA)
Replies
3
Views
229
PaulWJ
P
B
  • Question Question
VBA - Problem Highlighting Duplicate Values
Replies
3
Views
259
Fluff
Fluff
K
  • Solved
VBA multiple target.column
Replies
7
Views
147
Joe4
Joe4
A
  • Solved
VBA Help!
Replies
2
Views
488
AusNewb
A
I
  • Solved
Trying to open userform & run code from worksheet but see a compile error message
Replies
4
Views
129
ipbr21054
I

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top