VBA To Do 2 Validations On A Cell

Apr 13, 2009

On a tab, i have one column where information is entered. what should be entered is based on a dropdown list (which i have setup on a validation). the other thing that should be allowed to be entered is a an 8 digit number. when something else is entered, i need to have a popup message appear to tell the user to fix the error.

I have no idea how to effectively enter in the restriction in VBA for this.

Below is the VBA i have started:

1. The problem is that if one of the drop-downs is chosen, then i get this error message (because the dropdown list has text that is more than 8 characters in length).

2. The one other problem is that if the cell is left blank, i still receive the popup message. how do i avoid this from showing up.

3. I am also looking to do something like this for a range of cells (from F5:F100).

Private Sub Worksheet_Change()

Dim stue As String
stue = "f5"

Application.EnableEvents = True
If Len(Range("f5")) 8 Then
MsgBox "there is an error in cell " & stue & " "
End If
End Sub

View 9 Replies


ADVERTISEMENT

Validations Based On Previous Validations

Nov 12, 2009

On a worksheet I want 4 cells to have validation, when a selection in the first cell is made this limits the choice in the second, when the second is selected then the third is limited by 1 and 2 and the forth will be limited by 1,2, and 3.

In the attached example data , if I choose a Region, I have a choice of Countries. When I choose a Country I am limited to valid Cities and when I have chosen a City then a list of applicable Agents will be available. I have used NamedRanges for a two dimensional lookup but for four and my list will be far bigger than the sample, I do not fancy maintaining numerous NamedRanges.

View 5 Replies View Related

Multiple Validations To A Cell

Mar 6, 2007

I have an excel spread sheet, Col I is a Date, Col K is a Date, Col P is a date and Col T is a text(with list validation) I have validation that if if T = "As is" and P <> I or K (which ever one has a value) then Error. Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=IF(T2= ""Accept as is"",(IF(P2=(IF(K2<>"""",K2,I2)),TRUE, FALSE)), TRUE)"

But if the user changes T to some other value then it is ok to change P, But they could put any value in that field. I Need to validate that P is a DATE also
I was trying to do a validation Greater than > 01/01/2007 but I can not do multiple validations on the same cell. How can I validate P is a date also

View 3 Replies View Related

Validations Applied On A Cell Containing Formula

Apr 28, 2007

I have a problem in applying Data Validation on cells containing Formula. Eg: cell A1 has a value 10 and cell B1 has a value 15. i have a formula "=A1+B1" in cell C1. If i apply a Data validation to the cell C1 so as to accept values only in the range of 0 to 10, the cell C1 is still accepting values grater than 10 when i change the values in the cells A1 and B1.

View 2 Replies View Related

Multiple Data Validations With List And Cell Entry

Jul 2, 2013

I have a scenario where a cell has to be restricted based on the user input of another cell, also it should allow only Yes or No values.

ex:column A
column B

pending response
submitted

User should be allowed to enter a value in column B as "yes" or "No" only when column A has a value "Submitted"

I referred the thread to restrict entering the data [URL]...

However not able to add list as well.

View 2 Replies View Related

Excel 2003 :: Two Date Data Validations For One Cell?

Jun 8, 2013

I'm using Microsoft Office 365 Home Premium. Although this file is saved with Excel 97-2003 - because the computers at work only use 2003.

OK - Here is my dilemma. I've been creating a spread(work)sheet for work. One of the fields that needs to be entered by the manager doing the papwerwork is 'Date'. I currently have the Data-Validation set as follows because the paperwork being done is for the yesterday:

Code:
=TODAY()-1
I have the Error Alert set to Warning prompting the manager to ensure the date is correct (sometimes a mid-shift audit is being done, so the actual current date has to be used). Now, what I am wanting to do is to also set it that if the manager set's the date for a future date, I would like Excel to also either do a Warning or a Critical displaying a message that future dates cannot be used.

I know this may sound trivial, but with this paperwork, dates are extremely critical and vital in the event of an investigation for cash shortages etc.

I have uploaded the excel sheet to my domain, which I can provide a link to - either here or in a private message - but I do not want to do so unless I know that it's ok to do. If so, I will reply with a link to the location of the Excel file.

View 9 Replies View Related

Using Value In Single Cell To Determine Multiple Data Validations From Separate Work

Apr 10, 2014

I am creating a Spreadsheet to work with a game which I am designing. I want to create a combat calculator which will generate formulas based on which class a person chooses to be, and their choice of weapons, armour and other equipment (makes things a lot easier for me to visualize). I have created the first worksheet which will act as the interface where users can input their character configurations and a second worksheet where all of the data is stored, included items and their values.

Assuming that on the interface I have cell A1 which will let the user select their class (Warrior, Archer, Sorcerer).
I also have cells B1 = Helmet, B2 = Body Armour, B3 = Leg Armour, B4 = Feet Armour, B5 = Hand Armour.

I would like cells B1:B5 to create a Data Validation based on the class which is selected in A1.

E.g.
IF A1="Warrior"; B1 = A list of Melee Helmets, B2 = A list of Melee Body Armour... etc
IF A1="Sorcerer"; B3 = A list of Magic Leg Armour...

I have managed to do this with the following formula, but only if the data is stored in the same sheet.
Cell B1
=IF(A1="Warrior", =meleeArmour1, =IF(A1="Archer", =rangeArmour1, =magicArmour1))
Cell B2
=IF(A1="Warrior", =meleeArmour2, =IF(A1="Archer", =rangeArmour2, =magicArmour2))

[NOTE]
=meleeArmour1 represents the range of cells where the Melee Helmets/Head Armour is stored.
=magicArmour2 represents the range of cells where the Magic Body Armour is stored.

The group cell names do not seem to work if they are on a different sheet, nor does it work if I replace the group cell name (=meleeArmour1) with the full formula (=Sheet2!A$1$:A$10$)

View 5 Replies View Related

Validations In VBA

Sep 26, 2008

on VBA in regards validation rules in Excel?

View 9 Replies View Related

Edit Multiple Validations

Apr 24, 2007

I need to edit multiple validations in a worksheet. There are over 990 validation for each month. I want to change the word in the sentence. Example: April to May. Right now I have to edit each one seperatly. I have tried a macro but it changes to whole sentence. Find and replace do not allow editing in a validation.

View 9 Replies View Related

Erasing Data Validations In Worksheet

Jan 31, 2012

I've created a worksheet with a number of dropdowns / data validations. Now the thing with these validations is the source range apparently has to be in the same sheet (well it can be another one but much simpler if in the same). I've just realized I created them in the wrong place, so need to erase them. Pressing the delete key isnt working though, they're still there. I could right-click delete but that then requires one to move cells up/right etc and that could mess with my formulas.

View 7 Replies View Related

Data Validations And Filtercopy Macro

Jan 28, 2007

I am using a modified version of Debra Dalgleish's filtercopy macro in which 3 data validations are being used (she used 1 in her downloadable example ProductList. zip) and it works just fine when the validations are used in order as they should be. However, in looking over the data I am trying to present in my report I think it would be very valuable to use the first and third validations at times (skipping the 2nd) to discern information in that way. Long story short, I can't seem to get this to work and don't know if it's even possible because I'm not a VBA expert.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row = 3 And Target.Column = 4 Then
Worksheets("StoreData").Range("O2").Calculate
Worksheets("StoreData").Range("Database") _
. AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("StoreData").Range("O1:O2"), _
CopyToRange:=Range("A6:K6"), Unique:=False
End If
If Target.Row = 3 And Target.Column = 6 Then...............................

View 4 Replies View Related

Loosing Data Validations When Reopening Spreadsheet

Jan 29, 2013

I'm loosing the validations that I set up such as having a dropdown list and so on when I save and reopen the file. First I thought I didn't save it, but notice it happening consistently.

View 3 Replies View Related

Paste Formats And Validations To Current Row Of Spreadsheet

Jan 10, 2012

I am trying to paste formats and validations to the current row of my spreadsheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targetcol, ganntfirst, ganntlast As Integer
On Error Resume Next
Application.EnableEvents = False
targetcol = Range("BudgetHrs").Column
ganntfirst = Range("Ganntfirst").Column
[Code] .....

The code in orange is the problem code, and I have a range named format which is actually Row1:1

And I want the formats and data validation pasted into the current row and also the next row.

View 2 Replies View Related

Data Validations - Multipale Lists Depending On Each Other

Jan 6, 2009

I have search the site for a possible solution for my problem. And that is: how to create multiple lists using validation – depending on each other.

The scenario is. I have a data sheet that contains a list of alien plant species. The species have different methods of treatments (some do have the same). Each treatment might have one or more herbicide products that can be used and with each herbicide a certain application mixture.
I have used validation to create the pick list of the plants, the next pick list should be the treatment for the selected plant species and the 3rd list the product and then last list the mixture.
All of these lists should only list the information that is relevant to the selected plant specie, in the first list.

View 9 Replies View Related

Validations: Provide The Ability To Format The Fonts In The List

Nov 29, 2005

In MS Excel, I find that when the zoom is dropped to 50% the fonts in the
list become unreadable. Is there anyway that the fonts in the list have the
ability to have their own formats?

View 10 Replies View Related

Data Validations - Cells Remain Blank If Nothing Is Entered On Sheet 1?

Jul 22, 2014

On Sheet 1 I have Data Validation Lists set up. On Sheet 2 I have the information populating from what is entered on Sheet 1, only problem is...when I don't have anything entered in the cell(s) on Sheet 1 then the cell(s) on Sheet 2 reflect 0. I would like the cell(s) to remain blank if nothing is entered on Sheet 1.

View 4 Replies View Related

Multiple Data Validations (List And Preventing Duplicate Selection)

Jun 30, 2014

I have created a Data Validation list in one of my spreadsheets which works fine. The list itself has no duplicates which is fine but when I select my entries in the other spreadsheet, I want to make sure they cannot select the same value twice.

I tried selecting "List" in data validation and using an AND with COUNTIF but it doesn't work.

=AND(Employees, countif($A$1:$A$20,A1)=1)

View 2 Replies View Related

Populate Data Between 2 Linked File Based On The Dropdown Validations

Nov 27, 2009

How to populate data between 2 linked file based on the Dropdown Validations?

View 10 Replies View Related

Excel 2003 :: How To Create / Run Cell A Equals Cell B Put Cell C Info In Cell D

Jan 26, 2006

I need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.

View 7 Replies View Related

Autoupdate Cell Comment Based On Cell Input / Dynamic Cell Comments?

Aug 16, 2013

I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?

For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?

I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;

View 4 Replies View Related

How To Match A Cell Data With A Range Of Cells And Return Cell Reference In Another Cell

Dec 12, 2012

i want to match a cell data with a range of cells and if matches return the cell reference in another cell

View 3 Replies View Related

Excel 2010 :: When Cell Selected Can't Use Arrows To Move To Another Cell While Pointer Is Over The Cell

Apr 18, 2013

When I use the mouse pointer to select a cell I can't use the arrow keys to move to another cell while the pointer is over the cell and I can't edit the cell while the pointer is over the cell. If I move the pointer away from the cell then I can move around and edit as normal therefore I don't think this is a scroll lock issue.

This issue also happens when I select a tab. If I select a tab and then leave the pointer over the tab I selected then I can't use the arrow keys to move around the worksheet or edit a cell; if I move the pointer away from the cell then I can move around and edit as normal.

I am using MS Excel 2010.

View 1 Replies View Related

Adding A Date To One Cell Will Insert Close In Another Cell And Add Color To That Cell?

May 6, 2013

I would like to add a date to one cell (say A6) and have this do two things:

#1) this would add "Closed" to a given cell such a A5.

#2) and this would add a color to a group of cells like A1 through A8.

View 4 Replies View Related

Cell Formatting - Red Text If Cell Matches Certain Condition Based On Another Cell

Sep 24, 2013

I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)

When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.

I need the text in column AQ (the monetary amount) to become red when the the opposite is true.

i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.

View 2 Replies View Related

Look At Data From One Cell Reference Another Cell Then Display The Results From The Cell Next To It

Jun 19, 2008

I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.

Problem 1
Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:

Tab 1 (Never changes)
AB
Bob1
Jon2
Fred3

Tab 2 (Dynamic, changes each week)
AB
Jon
Fred
Bob

So it would work as follows.
Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result.

Problem 2

Weekly league rank table that shows position movements week by week
Example.

Week1
1Jon
2Bob
3Fred

Week2
1FredUp 2
2BobNot Moved
3JonDown 2

Can Excel calculate/show the actual movements of league positions?

View 9 Replies View Related

Join Cell Text And 1 Cell Date From Cell With 2 Dates

Sep 8, 2006

I have three cells in A2:C2 which require user to input some data.

What I want to achieve is to combine the data from A2:C2 in D2.

C2 is a field which user will input the date. He might key in 21/08/06 or
21/08/06, 30/08/06

I have tried using below formulas in D2 but without success.

=A2&" " &B2&" "&(C2)
=A3&" " &B3&" "&DAY(C3)&"/"&MONTH(C3)&"/"&YEAR(C3) (doesn't work if there are 2 dates.

I have attached a file which shows 3 scenarios if user input 1 date and 2 dates.

View 7 Replies View Related

Send Row Number Of Changed Cell To Cell Where Row Resides In Cell

Apr 26, 2008

I have the following range of cells B2:Y3700. I want that when a value from 1 to 9999 is inserted in one cell of the above mentioned range, to copy the row number of that cell in the cell that is located in column Z and row is the number inserted in that cell. For example, if the number "1234" is inserted in one cell from range B2:Y3700, to enter in cell Z1234 the number of the row where is located the respective cell. At same the time is very important to me to not permit to insert the same number in B2:Y3700, so i believe that is need a macro code to check the cell from column z to see if value already exist .

View 5 Replies View Related

Copy Cell To New Cell If Previous Cell Condition Met

Mar 25, 2013

formula to copy a cell to a new cell if the previous cell meets the condition.

i have attached an example work book.

so what im looking for is on sheet 1 there is a table and cell G9 shows Apples. on Sheet 2 is a second table where the information is in slightly different rows.

I want to be able to [When Sheet 2 Column C = Sheet 1 Column G Then Paste Sheet 2 Column D into Sheet 1 Column H (next to Apples)]

Then i would like in Cell J9 to look something like [When Sheet 2 Column C = Sheet 1 Column G Then Paste Sheet 2 Column F into Sheet 1 Column J]
and i would like Cell I9 to look something like [When Sheet1 Column J = Sheet 1 Column L Then Paste Sheet 1 Column M into Sheet 1 Column I]

G9 H9 I9 J9 L M <-- hidden C D E F
Sheet 1 |Apples| 20 | Hot |Average | Hot | Average Sheet 2 |Apples| 20 | AP | Average

View 1 Replies View Related

Marco To Sign Cell Value From Col B To Cell In Col C Based On Value Of Cell In Col A

Feb 12, 2014

I need to assign the ID value to the cell in the source column when it matches the cell value in the email column.

i.e from the email column the first cell value is bobjohnson@email.com his ID value is 0 I need to find all the cells with bobjohnson@email.com in the Source column and assign them 0.

[Code] ......

View 2 Replies View Related

Countif: Value Of One Returned To A Cell In A Row If One Cell In That Row Contains A Value Between 10 And 5 (less Than 11, More Than 4) And Another Cell Is Blank

May 8, 2009

I have a spreadsheet with details of letters that should be responded to within 20 days, and i want to have a cell showing the amount that that need a response within 10 - 5 days. Basically I want to have a value of one returned to a cell in a row if one cell in that row contains a value between 10 and 5 (less than 11, more than 4) and another cell is blank. For example, AD27 contains 6, k27 is blank. I want AE27 to show 1. I also need the same if the value is less than 5, but i'm sure i could figure out how to alter it.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved