Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each / Next
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each / Next
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each / Next
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each / Next
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each / Next
Worked beautifully. I greatly appreciate your help, Tom! NicB. -- NicB. ------------------------------------------------------------------------ NicB.'s Profile: http://www.excelforum.com/member.php...o&userid=20639 View this thread: http://www.excelforum.com/showthread...hreadid=544297 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|