| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| replace VBA run-time error message with custom message | Excel Programming | |||
| Replace Excel Message w/Custom Message | Excel Programming | |||
| Intercept/replace standard 'cell protected' message with my own message? | Excel Programming | |||
| Opening an attachment of a message that has a message as attachmen | Excel Programming | |||
| Displaying a message in a message box without requiring user to click anything to proceed | Excel Programming | |||
Linear Mode
