MsbBox with vbYesNo determines whether sub should proceed

[K. Georgiadis]

I'm having a brain freeze with this:

I have the following code attached to a macro button that changes multiple cells to 1:


Sub ChangeScenario_1()
Dim ws As Worksheet
For Each ws In Worksheets
Select Case ws.Name
Case Is = "Scenario Selector"
ws.Range("C5,C7,C9,C11,C13,C15,C17,C19,C21,C23,H5,H7,H9,H11,H13,H15,H17,H19,H21,H23").Value = 1
End Select
Next ws
End Sub


What I have been trying to do (unsuccessfully, as of this writing) is adding a MsgBox with Yes and No buttons displaying the message "You are about to change all cases to Scenario 1. Continue?" as soon as the user clicks the macro button. Clicking "Yes" runs the macro, whereas clicking "No" exits the procedure. This should be very simple but I'm either not nesting the sub procedure correctly or I'm making a fundamental syntax error.

[johnske]

Try this



Sub ChangeScenario_1()
Dim ws As Worksheet
Dim Answer As String
Answer = MsgBox("You are about to change all cases to Scenario 1. Continue?", vbYesNo)
If Answer = vbNo Then Exit Sub
For Each ws In Worksheets
Select Case ws.Name
Case Is = "Scenario Selector"
ws.Range("C5,C7,C9,C11,C13,C15,C17,C19,C21,C23,H5,H7,H9,H11,H13,H15,H17,H19,H21,H23").Value = 1
End Select
Next ws
End Sub

[K. Georgiadis]

Perfect! Thanks!

[johnske]

Not a prob, glad to be able to help :)