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 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 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 keep getting a "type mismatch error" for this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Len(Range("E18:E34")) = 0 Then
MsgBox "You must complete cell E18 thru E34 before you can save this file!"
Cencel = True
End If
I am trying to set a range of cells to ensure they are completed before the file can be saved. I can get it to work with individual cells, but not when I use multiple cells like in the example above.
This Works fine:
If Len(Range("D5")) = 0 Then
MsgBox "You must enter the Impress Amount before you can save this file!"
Cancel = True
End If
I need to make a dropdown list (I know how to do that) and based on that selection, I need some cells below to automatically complete. How can I do that? Is it possible with or without VBA?
View 5 Replies View RelatedI have a macro file
when I run the macro it activates a sub macro called: Find_Empty_cells_win
when this is run it picks up a empty cell in Column G
When this happens I get a Msgbox prompt and then I click on OK.
After this it continues on to the rest of the code.
How do I completed exit the vba coding right after I click on OK
I am trying to write a macro with a loop that counts the number of occurences of a specific piece of text within a range. I have tried both Do loops and ForEach loops. How do I end the loop? It just keeps wrapping back to the beginning. Excel does not seem to recognize EOF in the way Access or SQL does.
View 9 Replies View RelatedI need something that will exit all macros currently in place. The reason i am asking for this is because i have code that uses Application.Run to run another macro within the current code. If the other macro called on in Application.Run has a msgbox which has an OK/Cancel option, i want the Cancel button to exit all macros, not just the one that is used to access the message box via the Application.Run.
this is the portion fo the code that shows the cancel button and my attempt to use Exit Sub; however, this only exits the current sub, not all subs.
Code:
If TnC4 = vbCancel Then
msgbox "This application has been cancelled.", vbInformation, "CANCELLED APPLICATION"
Application.Run "CLEAR"
Exit Sub
End If
I seem to be spending an increasing amount of time on here these days! I hope this one is quite simple - I want to add a time stamp of when a cell is updated, but I dont know how to do this. I presume that an adjacent cell can have =now(), but need to know how it to run this when a particular cell has been changed.
View 9 Replies View RelatedI am trying to make a "master" sheet where employees total calls for the day are recorded.
Each employee will be marking their calls each day which will be on an excel spreadsheet. i would like to then create a MASTER sheet that links to all the employees specific sheets so when they exit the sheet it updates the master, or it can be a live update if that is possible...
I've set a range as DD14 to G014, then using a For Next Loop asked the program to cycle through each cell in the range. Upon findng a populated cell, it stores several bits of data from that cell (already done) and then move up one cell to record the cell value (time) and again move one cell up to record the date.
Now I did get this to work using a clunky bit of code that used activecell.offset to move the focus up the two columns and a While .... Wend loop - but using a set range and cycling through each cell it does not appear to be possible - or am I wrong?
Set MyRange(1) = Range("D14:ER14")
Set MyRange(2) = Range("D23:GN23")
For MyCounter = 1 To 2
For Each Cell In MyRange(MyCounter)
If Cell.Value = "" Then Goto Skip_Cell
MyCell = Cell.Address
MyPosition = InStr(1, Cell.Value, ",")
If MyPosition = 0 And Len(Cell.Value) >= 3 Then
Tool_Number = Left(Cell.Value, 3).....................
I worte a procedure to protect the worksheet. When we clik on command button it asks the password but if I click on cancel button procedure is exiting.
View 8 Replies View RelatedI have created a userform on VBA which our clients will use. There are a number of different userforms which have different questions to make up the tool. At the end of each field (text box) there is a submit button (command button) which needs to be pressed. When the user presses next to move onto the next userform I need a control in place that ensures all of the 'submit' buttons have been pressed. If not i want something to say, 'you have not pressed all the submit buttons'. At the minute I have a simple reminder message box before they move onto the next screen but i need this control to be tightned.
View 1 Replies View RelatedI have a survey with different groups of Control Toolbox options buttons on it. I want to ensure that each question has an option button selected before the survey can be exited and emailed onwards. The grouped button names are: GroupA, Group1 through to Group6.
View 2 Replies View RelatedI have created a spreadsheet and wanted to originally see if I could save as a separate format (that makes it more like an application than an Excel spreadsheet). Having investigated, I found that full screen mode pretty much does what I want, however you can restore the menus by pressing ESC or selecting restore down. Is there a way of preventing users from exiting full screen mode, I could add a close application button for them to use instead.
View 3 Replies View RelatedI have a large workbook with hundreds of rows of data.
I have sorted the data and ended up with product codes in column A and my data between columns C and V.
I want to do a sumif with a difference if that is possible.
I want to sum all of the data in column C based on the product code in Column A. But I don't want to include any rows of data where Column V is blank.
i.e. If my sumif for a product code covered 3 rows C3:V5
I want to sumif Column C dependent on the product code in column A but I only want to include rows where there is a value in Column V.
So if all of the cells between C3:V3 contain data include count C3.
Moving down to row 4 if this row only has data between C4:M4 don't include C4.
Moving down to row 5 if this row has data C5:V5 include C5 in the sumif.
i.e if there was 100 in C3, 200 in C4 and 300 in C5 the sumif would be 400 because although C4 has a value V4 does not.
I want to roll this out across all of the columns C:V using the same method if possible.
We have a spreadsheet in use at the moment which has various lines of information, each line has a customer name, id, request and various other bits of information which needs looking into.
The infomation is arranged onto weekly sheets and is managed manually. Once the customer request is complete, we grey out the background colour. Some outstanding issues background colours are either left blank, yellow or red, depending on whos doing them.
I was wondering if there is a search criteria that we could use that would search through all the sheets in the workbook and find any customer queries that are not greyed out and display them on the results page.
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.
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 "
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 RelatedI have the following setup on a worksheet :
5427 Data
5427 Data
5427 Data
6565 Data
6565 Data
7765 Data
all I want to do is have the above table look like this :
5427 Data
6565 Data
7765 Data
The concept being that I can double click that row to see the additional information(i.e. expand to show extra information)
Real life use would be of such, the first column contains the invoice number, the 2nd 3rd etc contain item associated with such invoice.In grouping the column invoice number, you only have to look at one invoice, instead of it duplicating for each item contained within the invoice.
This will need to be done in an efficient manner(over 4000 rows need grouped)
I realize I could just create a list that has one copy of the duplicates, but I want to extract the original information as well. For example, it would look like the following :
Original :
1212 Data
1212 Data 2
1212 Data 3
2323 Data 1
2323 Data 2
Edited :
1212
2323
double click 1212 for :
1212
+Data 1
+Data 2
+Data 3
2323
Note : This grouping will also have to span across the whole row, sample rows :
Invoice# Price Color Weight Item
1212 Column1Data Column2Data Column3Data Column4Data etc.
1212 Column1Data Column2Data Column3Data Item2Column4Data etc.
I am trying to come up with a composite score that if a child takes a particular course and has a grade then make a composite score. Here is an example below.
ABCDEFGHIJK1Math CourseScience CourseReading CourseWriting Course
MathScienceReadingWriting Composite Score Looking for2APGEAPAP A+BC-B- 11003
In the example below I am looking for a composite score of 1100. Here is how I derived at it!
=4*100 + 3.2 * 50 + 2.4 * 100 + 3.0 * 100 = 1100
Code:
Function composite(r As Range, course As Range) As String
Dim d As Double
Dim score As Double
[Code]....
What I am trying to do is record the time when a task is completed on the spreadsheet. For explanation sake, lets say I have list of 10 tasks in column C 1-10. In column B 1-10 I have a button for each task. When you press the button for the task, the time it was pressed is recorded in D 1-10. To put the time in I used =now(). The problem that I am having is, when you press button 2-10, it changes the time in the cell above to the same time. So instead of have 10 different times in 1-10, I have 10 identical times,being the last button I pressed. I tried timestamp, time value, etc
View 1 Replies View RelatedI have a little problem and could use some help please.I created a worksheet for logging orders but now find it too large to cut and paste but have little knowledge of macros
I would like to have any completed rows moved to another sheet in the same workbook ...
This seems easy in theory but it's above my head. I have written code to add text and numbers from a userform to certain cells on a worksheet. Now, is it possible to display a message box after the code executes to acknowledge the code has executed successfully? I have tried simply adding the Msgbox function at the end of the code but that does not work.
View 2 Replies View RelatedIn 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.