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?
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!
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)
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.
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.
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.
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å rad 17" Application.Goto .Range("E17:P17") Exit Sub End If End If...........................
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.
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 "
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.
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).
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
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.
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.
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
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...
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
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.
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..............
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.
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.
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.
I am currently trying to adapt the following code which is in VBA (Sheet1). It currently ensures mandatory cell entry of cell C2 before saving, and if this is not done then it would not save the item into the relevant directory. I would like to adapt this code so that cells B2 to R2 are all mandatory, and if they are not filled in a message box with the cell title (which would be in B1 to R1) would pop up. If the cells are not filled in then it will not save (the same function it carries out for cell c2 currently). The code is attached to a command button, i have tried to make the additional cells mandatory by playing around with the If Trim(.cells(2, 3).value) part of the code with the AND function etc however i have had not had any luck.
I have 4 columns, If column B (Ref No) is filled in, the adjacent columns, C,D and E become mandatory and an input box pops up on screen one after the other for entry into each column.
My problem is that I require a drop down list and not a input box for the final column (status). So i have created a userform with a combobox dropdown. I am having problems connecting the combobox selection to the final column, and for the selected combobox item to go into the right cell like the input box entry currently does.
Attachment 53209test743.xls
I have attached a sheet and also you can see the code below.
Code in Sheet
VB: Public SaveVal1 Public SaveVal2 Public SaveVal3
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
I have a worksheet with running macros which hides & unhide rows based on input of data in yellow highlighted cells (see attached). I'm trying to make all cells mandatory. The below code works but I run into problems when certain cells made mandatory are hidden. For example if I select "Expense" as Type of Expenditure in the attachment cell C10 & C11 are hidden so if its mandatory I won't be able to move forward to next cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Me.Unprotect "dawnwh81" Dim myCell As Range Dim myRange As Range
I have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
