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]

Why use two popups? Just use one popup that both reminds the user to make the contact and provides the contact info. Just add this near the bottom of the macro:

MsgBox "To complete the cancel, please contact DEMANDS CLERK on Ext. ####"

 

[simi_uk]

Why use two popups? Just use one popup that both reminds the user to make the contact and provides the contact info. Just add this near the bottom of the macro:

MsgBox "To complete the cancel, please contact DEMANDS CLERK on Ext. ####"

Hi jbeaucaire,

thanks for this..but i cant seem to get it to show the msgbox...i put it near the end as you state, but don't get the pop-up after clicking OK to submit the cancellation.

The code is as above, and i've put you're addition above the 'EndSub' on a new line under the "If SearchRange..." line, is there something i'm missing?

 

[jbeaucaire]

Not unless something above it is causing the macro to end early. That line of code is pretty basic.

Perhaps saving/closing your sheet, restarting Excel will wake it back up.

 

[simi_uk]

Not unless something above it is causing the macro to end early. That line of code is pretty basic.

Perhaps saving/closing your sheet, restarting Excel will wake it back up.

HI, thanks for getting back.

i've quit and restarted without any difference. I've not modified any of the code other than add that line. Is there anything in it (i'm still using the code shown above in my original post) that would cause it to end early or not process the MsgBox line?

Simi

 

[jbeaucaire]

Of course. You have "Exit Sub" in several places in the code.

You can insert those message boxes further up above the "Exit Sub" references in the places where it would be appropriate based on quitting at that point.

 

[simi_uk]

Of course. You have "Exit Sub" in several places in the code.

You can insert those message boxes further up above the "Exit Sub" references in the places where it would be appropriate based on quitting at that point.

Excellent, thats done it..thanks massively jb, you really are a saviour!

 

[simi_uk]

thanks again JB, but i've got a further question,

one last thought i had about this was recording a date and user name of the person cancelling. Would it be too difficult to, using the above code, ask for input of a user's name and then insert this, along with a datestamp, into a comment or empty cell on the sheet within the same row?

 

[jbeaucaire]

Dim uName as String

uName = sRemark = InputBox("Please enter your name")
Cells(SearchRange.Row, "AA") = uName
Cells(SearchRange.Row, "AB") = Date

...added into your macro wherever you want it to occur.

 

[simi_uk]

Dim uName as String
 
uName = sRemark = InputBox("Please enter your name")
Cells(SearchRange.Row, "AA") = uName
Cells(SearchRange.Row, "AB") = Date

...added into your macro wherever you want it to occur.

that works well JB, but i wonder now if there's a way to have both items entered into a comment.

i tried this...

dim uname as string
 
uname = sRemark = InputBox("Please enter your name")
Cells(searchRange.Row, "N").AddComment = uname
Cells(searchRange.Row, "N").AddComment = Date

but get a runtime error highlighting the "Cells(searchRange.Row, "N").AddComment = uname" line, error is as follows:

run-time error '438':
object doesn't support this property or method

probably something i'm doing wrong, any clues?