|
Message Box Q
Below is a portion of my code that I am trying to use to Mail a Report
If a certain value is in Report K6. I have introduced a Message Box with Yes/No and my code is not mailing, but has not error messages. The scenario in my example below is: If the City shown is London a message box should appear that asks "Are you sure you wish to e-mail Joe Bloggs", if user clicks "No" then the whole sub should exit (reverting to cell K6). If user select "Yes" then sub should continue and run the macro "Mail_Joe_Bloggs" This macro ""Mail_Joe_Bloggs" does work as it did run before I tried to introduce the message box Thanks With Worksheets("Report") If .Range("K6").Value = "London" Then Msg = "Are You sure you wish to e-mail Joe Bloggs?" Title = "Correct City Test" Response = MsgBox(Msg, vbYesNo + vbQuestion, Title) End If If Response = vbNo Then MsgBox "Go Back and Change to Correct City" Sheets("Report").Activate Range("K6").Select Exit Sub End If If Response = vbYes Then Mail_Joe_Bloggs End If End With |
Message Box Q
Hi Sean,
Your code, subject to the adjustment indicated below, works for me. Perhaps, therefore, the problem resides in your call to the email macro. Papou has suggested an improved code structure in his adjacent post. However, looking at your code I would suggest that you explicitly declare all variables and that you prepend each if the following lines with a dot in order to qualify them with the required sheet. replace Sheets("Report").Activate Range("K6").Select with Sheets("Report").Activate .Range("K6").Select --- Regards, Norman "Sean" wrote in message oups.com... Below is a portion of my code that I am trying to use to Mail a Report If a certain value is in Report K6. I have introduced a Message Box with Yes/No and my code is not mailing, but has not error messages. The scenario in my example below is: If the City shown is London a message box should appear that asks "Are you sure you wish to e-mail Joe Bloggs", if user clicks "No" then the whole sub should exit (reverting to cell K6). If user select "Yes" then sub should continue and run the macro "Mail_Joe_Bloggs" This macro ""Mail_Joe_Bloggs" does work as it did run before I tried to introduce the message box Thanks With Worksheets("Report") If .Range("K6").Value = "London" Then Msg = "Are You sure you wish to e-mail Joe Bloggs?" Title = "Correct City Test" Response = MsgBox(Msg, vbYesNo + vbQuestion, Title) End If If Response = vbNo Then MsgBox "Go Back and Change to Correct City" Sheets("Report").Activate Range("K6").Select Exit Sub End If If Response = vbYes Then Mail_Joe_Bloggs End If End With |
Message Box Q
On Apr 18, 8:45 am, "papou" wrote:
Hello Sean Amend your code as suggested below. HTH Cordially Pascal With Worksheets("Report") If .Range("K6").Value = "London" Then Msg = "Are You sure you wish to e-mail Joe Bloggs?" Title = "Correct City Test" Response = MsgBox(Msg, vbYesNo + vbQuestion, Title) Select Case Response Case vbNo MsgBox "Go Back and Change to Correct City" Sheets("Report").Activate Range("K6").Select Exit Sub Case Else Mail_Joe_Bloggs End Select End If End With "Sean" a écrit dans le message de news: . com... Below is a portion of my code that I am trying to use to Mail a Report If a certain value is in Report K6. I have introduced a Message Box with Yes/No and my code is not mailing, but has not error messages. The scenario in my example below is: If the City shown is London a message box should appear that asks "Are you sure you wish to e-mail Joe Bloggs", if user clicks "No" then the whole sub should exit (reverting to cell K6). If user select "Yes" then sub should continue and run the macro "Mail_Joe_Bloggs" This macro ""Mail_Joe_Bloggs" does work as it did run before I tried to introduce the message box Thanks With Worksheets("Report") If .Range("K6").Value = "London" Then Msg = "Are You sure you wish to e-mail Joe Bloggs?" Title = "Correct City Test" Response = MsgBox(Msg, vbYesNo + vbQuestion, Title) End If If Response = vbNo Then MsgBox "Go Back and Change to Correct City" Sheets("Report").Activate Range("K6").Select Exit Sub End If If Response = vbYes Then Mail_Joe_Bloggs End If End With- Hide quoted text - - Show quoted text - Thanks Pascal, thats exactly what I want |
Message Box Q
On Apr 18, 8:53 am, "Norman Jones"
wrote: Hi Sean, Your code, subject to the adjustment indicated below, works for me. Perhaps, therefore, the problem resides in your call to the email macro. Papou has suggested an improved code structure in his adjacent post. However, looking at your code I would suggest that you explicitly declare all variables and that you prepend each if the following lines with a dot in order to qualify them with the required sheet. replace Sheets("Report").Activate Range("K6").Select with Sheets("Report").Activate .Range("K6").Select --- Regards, Norman "Sean" wrote in message oups.com... Below is a portion of my code that I am trying to use to Mail a Report If a certain value is in Report K6. I have introduced a Message Box with Yes/No and my code is not mailing, but has not error messages. The scenario in my example below is: If the City shown is London a message box should appear that asks "Are you sure you wish to e-mail Joe Bloggs", if user clicks "No" then the whole sub should exit (reverting to cell K6). If user select "Yes" then sub should continue and run the macro "Mail_Joe_Bloggs" This macro ""Mail_Joe_Bloggs" does work as it did run before I tried to introduce the message box Thanks With Worksheets("Report") If .Range("K6").Value = "London" Then Msg = "Are You sure you wish to e-mail Joe Bloggs?" Title = "Correct City Test" Response = MsgBox(Msg, vbYesNo + vbQuestion, Title) End If If Response = vbNo Then MsgBox "Go Back and Change to Correct City" Sheets("Report").Activate Range("K6").Select Exit Sub End If If Response = vbYes Then Mail_Joe_Bloggs End If End With- Hide quoted text - - Show quoted text - Thanks Norman for those tips |
| All times are GMT +1. The time now is 10:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com