For Each / Next

[NicB.]

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
Dim Question As String
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.

[Tom Ogilvy]

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
>
>

[NicB.]

Thank you, Tom. The combo-boxes are controls, not forms. Your code worked very well, with the exception of one minor detail. The code checks all sheets in the workbook, what if I needed it to only reference one sheet? Is there a way to specify the worksheet? I will try to solve this, but if you have the answer, I would greatly appreciate it.

Thanks again for your help!

NicB.

[Tom Ogilvy]

I checked all sheets because the sample code you posted appeared to be doing
that. Certainly you can just set the variable Sh to refer to a single sheet
and remove that outer loop.

Dim sh as Worksheet, obj as OleObject
Dim msg as Variant
set sh = worksheets("Sheet1")
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

--
Regards,
Tom Ogilvy


"NicB." wrote:

>
> Thank you, Tom. The combo-boxes are controls, not forms. Your code
> worked very well, with the exception of one minor detail. The code
> checks all sheets in the workbook, what if I needed it to only
> reference one sheet? Is there a way to specify the worksheet? I will
> try to solve this, but if you have the answer, I would greatly
> appreciate it.
>
> Thanks again for your help!
>
> NicB.
>
>
> --
> NicB.
> ------------------------------------------------------------------------
> NicB.'s Profile: http://www.excelforum.com/member.php...o&userid=20639
> View this thread: http://www.excelforum.com/showthread...hreadid=544297
>
>

[NicB.]

Worked beautifully. I greatly appreciate your help, Tom!

NicB.