Message Box Q

[Sean]

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

 

[papou]

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

 

[Norman Jones]

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

 

[Sean]

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" <[email protected]> a écrit dans le message de (e-mail address removed)...











- Show quoted text -

Thanks Pascal, thats exactly what I want

 

[Sean]

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


with

Sheets("Report").Activate
.Range("K6").Select

---
Regards,
Norman













- Show quoted text -

Thanks Norman for those tips