Are these comoboxes from the control toolbox toolbar or from the forms toolbar?
for control toolbox:
Dim sh as Worksheet, obj as OleObject
Dim msg as Variant
for sh in thisworkbook.worksheets
for each obj in sh.OleObjects
if typeof obj.Object is MSForms.Combobox then
if obj.Object.Value = "" then
Msg = MsgBox("All questions have not been completed. " & _
" Please double-check the drop down boxes to ensure no " & _
"questions were left unanswered.", vbOKOnly, "Missing Answers")
exit sub
end if
end if
Next
Next
for forms toolbar:
Dim sh as Worksheet, cbox as DropDown
for each sh in ThisWorkbook.Worksheets
for each cbox in sh.Dropdowns
if cbox.Value = "" then
Msg = MsgBox("All questions have not been completed. " & _
" Please double-check the drop down boxes to ensure no " & _
"questions were left unanswered.", vbOKOnly, "Missing Answers")
exit sub
End if
Next
Next
--
Regards,
Tom Ogilvy
"NicB." wrote:
I need some help using for each / next, as I am not very familiar with
how to use it. I have a worksheet with many combo boxes. I want to
ensure that all of these drop downs have an answer before being emailed
back to me for review. I was thinking something along these lines...
Dim Question As ComboBox
Question = "Question"
Dim WS As Worksheet
For Each WS In Worksheets (do not know what i need here)
If Question = "" Then
Msg = MsgBox("All questions have not been completed. Please
double-check the drop down boxes to ensure no questions were left
unanswered.", vbOKOnly, "Missing Answers")
End If
Next Question
Can anyone help me?
Thank you,
NicB.
--
NicB.
------------------------------------------------------------------------
NicB.'s Profile: http://www.excelforum.com/member.php...o&userid=20639
View this thread: http://www.excelforum.com/showthread...hreadid=544297