VBA Mandatory / Required Fields Before Closing

Jul 31, 2013

I am looking to create a file that would force our sales rep (over 20 people in the company) to fill out certain fields in the excel spreadsheet before submitting the data to other teams. Often times the information is missing which makes many people unhappy. I am new to VBA excel and found the code below online but it does not seem to work. My goal is to have sales people fill out cells, C12, C14, C15, C16, B12, B14, B15, B16 (which are merged cells) and J12, J13, J14, J15, J16, K12, K13, K14, K15, K16 (also merged) and J5 before closing. The code below does not work correctly because as soon as the information is entered just in one cell, excel ignores other 'mandatory' but blank cells and allows the user to close the file. E.g. if info was entered in J5 when other cells were left blank, excel allowed me to close the file.

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cells(12, 3).Value = "" And Cells(12, 4).Value = "" And Cells(13, 3).Value = "" And Cells(13, 4).Value = "" And Cells(15, 3).Value = "" And Cells(15, 4).Value = "" And Cells(16, 3).Value = "" And Cells(16, 4).Value = "" And Cells(12, 10).Value = "" And Cells(12, 11).Value = "" And Cells(13, 10).Value = "" And Cells(13, 11).Value = "" And Cells(14, 10).Value = "" And Cells(14, 11).Value = "" And Cells(15, 10).Value = "" And Cells(15, 11).Value = "" And Cells(16, 10).Value = "" And Cells(16, 11).Value = "" And Cells(5, 10).Value = "" Then
MsgBox "Please enter required information (cells highlighted in blue) before saving"
Cancel = True
End If
End Sub

View 7 Replies


ADVERTISEMENT

Mandatory Fields

Sep 28, 2007

i have created a form hich dumps into a database.

There are some mandatory fields which need to be filled in by the user on the form - how can i flag these up at the end of the form - with a message box which says - "cant complete - feild "" "" "" are missing "

View 9 Replies View Related

Mandatory And Interdependent Fields?

Aug 21, 2014

I have a form (Excel, of course) which needs to be filled following some criteria. I have a filed name "Clause of non-compete" and you have to choose between YES and NO. This field has to be mandatory. I did that with the following code (see below). Now i have another field that says: If YES, should it be maintained? How can i make the second field to be dependant on the first one and mandatory? I want it to be mandatory only when there is Yes in the first field (merged field N35:O35).

[Code]....

View 3 Replies View Related

Adding Best 7 Of 10 Values With 4 Mandatory Fields

Aug 25, 2014

I need to create a formula to calculate rankings for a race series. there are 10 events in the series, only the best 7 individual results count. and there are 4 events which are mandatory and must be included in the rankings.

Sample attached. Sample rankings.xlsx‎

View 1 Replies View Related

How To Setup Mandatory Fields And Popup Box Upon Close

Dec 18, 2013

I am trying to save an excel file with the following features set up:

1. the user of the file should fill the mandatory fields (possibly highlighted in green) without fail

2. upon saving, the file should only save if all the mandatory fields are keyed / filled in.

in case of empty cells left as per the above, there should be pop-up messages asking to fill the details.

here is what I tried. however, i cant seem to get the pop-up messages. I guess I am missing out on giving some commands that make it mandatory to fill.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("Sheet1").Range("G11") = "" Then
Cancel = True
MsgBox "Client has not been filled in"
End If
If Sheets("Sheet1").Range("g12") = "" Then

[code]....

View 5 Replies View Related

Creating Formula - Adding Values (best 7 Of 10) With 4 Mandatory Fields

Jun 22, 2014

i need to create a formula to calculate rankings for a race series. there are 10 events in the series, only the best 7 individual results count. and there are 4 events which are mandatory and must be included in the rankings. sample attached.

View 3 Replies View Related

Forcing Required Fields

Mar 16, 2009

I have created a scorecard and I have certain fields which i need completed prior to the coach submitting the scorecard.

Is there a way which i can prompt the coach if they miss a field

The fields i want completed are:

X17, Y17, Z17, AA17, AB17, AC17, AD17 and AF17

View 9 Replies View Related

Required Fields For Emailing Spreadsheets

Sep 8, 2004

I'm trying to setup a template for people at work to use, where they would fill in fields i setup (Account number, customer name etc). They would then hit a macro that would email the spreadsheet to a certain group of people.

My problem is i don't want them to be able to use the macro unless all the fields have been filled in with a value. I would want all fields to at least equal something, either a number or a word, is there a way to do this?

View 9 Replies View Related

Required Fields In User Form

Feb 15, 2010

I have few text boxes and few combo boxes in one of my user forms. I have few text boxes and few combo boxes are set to be Required fields that means a user must enter a value in those fields. Please keep into considerations that a user can click on each fields or simply 'tab; thru them. If a user try to skip to next field then I want excel to show a warning message box prompting user to enter a data since this is a required field.

View 9 Replies View Related

Closing Workbook When Closing Userform

Sep 14, 2006

I have a userform which opens when the workbook opens. Ideally Id like to have the userform open without the workbook coming up and have the workbook close when you close the userform. If thats not doable then is there a way to just close the workbook when the userform is closed?

View 3 Replies View Related

Mandatory Field Set Up

Aug 2, 2007

I have a spreadsheet which has several fields containing data and
what i want to achieve is that when someone comes to close the
spreadsheet , If they have not filled in all the fields required it will
put up a notice to say that field **** must be completed.

View 9 Replies View Related

Mandatory Cells

Jul 25, 2008

I have a worksheet which will be sent out of our organisation to a supplier for them to complete, save and return to me. I have a number of cells in the worksheet which I want to be mandatory. ie if no information is entered in the cell they will get a warning when they attempt to save the file down but will be unable to save it without filling in the cell. I was also going to colour the cells to identify them as mandatory. The cells are in random positions on the worksheet and different worksheets sent to different suppliers may have a differing number of coloured cells.

Is there a way to have one macro which finds the coloured cells (red), checks that there is an entry in each cell . If it is, allows a save. If it is not populated, displays a warning and stops them saving the worksheet until it is populated and is useable for all worksheets regardless of how many red cells are on the sheet and the position of them? If so how would it look?

View 9 Replies View Related

Mandatory Combo Box

Apr 9, 2007

I have two combo boxes (created using the control toolbox), and I would like to have the second combo box (pricing analyst approval) be a mandatory field if the combo box value above it (Loss Type) is V-VII only. I would like to have some sort of promt appear that indicates that the Pricing Analyst combo box is mandatory.

View 9 Replies View Related

Making Cell Mandatory

Aug 21, 2012

In Column B of my spreadsheet i have a drop down that only allows users to enter "Yes" or "No"

Is it possible to make Column C a required field if "No" is selected in column B.

So basically if cell b4 is "No" then i want to make it manditory that cell C4 is filled out with a reason why.

Same thing for cell b5 then i would like cell c5 filled out before they continue using the sheet. etc.

View 1 Replies View Related

How To Make A Column Mandatory

May 4, 2012

how can I make a column mandatory?

basically if someone puts data in column C, they have to put data in column D

(people are not putting important information on my spreadsheet, so i would like to make that column mandatory)

View 2 Replies View Related

How To Create Mandatory Column

Nov 26, 2012

i have an excel sheet ,i need every one use this sheet has to fill the spceific cell in one coloum and he couldn't save his editing without fill the requeired cell by pop up message let him know that he has to fill this cell and high light the cell for the user at the same time

View 2 Replies View Related

Mandatory Cells And Filling In Next Row

Jul 21, 2014

I am creating an excel file for my company. This file contains 18 headers (from Columns A to R), all of which are mandatory to be completed.

This file is used by several other colleagues with the intention to update our records almost on a daily basis, and saved by them daily before being checked by me.

I would like to know if there is a macro to be used in order to prevent them from saving the file if all the fields are not completed. (ie. A5 to R5)

This function should only run right up to the next blank row.

Eventually, I would like the users to complete all cells from A5 to R5.

Should a single cell not be completed, an error message will pop-up.

When the next person accesses the file to enter their data underneath, it will again, not allow them to save until all the mandatory fields are filled out.

I have looked at another very similar link but it does not work and I do not know how to tweak it.

The example given is for when the data is in Column A.

The headers on my file are on Row 1 (Column A to R).

Mandatory Cells and knowing to fill in the next row!

View 5 Replies View Related

Mandatory Userform Textbox

May 8, 2007

I'm coding a userform with many textboxes, one of which needs to be mandatory. How do I make it such?

View 2 Replies View Related

Highlight Non Used Mandatory Cells

Jun 6, 2007

I have made a spreadsheets wherein all the cells where data are to be entered are coloured yellow.Some cells are mandatory (coloured Green)wherein user have to fill data compulsorily & some are optional (Coloured grey) Since the file is big and sheets are arond 28, I want to attach a buuton which will be assigned a macro to do the validation work.

The macro will search all yellow cells & green cells and if found any of those cell blank will change those colour to red and shall also show a list where the cells are merked red (List will be in a pop up manner, printable, and shall show the name of the sheets & cell address)

View 6 Replies View Related

Mandatory TextBox Field - UserForm

Sep 2, 2009

I am using Excel 2003. I need to make a mandatory field for TextBox1 on UserForm2. So if the user leaves TextBox1 "blank", then they are forced to enter in data into TextBox1.

View 2 Replies View Related

Mandatory Fill For Cells In Excel?

Nov 25, 2013

My requirement is something like this. I have a workbook where end-users can fill data and send it to our system. They might enter any rows of data, it can be just 1 or even 100. I want to force users to fill in values in few mandatory fields : columns in B,C,D,E. Now I need a VBA code that should throw a message when it finds that the value in cells B,C,D,E are blank.

Since I am not sure how many rows of data the user can fill, I dont know how to code in VBA the above scenario. I cant give a range as the total rows of input data is not available and static value.

View 3 Replies View Related

VB To Have Mandatory Cells Completed Before Exiting

Aug 27, 2007

I need to be able to have users complete a range of mandatory cells before they are able to close a workbook.

Foe example cells c5,c10,i11, i18,i22 i24,i26 etc etc need to have data in them before uses is allowed to save or close the workbook. I would like to have a message box prompt to alert them to the issue.

View 9 Replies View Related

Mandatory Cells Based On Criteria

Dec 17, 2007

I need a macro that will check that the cells B1,C1,D1,E1 and one of the cells F1,G1,H1,I1 are filed out upon entry in A1.

If Not IsEmpty(.Range("A1")) Then
If WorksheetFunction. CountA(.Range("B1,C1,D1,E1")) <> 4 Then
MsgBox "All cells in row 1 are not filled out"
Application.Goto .Range("A1:I1")
Exit Sub

But how do I get it to also check that either F1 or G1 or H1 or I1 is also filled out. If possible I also would like it to beimpossible to add text in more then one of the cells F1:I1. Summary, upon entry in A1 I want cells B1:E1 plus one of the cells in F1:I1 to be mandatory, so in total there will be 5 mandaory cells.

View 2 Replies View Related

Detrmine Mandatory Cells Not Filled

Jan 4, 2008

I want the macro to check the rows 17 to 1000 if there is a value in column E on respective row. I have this, it works fine but I have to cupy it approx 1000times, that is for every row and then change the row 17 to 18 and 19 ...1000, There must be an easier way?

Sub Knapp174_Klicka()
On Error Resume Next
With Blad1 ' CodeName
If Not IsEmpty(. Range("E17")) Then
If WorksheetFunction. CountA(.Range("E17,J17,P17")) <> 3 Then
MsgBox "Du har inte fyllt alla celler p&aring; rad 17"
Application.Goto .Range("E17:P17")
Exit Sub
End If
End If...........................

View 4 Replies View Related

Message To Remind That Cell Is Mandatory

Apr 23, 2008

We have spreadsheets which record our sales team's prospects and orders. In order for the sales director's summary spreadsheet to be correct, the month which the order came in has to be filled in and correct.

What I would like to be able to do is when column K of a line is turned to "100 - Purchase Order In", a message to pop up saying something along the lines of "Is the Month In correct?"

Looking through previous answers, I think that this is something along the lines of what I need:

Sub Print_Out()

With Range("d6")
If .Value = "" Then
.Select
MsgBox ("Make sure you enter your surname")
Exit Sub
End If
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

However, I don't know enough about VB Code to know how to change it for my purposes...

View 9 Replies View Related

Convert Column Data Fields To Row Data Fields In Pivot Table

Feb 8, 2014

CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83

[Code]....

left side pivot created in vb 6.0 & right side pivot table created manually in excel.

i want to generated pivot table using vb 6.0 same as right side pivot.

Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),

[Code]....

View 2 Replies View Related

Make Cell Mandatory Based On Criteria

Dec 28, 2006

I trying to require data entry in Cell 2 if Cell 1 contains text. Normally Cell 1 is blank but once the user enters data (its a text field) I want Cell 2 to then become mandatory for data entry. (Cell 1 is actually C5 and Cell 2 is actually DK5. So formula I am thinking if is: If C5 contains data then DK5 must also contain data. If C5 is blank, the DK5 is also blank.

View 4 Replies View Related

Check Mandatory Controls On MultiPage UserForm

Dec 29, 2006

I have successfully created validation code that operates as a command button. The code listed below identifies in a MultiPage UserForm non- null TextBoxes whose pair of associated OptionButtons have not been selected. (The TextBox and OptionButtons all lie within a Frame and their are 60+ sets of these throughout the UserForm.) A MessageBox alerts user to select one of the OptionButtons.

Private Sub CommandButton1_Click()
Dim cFrameT As Control
Dim cCtrlF() As Control
Dim pPage As Page
Dim cCtrl() As Control
Dim Num As Integer
Dim FNum As Integer
Dim vOpt As Boolean
Dim Opt() As String
Dim lPage As Long
Dim vFrame As String
Redim Opt(Num) As String
Redim cCtrl(FNum) As Control
Redim cCtrlF(Num) As Control

Num = 0
FNum = 0
' Loops through all Multipage pages
For lPage = 0 To Me.MultiPage1.Pages.Count - 1..............

View 2 Replies View Related

Highlight Mandatory Empty TextBox Controls

Jun 6, 2008

I have the below code that I am using to find an empty textbox with the name FirstName. All it does is place the cursor in the FirstName text box if the textbox is empty, but doesn't really make it stand out. I was hoping to find a way to color the background of the textbox pink, or have it set up so it would stand out somehow.

View 4 Replies View Related

Excel 2010 :: Creating Mandatory Field Before Saving A File?

Aug 22, 2013

coding mandatory field in Excel 2010.

This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it

Sub NextInvoice()
Range("J2").Value = Range("J2").Value + 1
Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy Invoice to a new workbook

[code].....

I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved