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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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

J
  • Question
3x dependent drop-downs
Replies
0
Views
181
JohnBi
J
C
  • Question
A Bookmark to return to the same row when exiting and opening the application
Replies
8
Views
166
Edgar_
Edgar_
I
  • Question
Listbox not being populate when code is run
Replies
4
Views
286
ipbr21054
I
G
  • Solved
Stop excel macro from running if there is a blank cell in a column
Replies
5
Views
150
RobP
R
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
325
pure vito
P

Forum statistics

Threads
1,214,872
Messages
6,122,026
Members
449,061
Latest member
TheRealJoaquin

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