How to correctly add "yes / no" into a VBA message box with existing string

Question

I have a macro that runs based on a drop down list. There are three options in the drop down list. I have created a custom warning message for each drop which works well. I wanted to add a "YES" and "NO" button selection onto this code, but i can't seem to get it to work correctly.

I can Only seem to do either or. The same warning message for each selection but with "yes" and "no", or custom messages for each selection, but only an "OK" option, no "YES and "NO" button selections.

Sub CopyRanges()

Dim message As String

If Sheets("Data").Range("D27") = "6-18" Then
    message = "You are about to change the size range, are you sure?"
Msgbox message
End If

If Sheets("Data").Range("D27") = "XS/S-L/XL" Then
    message = "You are about to change the size range to DUAL size, some POM's will not be available using the DUAL size range. Are you sure you wish to proceed?"
Msgbox message
End If

If Sheets("Data").Range("D27") = "XXS-XXL" Then
    message = "This size range is only for Fully Fashionesd Knitwear. Cut and sew styles please use the size 6-18 size range. Are you sure you wish to proceed?"
Msgbox message
End If

Answer 1

You can add options to your Msgbox (full list provided here).

Via the link provided above, the full syntax for Msgbox is:

MsgBox (prompt, [ buttons, ] [ title, ] [ helpfile, context ])

---

You want to access the buttons option. In practice it looks something like this:

Dim Ans 'Answer
Ans = Msgbox (message, vbYesNo)

If Ans = vbYes Then
    'Do what if yes
Else
    'Do what if no
End If

---

Also, Select Case works nicely here

Sub CopyRanges()

Dim message1 As String: message1 = "You are about to change the size range, are you sure?"
Dim message2 As String: message2 = "You are about to change the size range to DUAL size, some POM's will not be available using the DUAL size range. Are you sure you wish to proceed?"
Dim message3 As String: message3 = "This size range is only for Fully Fashionesd Knitwear. Cut and sew styles please use the size 6-18 size range. Are you sure you wish to proceed?"
Dim Ans as VbMsgBoxResult

Select Case Sheets("Data").Range("D27")
    Case "6-18"
        Ans = MsgBox(message1, vbYesNo)
            If Ans = vbYes Then
                'What if yes?
            Else
                'What if no?
            End If

    Case "XS/S-L/XL"
        Ans = MsgBox(message2, vbYesNo)
            If Ans = vbYes Then
                'What if yes?
            Else
                'What if no?
            End If

    Case "XXS-XXL"
        Ans = MsgBox(message3, vbYesNo)
            If Ans = vbYes Then
                'What if yes?
            Else
                'What if no?
            End If

End Select

End Sub

Lastly, if your 3 yes statements result in 3 inherently different tasks being completed, you may consider creating 3 subs that handle different tasks. Then, you can simply call the appropriate sub under each case. It will keep this code clean and I always encourage separating procedures to allow for specialized macros rather the one-does-all approach