searching comboboxes

  • Thread starter Thread starter Cliff Myers
  • Start date Start date
C

Cliff Myers

Hello all,

I'm having the hardest time with this, I'm trying to check for any blank
values in a number of comboboxes. I can get it to work using an If
statement for each cb but I really want to decrease the code by using a For
Next. Here's what I have and I cannot get it to work:
Dim cb As ComboBox
Dim MyCollection
Set MyCollection = ("Sheet1.ComboBox1:Sheet1.ComboBox5")
' if option 1 is chosen then e-mail workbook
If UserForm1.OptionButton1.Value = True Then
ActiveWorkbook.SendMail Recipients:="Cliff Myers", Subject:="test"
ElseIf UserForm1.OptionButton2.Value = True Then
For Each cb In MyCollection
If cb.Value = "" Then
MsgBox "All data is not complete"
Exit For
End If
Next cb
If Entrycells.Value = "" Then
MsgBox "You have an empty cell"
End If
Else
MsgBox "All data is complete"
End If
I'm having errors of object not found, defining objects, etc.
Any help is appreciated.
thanks,
cliff
 
You could loop through all the comboboxes on the worksheet, but that might not
be possible if you have others for different purposes:

Option Explicit

Sub testme01()

Dim OLEObj As OLEObject
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
If OLEObj.Object.Value = "" Then
MsgBox "all data is not complete"
Exit For
End If
End If
Next OLEObj

End Sub

Or if you've named them nicely, you could just loop through the names:

Sub testme02()

Dim iCtr As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")
For iCtr = 1 To 5
If wks.OLEObjects("combobox" & iCtr).Object.Value = "" Then
MsgBox "not all filled"
Exit For
End If
Next iCtr

End Sub
 
Back
Top