Multiple VBA Macros Issue

blrhode

New Member
Joined
Nov 7, 2019
Messages
12
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:
VBA Code:
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!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Let's start with this:

VBA Code:
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

New Member
Joined
Nov 7, 2019
Messages
12
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!
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Nov 7, 2019
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Nov 7, 2019
Messages
12
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,581
Messages
5,548,874
Members
410,881
Latest member
toonces
Top