Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot Dismiss the MsgBox
Hello all, I don't have a clue why when I click on the OK or Cancel
button the MsgBox does not go away. The only way I can get out is to kill Excel with Control/Alt Delete and end Excel. How do I program the cancel button to end the sub or at least dismiss the MsgBox when clicking OK or Cancel? Any suggestions? Thank you all for your assistance, Ron Sub testfollowup() Dim c As Range For Each c In ActiveSheet.Range("K12:AI10000") If c.Font.ColorIndex = 3 Then MsgBox "Please make additional corrections", vbExclamation + vbOKCancel, "TEST" Else MsgBox "Data validated, good job!" & vbNewLine & "If the sheet is to be printed, clicking on the Print Setup button prepares the file for printing.", vbExclamation + vbOKCancel, "TEST" End If Next c End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot Dismiss the MsgBox
Hi Ron, You need to assign the response to a variable and then test the variable for the user's response. You can use If/Then/Else or Select Case for the testing. Sub testfollowup() Dim c As Range Dim userResponse As Variant For Each c In ActiveSheet.Range("K12:AI10000") If c.Font.ColorIndex = 3 Then userResponse = MsgBox("Please make additional corrections", _ vbExclamation + vbOKCancel, "TEST") Select Case userResponse Case vbCancel Exit Sub 'Or other required code Case vbOK 'Required code here End Select Else userResponse = MsgBox("Data validated, good job!" _ & vbNewLine & _ "If the sheet is to be printed, " & _ "clicking on the Print Setup button " & _ "prepares the file for printing.", _ vbExclamation + vbOKCancel, "TEST") Select Case userResponse Case vbCancel Exit Sub 'Or other required code Case vbOK 'Required code here End Select End If Next c End Sub -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot Dismiss the MsgBox
hi all, With your current looping approach one of the two message boxes is going to appear for every single one of the 10,000-12=9,988 rows (let alone when you factor in each of the columns which potentially can make the amount much larger). This makes me suspect that each time you press okay, you are then seeing the next messagebox quickly appear which makes it seem "that the message box doesn't go away". You could check this by changing your msgbox title eg: Code: -------------------- sgBox "Please make additional corrections", vbExclamation + vbOKCancel, "TEST" & c.address -------------------- To add to OssieMac's approach of ending the macro (Exit Sub)... You could build a range of the cells matching the criteria (w/o using message boxes) & then loop through each of the resulting cells in a userform (possibly showing a current value, new value, ignore this one or next, and a Cancel option to completely stop the process. Tushar shows an example of creating a range using Union which you may be able to adapt. Code: -------------------- http://www.tushar-mehta.com/excel/tips/findall.html -------------------- hth Rob -- broro183 Rob Brockett. Always learning & the best way to learn is to experience... ------------------------------------------------------------------------ broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112296 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot Dismiss the MsgBox
hi you're caught in a loop. the solution i posted earlier displayed the msgbox only when a red font was found. i added code to your other post to correct multiple instances of red fonts ie exit sub after the msgbox. my bad for not seeing that. the second msgbox you added will display for each and every cell in the loop that is not red font. this is why you can't get rid of it. suggestion. during development and testing, choose a much smaller range. and use step mode to trouble shoot. what is the purpose of the second msgbox??? what are we trying to do???? if you're just adding a "good job" message at the end then move the second msgbox OUTSIDE the loop. post back with more info if i am not understanding. regards FSt1 "Ron" wrote: Hello all, I don't have a clue why when I click on the OK or Cancel button the MsgBox does not go away. The only way I can get out is to kill Excel with Control/Alt Delete and end Excel. How do I program the cancel button to end the sub or at least dismiss the MsgBox when clicking OK or Cancel? Any suggestions? Thank you all for your assistance, Ron Sub testfollowup() Dim c As Range For Each c In ActiveSheet.Range("K12:AI10000") If c.Font.ColorIndex = 3 Then MsgBox "Please make additional corrections", vbExclamation + vbOKCancel, "TEST" Else MsgBox "Data validated, good job!" & vbNewLine & "If the sheet is to be printed, clicking on the Print Setup button prepares the file for printing.", vbExclamation + vbOKCancel, "TEST" End If Next c End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot Dismiss the MsgBox
Good pickup Rob. I was guilty of not assessing what the the rest of the code was doing. Only providing the answer on how to handle the user response. The following code displays the cell Id with the error and gives the user the option of continuing the test (and perhaps make a note of the error cell Id) or abort the test and fix the error and then run the test again. If processing is continued then bolError is set to true so that the final msgbox displays that there are still errors. The final msgbox/s do not require testing the user response. Sub testfollowup() Dim c As Range Dim bolErrors As Boolean Dim userResponse As Variant For Each c In ActiveSheet.Range("K12:AI10000") If c.Font.ColorIndex = 3 Then userResponse = MsgBox("Cell " & c.Address(0, 0) _ & " not corrected." & vbCrLf & _ "OK to continue or Cancel to abort test.", _ vbExclamation + vbOKCancel, "TEST") bolErrors = True If userResponse = vbCancel Then Exit Sub End If End If Next c If bolErrors = False Then MsgBox "Data validated, good job!" _ & vbNewLine & _ "If the sheet is to be printed, " & _ "clicking on the Print Setup button " & _ "prepares the file for printing.", _ vbExclamation, "TEST" Else MsgBox "Errors exist." & vbCrLf & _ "Correct and run test again." End If End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cancel (Dismiss) Input Box | Excel Programming | |||
msgbox | Excel Programming | |||
dismiss blanks in lookup | Excel Worksheet Functions | |||
Dismiss a dialog box (Via a Macro) | Excel Programming | |||
Preventing Form Dialog dismiss (Red X) | Excel Programming |