VBA - Msg Box to ask Yes/No question

[simi_uk]

Hi all,

Back again i'm afraid with more help needed. I've got a Macro that, when activated, allows the user to cancel a demand and highlight that row red when completed, here's the code:

Sub Cancel_DMD()
Dim datatoFind, sRemark As Variant
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
Dim searchRange As Variant
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Demand Number To Cancel {(}Ensure correct Demand No. entered{)}:")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
'defines the search to column A of the active sheet
Set searchRange = ActiveSheet.Columns(1).Find(What:=datatoFind, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:=False)
If Not searchRange Is Nothing Then 'test to see if the search is successful or not
Worksheets("demands").Unprotect Password:="JSmith"
askRemark:
sRemark = InputBox("Reason for cancellation")
If sRemark = "" Then GoTo askRemark 'returns to ask reason for cancelling if left empty
Range(searchRange.Address).EntireRow.Interior.ColorIndex = 3
Cells(searchRange.Row, "N").Value = sRemark
Worksheets("demands").Protect Password:="JSmith"
Exit Sub 'quits when a match is found
End If
Next counter
Sheets(currentSheet).Activate
Worksheets("demands").Protect Password:="JSmith"
If searchRange Is Nothing Then msgbox ("Demand not found") 'searchRange is empty if Demand number was not found
End Sub

I also need to insert a MsgBox that pops up before exiting. The MsgBox needs to be in two stages, the first will ask "Have you contacted the Demands Clerk to cancel?" with "Yes" or "No" buttons. If "Yes" is clicked, then the macro completes and exits, if "No" is clicked, then the sencond stage will appear. In the 2nd box, it would say "Please contact Demands Clk on Ext. ####" with an "OK" button to click and close the macro.

this is probably pretty easy for you guys 'in the know' but i'm just stumped as a VBA n00b!

Thanks In Advance

 

[jbeaucaire]

Personally I find comments uniquely unfriendly, so I don't use them. A person should be able to read data without scrolling a mouse and hovering over cells. Ugh.

So I don't have a lot of VBA suggestions regarding comments. Sorry.

 

[simi_uk]

no probelem JB, thanks for your help, its been really great..i'm on the right trakcs now at least....

hopefully there's somebody else here with experience of comments who can jump in and carry the torch to the finish??