3

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
urdearboy
  • 14,439
  • 5
  • 28
  • 58
MissKatherineEmma
  • 43
  • 1
  • 6
  • Consider rewriting that conditional as a `Select Case` block, or at least branch the conditionals with `Else If`; that way you won't need to systematically evaluate all conditions, since only one of them can ever be true at a given time. – Mathieu Guindon Feb 14 '19 at 03:51

1 Answers1

3

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

urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • 1
    I think you also need to put `(` and `)` around the arguments when you use `Msgbox` as a function, i.e., `Ans = MsgBox(message, vbYesNo)`. – mbj Feb 14 '19 at 03:43
  • 1
    You are correct, thank you @mbj! I typed this out right on **Stack** so didn't have VBA syntax error catcher to help – urdearboy Feb 14 '19 at 03:44
  • Note: If you declare `Ans As VbMsgBoxResult` then you get autocompletion when you type the `=` in `If Ans =` – Mathieu Guindon Feb 14 '19 at 03:49
  • Hmm, actually `Ans` isn't even needed, you could just as well go `If MsgBox(...) = vbYes Then...Else...End If` – Mathieu Guindon Feb 14 '19 at 03:56
  • The lessons never end @MathieuGuindon – urdearboy Feb 14 '19 at 03:57
  • Indeed! I'm still learning new stuff, and been writing VB6/VBA for well over 20 years! – Mathieu Guindon Feb 14 '19 at 03:59
  • 1
    Thank you so much for your help! I have virtually no experience in excel but have been 'learning no the job' with an excel file i now have ownership over, so with lots of googling I have managed to get by where i can - after i have exhausted all options I will ask for help! it's great to see how you have re-written it for me and can see how well it works to separate the steps. This is much more workable - thank you ! – MissKatherineEmma Feb 14 '19 at 04:11