Clear Data In Cells With Validation
Jul 8, 2008
I have several cells on a worksheet which contain data validation and force the user to select from a list, and several other cells with free user input. I want to use a macro to clear all data, and return the cells containing data validation to blank. (I have a blnak row at the top of all my lists, so there is a selection for "blank".)
I have no problem clearing the cells requiring user input, but my recorded macro would not select the cells with validation and return them to blank.
The first cell containing validation that I need to clear is M39. I have my code listed below for clearing the other cells. If I could get a clue on how to return M39 to blank, I could figure out the rest of the cells.
View 13 Replies
ADVERTISEMENT
Mar 10, 2014
In Column C, I have a variable range (usually like, C5:C100000) that has data validation in it. However, only about 10% of the cells have a value from that data validation list in them (which is what I want). I'd like to write a macro that only cleared out the cells with no value in them, so that they no longer had any data validation in them. Is this possible?
View 2 Replies
View Related
Apr 13, 2012
I am trying to find something that would allow me to have a spreadsheet clear the contents of a certain cell based on the selection made from a data validation list which resides on the same row. This can best be described with an example.
The user selects anything but " " or "none" from the validation list, which for this example resides in C5, would trigger code to clear the contents, if any, in AA5. If " " or "none" is chosen the value remains.
This capability should be available for each row through 100.
View 3 Replies
View Related
Aug 1, 2014
I have a spreadsheet where I am tracking several entries in a table that will keep growing. Three fields are Data Validation Drop Down Lists. The macro below works well to clear the two lists to the right when the first one is changed by the user.
[Code] .....
I want this to affect the rows below it in the table as they are added.
View 2 Replies
View Related
Oct 25, 2012
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
View 3 Replies
View Related
Mar 29, 2006
I have 2 validation boxes. The first is a simple list of 5 options (based on
a 'name' range). The second is a filter of a larger list depending on the
answer in the first box. I have made my 'name' range as big as the largest
list but other lists are quite short. The problem I have it that the drop
down box is showing all the blank cells in my 'name' range.
The bigger problem is that when you select the pull down it highlights the
blank field first instead of the first item on the list so you have to scroll
up. This is a problem as staff think there is nothing on the list. So how do
I either not show the blanks or get the drop down to start at the top of the
list?
View 10 Replies
View Related
Apr 14, 2009
I have a series of worksheets that are formatted for data imported from various scripts.
What I want to do is parse through each worksheet and cell, when the cell has numeric data, I want to clear the cell.
I do not want to clear the cells on the following conditions:
Cell has Text
Cell has Formula
Cell has date, month or time
View 9 Replies
View Related
Mar 17, 2008
See attached spreadsheet, in sheet 1, yellow columns are for restricted cells and Sheet 2 contains the lists for such columns' validation range. Sub-Strategy is dependent on the choice made from Main-Strategy, e.g. if Main-startegy = EquityHedge, then the choices for sub-strategy can only choose Long Biased, Low Beta & opportunistic. But I also have a "Other" category under Main Strategy, Sheet 2 cell G1, if this is chosen then I would like to remove the data validation in the sub-strategy column, (i.e. u can input anything for sub-strategy) how can I do this?
View 2 Replies
View Related
Nov 6, 2003
I've 12 numbers for 12 months in Cells. 13th Cell is the sum of all these 12 Cells. What I want is - Can I have a Data Validation on the Sum that it should not be more than 100. So, the users can enter the values in 12 Cells to make it equal to sum of 100 only. I know, I can do in VB Code but if it is possible to do as a function or Data Validation..
View 6 Replies
View Related
Aug 19, 2009
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies
View Related
Oct 16, 2008
Attached is the spreadsheet I am working on, log sheet and example sheet. As you can see from the example sheet I have location, Equipment type, equipment id and some others. As you can see on the log sheet, at the moment people select the information from validated cells, but if they select Point Machine from the equipment type list they have to scroll through all the equipment IDs when they only really need to see 10 equipment IDs relating to Point Machines. Is there a way to get the validation cells to do this or is there another way. I am very very new to VBA if this is an alternate way to do this.
View 2 Replies
View Related
Jan 23, 2013
im having when it comes to creating a drop box. When I click on a cell or several cells and go to click data validation, I get "No cells were found"
View 2 Replies
View Related
Nov 6, 2009
In need data validation in a range of cells (lets keep it to A1 for the example please). The data being entered is a 9 digit number, which needs to satisfy the following:
The 7th and 8th characters should be equal to an even number and be greater than zero, but less than 35.
Example 1: If 123456789 is entered in the cell, this would return an error because 78 (although being an even number) is above 35.
Example 2: If 987654321 is entered in the cell this would be allowed as 32 is an even number and less than 35.
View 9 Replies
View Related
Aug 15, 2008
I have a list and set cells with a List Data Validation so you can choose them from a drop down menu. What i want to do is when you choose one of the option is the cell next to it come up with other option relating to the previous. E.G. I choose John Smith in cell A1 as a manager and in A2 a list of his team appears.
View 4 Replies
View Related
May 1, 2014
I want to prevent copying and pasting over cells with data validation as this means the validation is overwritten. To get around this I've selected all the cells with data validation and unlocked them, then protected the worksheet with all boxes ticked apart from format cells, columns and rows.
This prevents copy/pasting from overwriting data validation but it doesn't prevent data being pasted in that doesn't meet the validation criteria.
So for example, say -1 is in cell A1, with no data validation. In B1 there is data validation, which doesn't allow negative numbers to be entered. If I copy and paste A1 into B1, the data validation isn't overwritten, but it doesn't stop the non validated data (-1) from being entered! If I then double click on B1 and press enter it recognises the validation criteria is not met.
View 3 Replies
View Related
Jul 24, 2009
I have column of cells, both of with have drop down menus to select information from. I have an adjacent cell to display an error based on conditions. Is there a way to have conditional formatting to apply to the cells with Data Validation to change the color, based on the error. I have tried using the Conditional Formatting options but it still does not change the color of them. I believe i have to use the formula option, but not exactly sure what to put, i know a if statement could work but unsure on how, there are only two conditions that i would need for it to apply, if the adjacent cell has an error, and when it does not. Is this possible?
View 9 Replies
View Related
Aug 21, 2009
I have a list in one worksheet which comes from "=SALESMEN!$D:$D" but the list is extremely long with blank values. How can I make the list only show values from column D which are non-blank?
Currently the list goes up to 30, however I want to use all of Column D from the SALESMEN worksheet, that way if I add to it, the names will automatically be added to the list in the other sheet.
View 10 Replies
View Related
Sep 26, 2009
Starting to struggle a bit here. Trying to create a user form, with help from this website I was able to create 2 dropdown lists based on data validation and information from another sheet in the same workbook. I have one cell B7 that uses data validation and sheet "Eq List" to create a drop down list of departments that the user can select from. Once B7 is filled in, it creates the 2nd dropdown list in B9 so the user can pick "Equip Desc".
Where I am trying to fill B11 which should automatically give me back the Mach ID that is related to B7 & B9. Am I going about this the right way? I have ttached a sample file to make it easier to understand.
View 4 Replies
View Related
Feb 3, 2014
Is there a way to have a user on sheet1 pick from a pull down of a data validation on sheet2? I have important information that is linked to only sheet2 within a data validation. I want to be able to reference it in sheet1 to have the user pick, then it toggles them on sheet2. Is this possible?
View 2 Replies
View Related
Feb 22, 2014
Somehow I cannot seem to count data in a column (e.g C) where cells are populated from a data validation list. The same formula works when I manually populate the same data in another reference column (e.g J). I have tried both COUNTIFS and SUMPRODUCT but they dont seem to work
=SUMPRODUCT(--($C$4:$C$9995=AB3),--($G$4:G$9995
View 2 Replies
View Related
Mar 4, 2003
I am using Data Validation and I don't want the user to go past a cell without putting in an entry. When I leave the box (ignore blank) unchecked it doesn't do anything after I have protected the document.
View 9 Replies
View Related
Oct 1, 2009
I need a macro which will lock the cells after data is entered in the cell once. Cell should should be only edited after a password is provided. I found the following macro but the problem is that it's not locking the cells where I have Data Validation List :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:M10000")) Is Nothing Then 'set your range here
ActiveSheet.Unprotect Password:="1234"
Target.Locked = True
ActiveSheet.Protect Password:="1234"
End If
End Sub
View 9 Replies
View Related
Jun 10, 2006
I have tried to modify the examples here on the site, but can't seem to get it. Cell G1 has list data validation of 5 numbers,6 digits long. In the cells below G10:G500, these numbers are listed. However, in the G10:G500 range the numbers have 8 digits. The first 6 digits remain the same and only the last 2 change. Here is what I would like to see happen: When I chose a number from G1, I would like to highlight all the cells with the first 6 digits matching G1 in the range G10:G500.
View 2 Replies
View Related
Apr 24, 2014
How I can Removing the Black cells in list in data validation ?
View 1 Replies
View Related
May 14, 2009
What I am trying to do would appear to be very simple. I am trying to build a quotation form for our sales guys to use. I would like to use a drop down list with the list of products in, which I have created using a list in a second sheet and then using a data validation drop down list.
What I would like to achieve is the sales person selects the product (of a list of only 14) from the list and excel to automatically fill in the two columns to the right with the model no. and also the price.
Would it be possible to use an IF statement as there are only 14 products to choose from? Is there a better way of doing this? I have created the lists of model numbers and prices in the second sheet alongside the product name which the drop down list sources from.
View 10 Replies
View Related
Apr 15, 2011
On Sheet2, data validation is applied to cells D21:D70 to limit data to values to a list, which is a named range on another worksheet. There are ranges on two other worksheets with the same data validation rule. The range for the list is ‘Sheet1’!D12:D26 (which is named “Processes”).
On Sheet1, the user enters a list of their processes. Then on Sheet2 (and the other sheets where the data validation is applied), they select one of the processes from the list. This all works fine until they go back to Sheet1 and modify one of the previously entered values.
For example, "Brush Teeth" is entered as one of the processes on Sheet1. It is then selected on Sheet2. Then, the user goes back to Sheet1 and changes the value to "Brush Hair." When you return to Sheet2, the value is still "Brush Teeth." It can be manually be changed by selecting “Brush Hair” from the drop-down list (“Brush Teeth” is no longer available), but it doesn’t automatically update.
Data validation was applied to a single cell (A3) and the list was on the same worksheet (B1:B3).
Option Explicit
Private oldValue As String
Private Const DVCell As String = "A1"
Private Const dataList As String = "B1:B10"
[Code]...
'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in.
On Sheet2 (I assume I need to add this code to every applicable sheet), I tried editing the first part of the code as follows (changes in bold), but when I click on any cell on Sheet2 that has the data validation rule applied, VB returns the error “Run-time error ‘1004’: Method ‘Range” of object ‘_Worksheet’ failed.” If I go into the debugger, the second instance of “If Not Intersect(Target, Range(dataList)) Is Nothing Then” is highlighted.
[Code] .....
View 9 Replies
View Related
Oct 13, 2007
I have a spreadsheet which I have formatted with validation rules for each cell. I have protected the sheet so that only certain cells can have info entered.
I also have a second spreadsheet with a bunch of data. If I copy this data and Paste Special it into my specially formatted sheet (Values only), all of the data copies over regardless of my preset validation rules.
I would like error messages to pop up for every incorrect value though.
View 9 Replies
View Related
Mar 10, 2008
when you choose Data Validation for a specific cell, you can choose list. Once you have chosen list for "Allow", you can select the source cells for what will be in the drop-down menu. I want to be able to use VBA (or just use Excel) to modify those source cells.
For example:
There will be 3 lists with drop-down menus using Data Validation.
List 1:
Choose first option in list -> List 2 uses source A
Choose second option in list -> List 2 uses Source B
etc...
List 2:
Choose first option in list -> List 3 uses source C
Choose second option in list -> List 3 uses source D
View 11 Replies
View Related
Jan 13, 2010
I'm trying to make the included spreadsheet as idiot proof as possible as a lot is a stake. I have most functionality working well; however, not I would like to add a little more data validation that does not exist in the normal cell validation rule set.
I need all the input cells to validate that the cell contains the word "BOX" upon entry, otherwise throw a warning. This is because the data entry will be done by barcode and there are a couple barcode labels on each box. The one I want will include the word "BOX" somewhere in the code sequence.
Since a previous function in the Code locks the cell, they wont be able to fix the error. but it will alert them so they can make sure to scan in the proper barcode and not miss the box. Also, once I get something that works in the code, where would I put it? In the same code box as the previous script, underneath it, or somewhere else? So, bottom line, I need a script that check the cell for Text of "Box" otherwise throw a message box.
View 2 Replies
View Related
Jul 10, 2014
I have a data validation list in col A.
I don't want ppl to be able to paste values in cell - them must either type the data or select from list.
Also - the sheet is protected but col a is open
have tried...without success
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.CutCopyMode = True
End If
End Sub
View 9 Replies
View Related
