For Each / Next

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NicB. - 22 May 2006 14:48 GMT

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.

Signature

NicB.

Reply to this Message

Tom Ogilvy - 22 May 2006 15:18 GMT

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

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 18 lines]
>
> NicB.

Reply to this Message

NicB. - 22 May 2006 16:26 GMT

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.

Signature

NicB.

Reply to this Message

Tom Ogilvy - 22 May 2006 16:50 GMT

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

Signature

Regards,
Tom Ogilvy

> Thank you, Tom. The combo-boxes are controls, not forms. Your code
> worked very well, with the exception of one minor detail. The code
[quoted text clipped - 6 lines]
>
> NicB.

Reply to this Message

NicB. - 22 May 2006 17:43 GMT

Worked beautifully. I greatly appreciate your help, Tom!

NicB.

Signature

NicB.

Reply to this Message