VBYesNo MsgBox - Computer always says "Yes"

[Peter Rooney]

Good afternoon, all!

Apologies for those non UK residents who didn't get the "Little Britain"
joke...

Can anyone see the deliberate error with this code - no matter what button I
click, it always processes the vbYes option. Response returns "7" when i
click "No" and "6" when I click "Yes".

All help gratefuly received!

Sub ConfirmIssuesDelete()
Dim Prompt, Buttons, Title, Help, Ctxt, Response, MyString

Prompt = "Do you REALLY want to delete this row?" ' Message.
Buttons = vbYesNo + vbCritical + vbDefaultButton1 ' Buttons.
Title = "Whoa!" ' Title.
Help = "D:\DEMO.TXT" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Prompt, Buttons, Title, Help, Ctxt)
MsgBox (Response)
If Response = vbYes Then
WhereWasI = (Selection.Address)
Selection.EntireRow.Delete
IssuesDataRangeFormat
Range(WhereWasI).Select
Else
Exit Sub
End If
End Sub

 

[Ron Coderre]

Try changing your code to this:

Sub ConfirmIssuesDelete()
Dim Prompt, Buttons, Title, Help, Ctxt, Response, MyString

Prompt = "Do you REALLY want to delete this row?" ' Message.
Buttons = vbYesNo + vbCritical + vbDefaultButton1 ' Buttons.
Title = "Whoa!" ' Title.
Help = "D:\DEMO.TXT" ' Define Help file.
Ctxt = 1000 ' Define topic

If MsgBox(Prompt, Buttons, Title, Help, Ctxt) = vbYes Then
WhereWasI = (Selection.Address)
Selection.EntireRow.Delete
IssuesDataRangeFormat
Range(WhereWasI).Select
Else
Exit Sub
End If


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Peter Rooney]

Ron,

Thanks, but for some bizarre reason, it disn't - it still deleted the row
even when I clicked "No"
I also realise that I should have posted this in the developers' forum, so
special thanks for responding!

Cheers

Pete

 

[Ron Coderre]

I'm not sure why that is happening....it works fine when I run it.

I'm guessing that some other code is causing the problem.

Here's a simple way to find out:

If MsgBox(Prompt, Buttons, Title, Help, Ctxt) = vbYes Then
MsgBox "You clicked yes"
' WhereWasI = (Selection.Address)
' Selection.EntireRow.Delete
' IssuesDataRangeFormat
' Range(WhereWasI).Select
Else
MsgBox "You clicked no"
Exit Sub
End If

Does that help?
***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Peter Rooney]

Ron,

Doh! You were right - it doesn't help if the celling macro deletes the row
anyway...

Sorry to waste your time - have a great Christmas!

Pete