Excel 2010 :: Data Validation With Custom Function And Message Box Display
Feb 24, 2014
I have four cells c1 thru c4. The SUM of these cells must equal 1. I want to display an error message "sum of cells not equal to 1.0" but only when data has been entered in all four cells.
For instance =SUM(C1:C4, "<>1") should display the error message. Is there a way to write the formula with an IF statement and still use data validation with error message box?
-Will ignore blanks
-Error message "Sum of cells are not equal to 1.0" until values in all four cells have been entered, not after a value is entered in a cell. (validate expression after all four cells have values or blank)
c1 = .25, c2 is blank, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .25, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .30, c3 = .25, c4 = .50 FALSE error message should display after value is entered in c4
What I don't want is for the error message box to display after .25 is entered in c1 because the user is still in the process of adding values. Can this be done using Data Validation in Excel 2010?
I use 2010 and I will try and I need to complete a relatively simple project that I am working on.
I have a range of cells, "D7:Q30". This range has numerical values that are updated automatically (every cell within the range has a numerical value) from an "old value" to a "new value". The refresh rate is not constant, that is the values may change at any time depending on when a value changes I think it is called "pushed" data. Once a value is updated ("new value"), it stays at that value until one of the updates changes its value (and then it becomes the "old value").
I would like a message box to display the following EACH TIME A CELL CHANGES ITS VALUE when the NEW updated value is LESS THAN the OLD value:
"B51, B52, **5, has a changed value from "old value" to "new value".
The ** above in the message box represents the following: If for example cell F23 changes, ** in the message box should be the value that is in cell F5. If Q10 changes, the ** value in the message box should be Q5. It will always be the value in Row5 but have a different column depending on where the change is detected.
B51 is the text value in B51..same for B52 (text) etc...these values change depending on what spreadsheet I have open, but will always be in those cell positions. But the values within the range D7:Q30 are always numeric.
I am trying to use Excel to create some XML output that is formatted specifically for an application that I have. The application expects to receive time data for two columns in hh:mm format, i.e. no seconds. The standard Excel data validation and also the XML time data type all expect hh:mm:ss.
In order to get around this, I have used string as the data type and I want to use data validation to check the user input conforms to hh:mm. I can't see how to write the formula directly, nor how to apply the validation formula to an entire column (as opposed to named cells or ranges), and although I could write a function using regex, I still couldn't use this because all the examples I can find relate to checking a specific named cell.
I have a userform with a combobox (cmbSearch) , a textbox (textbox1) and a listbox (listbox1)....and a commandbutton (cmbFind). This userform is used for searching records in my worksheet....where user selects a "Search By" category from the combobox and then enters relevent text search criteria in the textbox....and all the search results are shown in the listbox. This part works fine untill someone tries to search a "non-existent" record. e.g. a customer name thats not in the database and it gives an error. I would incorporate in my code to have a Message box pop up on these type of searches saying "textbox1.value Not Found!" insetad of error message.
Private Sub cmbFind_Click() Set c = Range("a65536").End(xlUp).Offset(1, 0) 'write userform entries to database Dim DataSH As Worksheet Set DataSH = Sheets("ComplaintData")
With DataSH .Range("L1").Value = cmbSearch.Value .Range("L2").Value = TextBox1.Text .Range("N1"). CurrentRegion.Clear .Range("A1").CurrentRegion. AdvancedFilter Action:=xlFilterCopy, criteriarange:=.Range("L1:L2"), copytorange:=.Range("N1") End With lstSearch.RowSource = vbNullString lstSearch.RowSource = "outdata"
I am using excel 2010. I have 3 separate form control each with its own macro. I would like to create a combo box with a drop down with a list of each form control case and be able to perform the same function as the individual form control when a specific name is chosen in the list of the combo box.
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I have a worksheet that uses the INDIRECT function as part of cell validation to generate a custom 'name' range, this name then references a bunch of sheets that contain the actual range where the values for the validation list are stored. For example: ValidationExample.xlsx
Name = Color Values = Blue, Red, Green
Name = Shape Values = Square, Circle, Triangle
So what this allows me to do is in the first cell, I can define two options such as Color and Shape. Once one of these is selected, the next cell will have a formula for the validation as "=INDIRECT(A1)" then I will define two named ranges called 'Color' and 'Shape'. This will then show me the list of items in the cell based on previous selection. An example of this is attached to this posting above.
When the formula is written into the validation, an error message is generated saying that the formula will generate an error. Even with this message, the method works effectively to provide a blank list if nothing in the first cell is selected, or a list dependent on the selection of the first cell.
In this case, what I want to do is generate a macro that will populate the validation for a cell when new records are added. This won't always copy down from previous cells because the way I add records is through use of a macro and I generally find using the format painter in vba to be a fairly sloppy way of getting formats from other cells. So I go through a series of validation additions to each cell in the worksheet to get this validation created (I have no written this into the workbook attached).
VB: Dim i As Long Dim lastRow As Long Dim sht As worksheet
As I read in previous posts, Formula1 cannot contain an actual formula, only names and lists of items. Since each of the cells will have a changing reference, thus the INDIRECT function would need to change to reflect this, The 'Secondary' name consists of the following:
Which of course does not work due to the error when using INDIRECT in the first place (though the Address() function appropriately finds the cell with the validation in it's appropriate address). I can think of other ways to do this, such as having validation lists change in with VBA on a SelectionChange or Change event, but that's a lot of code that is subject to change when the named ranges have changes to them (which is fairly frequent). Is there any way to get the .Validation.Add method to ignore errors in the name supplied to it.
For reference, my code is this:
VB: Private Sub cmdRefreshValidation_Click() 'Re-enters validation parameters to all cells to allow selections Dim i As Long Dim lastRow As Long Dim sht As Worksheet
I'm using Excel 2010, and I need to restrict the value the user can enter into a cell (E9).
In cell E3 is the screen width (pixels). eg 6024 In cell E5 is the preferred width of a window. eg 450
The user, in cell E9, enters an x coordinate for which they prefer the top left corner of the window whose width is specified in E5.
If the value that the user enters in E9, added to the width entered in E5, exceeds the value of E3, (if E9+E5 > E3) then the value should be disregarded (window will be off right of screen) and the user re-enter.
I'm not familiar with the use of data validation, so I'm uncertain as to how to use it in this circumstance.
Im currently studying for an ICT A level and im in my first year doing AS. Im working the AQA board and i have a project that i have a target for an A! I need to create a system for a business and write a report on everything i did...wont go into detail. Anyway. i am having trouble with an IF FUNCTION!!! I have a cell (E35) that contains a percentage.
I want the IF function to display a message depending on what the percentage in cell E35 shows;
IF, 0>> Display "Bad"
40>> Display "OK"
90>> Display "Very Good"
E35>=100 >>>> Display "Excellent"
I have tried so many things and I was so sure i entered it correctly, but it keeps coming up with an error.
Using Excel 2010, I set up a simple workbook with a spreadsheet on one page and the ranges for data validation on another. I successfully linked the fields in the "Category" and "Tender" columns with the data on the second sheet, making nice drop down menus. Everything works fine on my computer. However, when we open the workbook on a different station, the drop down menus disappear. If you highlight any of the "Category" or "Tender" fields, the little drop down prompt is missing; they behave like regular, un-formatted fields. It appears that the data validation became un-linked.
We tried opening versions with certain fields locked (Mainly the Total, Breakdown, and Summary areas, but the fields with data validation were still editable), and also completely unlocked documents. Every time, the drop down lists were lost when opened on different stations.
I've attached the completely unlocked version : 00_FLEX TRAVEL EXPENSE REPORT_v3.xlsx
Forget to check a box or something? This seems way harder for me to figure out that it should be!
I have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.
What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.
Using: Excel 2010
Code: Sub SEARCH_FUNCTION() Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not FoundCell Is Nothing Then With FoundCell
I am trying to apply custom data validation for two cells. The first is data validation in cell B6. The only valid entries should be in mulitples of 100 (e.g. 100, 200, 300, .... etc). The second cell is B12. The only valid enteries should be any number above 100 and not = 100, 200, 300, .... etc). I tried using following formula in data validation for cell B6 but no luck. Couldn't figure out what to use for cell B12 =mod(B6,100)
In cell 'I6' if I enter the text "Balance" I would like it to display as "Actual" but if any other text is entered I would like it to display the text entered. How to do this using custom cell formatting. (Currently on Excel 2010).
I thought what I wanted would be simpler than I have found. What I am trying to achieve is for any user trying to enter anything other than a "Y" into a cell would get an error message. In a nutshell I have in column A colleague names and from there 7 columns representing the days of the week split by a "spacer" column. This goes on for seven 4 week periods followed by a column that calculates how many "Y"s and into a formula that gives a running percentage.
My problem is that as there are several people that input data, some put multiple "Y"s so making the formulas throw up erroneous figures. That is why I am after a simple solution to stop any entry other than a "Y". I am guessing this would be better done via macro code?
I'm trying to get Excel 2010 to automatically generate a custom timeline in a separate tab using data I enter in a different tab. In the attached example, the data in the first tab ("daily log") tracks the number of trucks parked at a certain yard by the state they are registered in, their company, and their ID number. I would like Excel to automatically generate the timeline I've created by hand in the second tab ("Timeline (desired outcome)"). I need a formula that returns a "1" if several conditions are met (see comments in example) and a " " if those conditions are not met. I played around with IF(AND...) functions, but I've never used them before and couldn't make them work. There are a few complicating factors: there aren't a consistent number of trucks at the yard per day, and some trucks changed their ID numbers during the data period. I use a blank row, highlighted in dark grey, to visually differentiate days. This row can be filtered out with the "null" column.
I have a data validation in a cell that works like this : if a control cell has the value "F" it validates the input with some dates in a custom list, if not it does nothing.
The formula looks like this :
=IF(F2="F",datelist,"") It works fine but I also want it to display the drop down list when cell F2 = F, now all i can do is type a value and it checks if it's ok. Can I do this without using vba ? I will send this to other persons and I don't want the macro warning to pop up when they open it.
I have , within a workbook multiple worksheets which have multiple cells with a certain DATA VALIDATION MESSAGE. I am trying to write a macro to sum up all those cells which have a common DATA VALIDATION MESSAGE.
I currently have 6 cells that Have a Validation Criteria of a "List". I'm using the Input Message on the Data Validation box. I need to have this Input Message Pinned to a specific area on my worksheet. Each time that I exit and reopen the file the Input Message returns to the Area around the dropdown box. The input message is different for each of the 6 cells.
Cells A1 to A10 contain names of different cars, what i would like to do in VBA is when i select any individual cell to the right of these the name of the car appears as a message (as if i had gone DATA -> VALIDATION -> INPUT MESSAGE -> CARS NAME).
Tried positioning a Userform next to the active cell but when i scroll down the page and select a cell the userform is to far down the screen to be seen.
I have an input msg for a cell (in the data validation box). Whenever the cell is selected, it pops up the message box right by the cell, is there any ways we can position the msg box at our discretion? I can drag it down while the cell is selected but even if I save the file and re open and then select the cell, the msg box pops up right by the cell and not the place where i had dragged it earlier.
When I use list validation with name (Data validation List. Inseret name difine) and then trying to write manualy a differnt value (not from the relevant list) I am not being warned like I get when I choose a list . And then even when I have a list or a Name If I copy a diffrent value I am not being warned.
I currently have an excel workbook (2010) in which I created a custom ribbon and assigned several different macros to the buttons.
Long story short, I will be saving several variations of this workbook every so often when I get new data.
The problem is that if I change the name of the file (Save as or Rename), the custom ribbon buttons no longer work. When I assigned the macros to the ribbon buttons, the macros were absolutely assigned.
Is it possible to assign relative macros to custom ribbons?
From research I have done, it appears that custom ribbons cannot be created using VBA. Is this still true?
Below is some code that I found on the internet some while ago. When Excel opens this code runs which brings up a message box prompting a user to select an option. If no option is selected within 15 seconds then the macro runs, else it depends on the selection entered.
The problem that I am experiencing is that sometimes when the user does not select an option the macro does not execute after the coded 15 seconds. The message box just sticks around until a selection is made.
Most of the time it works just fine... some of the time it doesn't. The problem would appear to have gotten worse since upgrading to Excel 2010.
So, my question is either:
1. Why the code does not execute as expected each time Excel is opened? 2. Is there a better / more robust way of executing the below code without using "shell"?