Multiple VBA Macros Issue

[blrhode]

Hello, I'm hoping that someone can help me. I'm relatively new to VBA and I have a spreadsheet that I'm trying to create multiple macros within. I've been searching for terms, phrases, etc and have come up empty thus far.

The first issue is, this form will be used by multiple people and needs to be able to be saved with the cells empty once I'm done entering all of the required cells macros.

The second portion, "Cells must be filled before save" I've figured out by using the following:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Application.Sheets("RFQ").Range("D5").Value = "" Then
    Cancel = True
    Response = MsgBox("Please enter a value in D5", vbCritical, "Error!")
    Else
    If Application.Sheets("RFQ").Range("H5").Value = "" Then
    Cancel = True
    Response = MsgBox("Please enter a value in H5", vbCritical, "Error!")
    Else
    If Application.Sheets("RFQ").Range("C31").Value = "" Then
    Cancel = True
    Response = MsgBox("Please enter a value in C31", vbCritical, "Error!")
    Else
    If Application.Sheets("RFQ").Range("C32").Value = "" Then
    Cancel = True
    Response = MsgBox("Please enter a value in C32", vbCritical, "Error!")
    Else
    If Application.Sheets("RFQ").Range("C33").Value = "" Then
    Cancel = True
    Response = MsgBox("Please enter a value in C33", vbCritical, "Error!")
    Else
    If Application.Sheets("RFQ").Range("C35").Value = "" Then
    Cancel = True
    Response = MsgBox("Please enter a value in C35", vbCritical, "Error!")
    Else
    If Application.Sheets("RFQ").Range("C36").Value = "" Then
    Cancel = True
    Response = MsgBox("Please enter a value in C36", vbCritical, "Error!")
    Else
    If Application.Sheets("RFQ").Range("F31").Value = "" Then
    Cancel = True
    Response = MsgBox("Please enter a value in F31", vbCritical, "Error!")
    Else
    If Application.Sheets("RFQ").Range("F32").Value = "" Then
    Cancel = True
    Response = MsgBox("Please enter a value in F32", vbCritical, "Error!")
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub

The third issue is I need another "BeforeSave" if cell C15 isn't filled in then C16 needs to be filled before save.

The fourth issue I have is there are multiple dropdowns within the form that need options chosen prior to saving. Example: C8 is a dropdown, the user needs to select one prior to saving.

The final issue that I have is there are multiple checkboxes Yes/No, one or the other needs to be checked. Example: D48 has a form-fill of Checkbox "Yes" and Checkbox "No", one of them needs to be checked before saving.

The first and obvious question is, can these even be done outside of the one I've already figured out? The second question is, does anyone have any guidance or video recommendations that could walk me through this?

Thank you in advance!

 

[DanteAmor]

Let's start with this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Sub test()
  Dim aCells As Variant, c As Variant
  
  'Validate cells
  aCells = Array("D5", "H5", "C31", "C32", "C33", "C35", "C36", "F31", "F32")
  For Each c In aCells
    If Range(c).Value = "" Then
      MsgBox "Please enter a value in " & c, vbCritical, "Error!"
      Cancel = True
      Exit Sub
    End If
  Next
  
  'Validate C15
  If Range("C15").Value = "" Then
    If Range("C16").Value = "" Then
      MsgBox "Please enter a value in " & "C16", vbCritical, "Error!"
      Cancel = True
      Exit Sub
    End If
  End If
  
  'Validate dropdowns
  aCells = Array("C8", "C9")
  For Each c In aCells
    If Range(c).Value = "" Then
      MsgBox "Please enter a value in " & c, vbCritical, "Error!"
      Cancel = True
      Exit Sub
    End If
  Next
End Sub

IN THISWORKBOOK
Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will openthe Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Save the workbook as a macro-enabled file, close it and then re-open it.

 

[blrhode]

Hi there,

Thank you so much for your help! The code seems to be working, however, I'm not able to save this and close out of it to save it as the template. Is there something else I need to be doing?

Thank you!

 

[DanteAmor]

I'm not able to save this and close out of it to save it as the template. Is there something else I need to be doing?

You cannot save as a template. Which of the error messages do you get?

By the way, on which sheet should the validations apply. As it is, validate on the active sheet

 

[blrhode]

How can I have this for all of my team to use then with the macro's active?

It should for now apply to the first sheet. Thanks!

 

[DanteAmor]

How can I have this for all of my team to use then with the macro's active?

IN THISWORKBOOK
Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will openthe Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Save the workbook as a macro-enabled file, close it and then re-open it.

It should for now apply to the first sheet. Thanks!

Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

  Dim aCells As Variant, c As Variant

Wtih Sheets(1)  
  'Validate cells
  aCells = Array("D5", "H5", "C31", "C32", "C33", "C35", "C36", "F31", "F32")
  For Each c In aCells
    If .Range(c).Value = "" Then
      MsgBox "Please enter a value in " & c, vbCritical, "Error!"
      Cancel = True
      Exit Sub
    End If
  Next
  
  'Validate C15
  If .Range("C15").Value = "" Then
    If .Range("C16").Value = "" Then
      MsgBox "Please enter a value in " & "C16", vbCritical, "Error!"
      Cancel = True
      Exit Sub
    End If
  End If
  
  'Validate dropdowns
  aCells = Array("C8", "C9")
  For Each c In aCells
    If .Range(c).Value = "" Then
      MsgBox "Please enter a value in " & c, vbCritical, "Error!"
      Cancel = True
      Exit Sub
    End If
  Next
End With
End Sub

 

[blrhode]

Thank you! This seems to be working! Is there anything you know about checkboxes that may help me to make sure one or the other is checked? You've been such a huge help!! Thank you!

 

[DanteAmor]

Is there anything you know about checkboxes that may help me to make sure one or the other is checked?

You can comment in more detail about your checkbox, is it a form or an activex control, what is it called, is it linked to a cell, to which cell?