title: Excel VBA - Make multiple fields required based on value of another field
body: <p>I've created a request form in Excel with VB which uses a submit button to generate an email in outlook based on the values entered into the form.</p>
  
  <p>Everything works fine. However, users often fail to complete all the necessary fields before submitting the request. </p>
  
  <p>I need to be sure the user completes all the required fields when a specific value is entered into cell D7 before they submit the request</p>
  
  <p>Here is where I get lost...I've tried approaching it two slightly different ways. </p>
  
  <p>Hopefully someone can help me with this!</p>
  
  <p>Approach 1:</p>
  
  <p>When the submit button is pressed...</p>
  
  <pre><code>Button_click()
  
  If Range("D7").Value = "Special Request" THEN
  'Make cells B6, B7, B8, B9, D14 mandatory in order to generate email 
  
      On Error Resume Next
      If ThisWorkbook.Worksheets("Request").Range _
      ("B6, B7, B8, B9, D14 ") Is Nothing Then
  
      MsgBox ("Please confirm all required fields have been completed!")
  
  'Do not generate email
  </code></pre>
  
  <hr>
  
  <p>Approach 2:</p>
  
  <p>When the submit button is pressed...</p>
  
  <pre><code>Button_click()
  
  
  'If the value of cell D7 is ANYTHING OTHER THAN "Special Feature", 
  'execute code as normal to generate email
  
  'Else, check for empty fields in the required cells ("B6, B7, B8, B9, D14 ") 
  
  'if any required cells are empty, display message and do not generate email    
  MsgBox ("Please confirm all required fields have been completed!")   
  
  'If D7 = "Special Feature" AND NO REQUIRED FIELDS ARE MISSING, 
  'continue with executing code to     generate email
  </code></pre>