write a VBA macro that will check selected cells to see if their values conform to the corrosponding data validation rule?
I understand that data validation only checks the data upon entry. how to check all the cells or a selection of cells in a worksheet after the fact. I done a lot of editing and made changes to some of my validation lists and now want to know which cells no longer conform.
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 RelatedTemplate is created where the users copy/paste the data from other file. Data validation has been performed with the following steps:
1. Macro inserts the vlookup formula into column A, which isused for validating data that is entered by user from column B to E.
2. If the data is incorrect the N/A will be displayed in column A and invalid data will behighlighted in red color in column B.
3. This validation goes through the loop and after the loop is finished the pop message will be displayed and macro should stop so the user can correct the data.
4. After the user correct the data, the macro needs to run again to make sure there is no further errors. If there are no errors, thehighlighted cells should be cleared out of color and pop.
Here is the code that runs by command button:
Private Sub CommandButton1_Click()
Call FindNA
End Sub
Sub FindNA()
Dim ResultRange As Range
Dim ResultCell As Range
Dim iRow As Integer
[Code] ......
lately I have promised to program a little macro which looked easy for me to do but on which I have now spend a considerable amount of time without finding the problem why it is not running. I am working under Windows XP with Excel 2010, here is what i was trying to do:
In a worksheet there are two columns G and H. In both columns it shall be possible to select a number of possible entries from a list. For column G this is done via data validation, no problem. Now, the choices which are there in column H shall depend on the entries made in the appropriate row of column G. Column G thus functions as an overarching category. If I choose "Tree" in G, H would give me branches, leaves, apples. If I choose "car" the choices would be wheel, engine, gasoline etc. This shall be done by a Macro:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cat1 As Range
Dim cat2 As Range
Dim x As Integer
Set cat1 = Range("G:G") 'Task Group
Set cat2 = Range("H:H") 'Task Category
[code].....
When running, it gives me Run time error 1004: Method "Range" of object '_Worksheet' failed. Debug highlights the row
Code:
With Range(Cells(ActiveCell.Row, 8))
I have written the below code to enter some formulas, validation, and formatting. I keep running into a problem when trying to add the first validation.
The debug msg is...
Run-time error '-2147417848 (80010108)':
Automation error
The Object invoked has disconnected from its clients.
Debug highlighted portion is...
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PRODUCT" ....................
I am currently working in an excel file for which i want to create a macro. I am using colomn F as the base from which it should be determined whether a row should be copied. In colomn F the user can select three options through data validation. If 'completed' is selected, the whole row should be copied to the second sheet, and each copied row should be pasted to the next empty row. The data on the source sheet starts at row 11 and should be copied to sheet 2, starting from row 11 as well.
View 5 Replies View RelatedI 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.
View 3 Replies View RelatedI have a Data Validation list in cell A1 of a worksheet called LIST. I would like to modify the macro that I have recorded, the macro uses data from various worksheets. I would like to sort out some code so that at certain points in the macro it will select the worksheet that is shown in cell A1 of the LIST sheet and then carry out the code already recorded. THis will allow me to quickly change a sheet name that is refererred to in the macro numerous times without actually manually editing the sheet name in the macro code.
View 7 Replies View Relatedcreate macro in order to protect Data Validation rules.
e.g. If user copy and paste cells from others source which is not in the same validation criteria or not contain any validation rules, the existing validation will gone.
so, is there any macro which will be able to automatically run to prevent the data validation?
I have a data validation cell that has 5 items to be selected, lets say A-E.
I would like a macro that checks and does the following:
A or B selected, hide row 25-34, rows 45- 53
C or D selected, hide row 24-44
E selected, hide rows 34-53
In each case, the macro should check and unhide all rows between 25-53 before doing the above
I have a VBA macro for Excel 2007 below that loops through a workbook and deletes a picture (shape) in a range at the top of each worksheet.
The macro works fine until a cell which contains a seemingly unrelated data validation list on Sheets(1) is changed. The macro then repeatedly trips up with a 'Run-time error 1004 - Application defined or object defined error'.
The cell with the data validation is outside of the range in which the shapes are deleted and does not set any of the variables in the macro.
Sub DeleteLogos()
Dim Count As Integer
Dim NumberOfWorksheets As Integer
Dim Logo As Shape
Dim LogoZone As Range
NumberOfWorksheets = Worksheets.Count
For Count = 1 To NumberOfWorksheets
With Sheets(Count)
[code].....
I would like to delete a column using data validation list. for example :
i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.
And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no.,
the selected part no. row should be deleted.
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.
I'm trying to do a few things actually. I am somewhat a newb at programming, although I do understand how C++ and the coding works within Excel. To me, it's like the English language...knowing all of the vocabulary and formatting it right is important. If I don't know the vocabulary, I don't know what options I have.
This tool is being used in the call center I work in. It is to help eliminate repititious information used to notate accounts. I have attached a picture of what the tool looks like so you can have a better idea of what I'm working with. Here are a list of things I'm trying to do which I have searched and not found answers to:
1. I am trying to use a middle mouse scroll to scroll through validation list. This list information is located on a different locked tab. Basically, I have a few rows where a drop down selection can be made and it would be easier to scroll rather than manually moving the slider within the validation list.
2. As you can see, this "tool" an employee and I have created has many buttons. What I would like to do is to be able to click a button on the right and have it "stack" text within a single cell. In other words, if I click the button once, the text may be entered into the "actions taken" cell. If I click another button, I can have the option (or maybe a little "+" next to the button) to add this button's information to the end of the information already entered within the "actions taken" box. I've tried recording and using the keyboard and selecting "end" and then having the text added, but it didn't work. I've seen "loop" VBA code, but I don't want something to repeat, I want it to stack in the cell when clicked manually.
I'm sure there is a way to make the tool much better using VBA code and not use Excel, but I don't have an extensive knowledge of Excel to do something like that. I'm a very fast learner and understand how the coding works, so if you have an idea which may help, and you understand what I'm trying to do here, please provide your .02.
Basically, it's used so the Customer's name and phone number are entered manually, and then just about everything else is automated by the use of the buttons on the right. If I could get the 2 above options to work, it'd be perfect. I just figured out I could eliminate screen flicker and hide the macros working by using:
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is
Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName
It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies View RelatedI would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
I had some of values in Column A, B & C for ex: column A has brand name, column B has model name and Column C has sub_model name here i have a limited values i need to make it as drop down list but i had a problem with the below formula.
=IF($F$2=Sheet2!$H$2,al_v,IF($F$2=Sheet2!$H$3,am_v,IF($F$2=Sheet2!$H$4,au_v,IF($F$2=Sheet2!$H$5,be_v,
IF($F$2=Sheet2!$H$6,bmw_v,IF($F$2=Sheet2!$H$7,bg_v,IF($F$2=Sheet2!$H$8,cv_v,I
F($F$2=Sheet2!$H$9,ch_v,IF($F$2=Sheet2!$H$9,ch_v,"")))))))))
i need to add some more ifelse but the validation don't allows it.
I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
Essentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.
Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).
Also, the Data Validation dropdown includes blanks which I would like to exclude.
I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.
I want to load the actual data exist in the rows by selection value from the data validation list.
E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.
On the second sheet, i made a data validation list of all the person names
Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.
Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?
View 3 Replies View RelatedI have a excel sheet with values correcponsing few names. I have a list for a Data Validation drop down. How to get all the data row wise for the name selected in the drop down.
View 3 Replies View RelatedI am trying to learn the concepts, then apply them to a set of actual worksheets. I am trying to use named ranges from one worksheet in another worksheet. The named ranges need to expand. Although I can get the add data to a dummy worksheet to work within the same worksheet and I can even get a named range to work in the foreign sheet.
My problem is, when I can end up adding the additional data to the named range, it will only add one name. I have a number of named ranges that are referenced on various worksheets. The idea is, certain named ranges should be able to expand so I donft have to type them all in. (There could be hundreds of manufacturers or models.) I would also like them to be able to sort themselves after additional names are added to the named ranges. I would also like them to auto populate as well. I am using Excel 2003 SP3 (11.8307.8221). I have enclosed the file. Drop down lists (named ranges) are on the worksheet called DROP DOWN LISTS (LOCKED) **ITfS NOT CURRENTLY LOCKED**.
The data validation issues are on the worksheet called Rev7.0 Wood (Basic Sheet.) There are other instances to use these, but I hope to learn to do them myself on the other worksheets. Of particular interest is Column f.................
In column A I have a list of text. There are blank lines in between the cell that actually contain text. What I am trying to accomplish is create a validation list that will give me only the cells with text in them and ignore the blanks. For example in column A1:A7 I have the following text:
John
Mike
Tony
Jake
My validation list will return those names but will also give me the blanks in between the names.
Is there any way to ignore the blanks?
I have a file which contain lots of TAX Details of different country which we need to validate manually do we have any macro to validate them according to there countries
View 2 Replies View RelatedIf the user clicks the button to do the paste special macro twice - the second time they get an error (I am assuming that the clipboard has been wiped clean and they need to go back and copy the data again). The specific error message is: "Run-time error '1004': PasteSpecial method of Range class failed".
What I would like to do is add code to the pastespecial macro that looks to see if there is something to paste - if not, I want to give my own message and not the default one.
I have a workbook that contains a worksheet with commission data with a validation list of about 50 items. Each of these items represents a sales peron within my organization. Every month I select each of these items or sales people from the validation list (using the drop down box), which changes the commission data for each rep on the tab, then convert this to pdf to create and send each of these reps a commission statement.
I want to do now is link the code below to a drop-down menu I have created on one of the sheets in the workbook. I created this list using data validation and declared this drop-down menu as MIndex and the values to choice from are 0,1,2.
Does anyone know how I can link this drop-down menu to the macro. So basically once the drop-down menu is activated run this macro.
Sub SumMonthlyVol()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
Select Case UCase(Left(wSheet. Name, 2))
Case "AJ", "CJ", "PJ"
If (MIndex = 0) Then
wSheet.Range("L52") = "= SUM(R33C4:R50C12)"
Else
If (MIndex = 1) Then
wSheet.Range("L52") = "=SUM(R34C4:R50C12)" ......................