New to VBA - VBA Before Save & Before Email

Hi Everyone,

I hope someone is able to help me and apologies if i don't explain this very well but i'm in the process of creating a user interactive form on Excel. We used to have this form on a word document but as a few fields used to be left blank i'd like to make it so that the form cannot be submitted (i.e beforesave or beforeemail) unless the information is completely filled in.

Any field that has a red * is deemed to be mandatory and must be completed.

There are also 2 types of combobox on the form, 1 for Cost Centre & the other for GL. Is it possible to make the Cost Centre and GL mandatory for completion only if the rest of that row has cells filled in? So, ComoBox1 & ComboBox6 are only mandatory if cells B45 - F45 are filled?

Thanks in advance!

Hi giggles2005 and welcome to ExcelForum,

Notes:
a. Link ComboBoxes to Underyling cells
b. Description of Product MUST be Mandatory - otherwise how do you know what is being purchased.
c. Removed TextBoxes in Column E (VAT).
d. Option Explicit
e. Added check for all BLANK Data Lines.

It is highly recommended that you avoid 'ActiveX' Controls (ComboBoxes, TextBoxes, CommandButtons) and use 'Forms' Controls instead. 'ActiveX' does not play well with others, and can cause Controls to move or worse.

Try the following code which is included in the modified copy of your sample workbook (attached):

In the Code Module For Sheet 'PO Request Form':

Please Login or Register  to view this content.

In Ordinary Code Module ModVerifyInputs:

Please Login or Register  to view this content.

To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

Lewis

Hi Lewis,

Many thanks for your help with this. Its just what I needed!

Just a quick question though - once all the fields have been completed correctly and emailed through, when I click to download the completed form, none of the information that was inputted is pulling through? How do I correct this?

Any help you could give would be incredible.

Thank you

Hi,

The workbook was blank because the file was never saved. I thought you were going to do that manually. You had this code in the file (which I did not touch) that prevented the file from being saved unless VBA was disabled (Application.EnableEvents = False).

Please Login or Register  to view this content.

I updated the file, to change the code so the file can be saved when all the fields are filled in. Changes in red:
In the ThisWorkbook Code Module:

Please Login or Register  to view this content.

In the Sheet PO Request Form Code Module:

Please Login or Register  to view this content.

The Ordinary Code module that verified inputs had no change.

Lewis