a value in a cell depending on some condition.
In the attached sample file, when A2=Fruit, A9=Domestic, A10=Orange and A11=1, B2 would not accept a value greater than 200 and there is a pop up message.
This control check works fine if a user has input "Fruit" in A2 first and input amount in B2 later.
However, if the user has input value 250 in B2 first and input "Fruit" in A2 later, 250 would be accepted by B2 and there is no pop up message.
I have cell E15 in Sheet1 which will take an 8 digit Personnel Number, which can have leading 0's. I want to prevent a user from entering any characters other than 0123456789. I have worked out that I need to use the Worksheet_Change event and I need to use the function IsNumeric.
No matter what I do I can't trigger the Worksheet_Change event!
I have a range B1 to B4 for user to input value. Worksheet Change Event
However, the range B1 to B4 should not accept any value if the cell on the left hand side is empty.
Eg,
If A1 is empty, B1 should not accept any value.
If A2 is empty, B2 should not accept any value.
If A3 is empty, B3 should not accept any value.
Below code checks for condition in A1 only.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:B4")) Is Nothing Then
If IsEmpty(Range("A1")) Then
MsgBox "Please input something under column A."
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
Target.Offset(0, -1).Select
Exit Sub
End If
End If
End Sub
Eg, If I enter something in A1, B2 would accept a value which is not what I hope to achieve. It should be checking for cell in A2 whther it is empty.
Would be grateful for some advice on how to modify the code.
I attached a sample file for reference
I've created Keyboard Shortcuts to run Macros in "Workbook A". Problem is, if I have "Workbook B" open in the same Excel instance/process as "Workbook A" the Keyboard Shortcuts will run the Macros in "Workbook A".
Is there a way to restrict the Keyboard Shortcuts to only run the Macro if Workbook A is active?
Or is there a way to code the Macro to run its procedures only if Workbook A is active?
I have a ctrl-q key activated macro (module 2) that successfully clears specific data entry cells in forms on two identically formatted sheets. How do I prevent access to that macro on the remaining three sheets of five in the workbook?
View 2 Replies View RelatedIs there a way to restrict a macro to run only a specified number of times during a user designated time period, for example run no more than 3 times during a 24 hour period?
We have a macro that runs to alert maintenance personnel if a certain temperature condition is met in machinery. This condition may happen numerous times during a day but we only need to alert the mechanics once a day if the condition is true.
I am working in both 2003 and 2007 Excel.
This is a simplified version of my worksheet:
I have two cells, A2 and B2. When I enter a numeric value into B2, I want it only allowed when certain text values are in A2. I.E.: Allow any value in B2, but only if A1 is either "SYDN or "ADEL".
Now using info found in an old post, I can achieve this with a combination of data validation and code:
=OR(A2="SYDN",A2="ADEL") for the validation and for the code:
[Code] ......
The only problem is I also need to reset B2 if A2 changes to invalid data as a result of some other input to that cell (A2). In other words if B2 is showing "1" already and A2 were to change from "SYDN" to say "LOND", then B2 needs to be cleared. If A2 changed to "ADEL" then it doesn't need to clear but it is ok if it does because it is fine to re-enter "1" into B2 again.
I have attempted to attach a sample worksheet : Xl0000003.xls
for instance if i type in a particular cell any number 1, 0, 53 .. excel must not allow me to do this...
but there are certain value that have both number and alphatbets eg. 001 michael jordan ... cell should allow such values.
I have two coloumns on a worksheet. In the first column I have data validation drop down list. I want to be able to: if the first column "Other" is chosen then the next cell will allow text for description, if anything other is chosen in the first drop down list cell then I want the next ( text for descritption ) cell to be resticted.
View 4 Replies View RelatedI am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple:
Cell A1, they can select Rice, Cheese, or Rabbit
I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10.
Can I do that with data validation? I can't get any if thens to work in it.
I need a formula that I can put into a cell, I assume via data validation, and will prevent the use of any symbols other than a hyphen "-". Spaces are fine. The cell is being used to enter the 1st line of an address.
View 14 Replies View RelatedI am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E.
What I am looking for
1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that.
2) I would like this validation/formula to be applied to all the cells in a particular column.
3) Nice-to-have.
If the user enters a lower case letter, then I would like to convert to upper case.
I have a cell (A1) with a number in it. The number has 30 digits. I need a formula for (B1) that will enter the same number as A1 but restrict the number to 28 digits. Basically I need it to cut off the last 2 digits.
View 3 Replies View RelatedI am running the following code to check if a value = 0 is in specific cells. Because this code on all the cells runs each time any cell in the workbook is calculated, the screen flashes. I would like to stop this screen flashing by having the check done only when the specific cells are exited....
View 9 Replies View RelatedIs there a way to limit say a value to be inputted into a cell say between 1 and 10, and to have a warning message pop up if any other values are keyed into that particular cell?
View 3 Replies View RelatedI'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
I have two cells, A1 = Yes/No and B1 = Date. I want to restrict someone inputting a date into B1 until A1 is marked as Yes...? I've tried data validation, but seem to be hitting a wall with it...
View 2 Replies View RelatedThe 'gist' of what I need is for events scheduled (top of spreadsheet), no school can be placed into an event within 29 minutes (because one-half hour apart IS ok). The time increments are on the left side of the spreadsheet (rows). I'm trying to "automate" somehow so that if we try to plug a school into an event too soon from their last event, the spreadsheet won't allow it.
View 2 Replies View RelatedI've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
I am looking for a forumla, which I think will be an If forumla, to allow/block entry into a cell depending on what is entered in another cell.
So if "Yes" is entered into cell 1, I want cell 2 to show "N/A", and if "No" is entered in cell 1 I want the user to be able to enter data into cell 2.
I want to restrict user from entering alphabets like I,O,Q and S in a cell. Can we do this using data validation or any formula because I don't wan't to use drop-down list or refer to values in other cells either.
View 3 Replies View RelatedI am using the below code to enter the data in Cell "D" & "E" of the worksheet.
Dim a, b As Integer
a = WorksheetFunction.CountA(Sheet2.Range("C:C"))
ActiveWorkbook.Sheets("Retailing Data Sheet").Activate
'If Range("C" & a + 1).Value "" Then
[Code] .......
But, If someone wants to enter the data manually into the Cell "D" & "E" its allowing which i dont want.
It should be enter by using the form only...
I'm am trying to restrict the length of data that can be entered into a cell based on a drop down in that row. I have achieved this, but what I need to be able to do is use data validation to restrict the cell length to one of two values - so for example the length can only be 6 OR 9 characters long.
I cant see how to do this. At present the best I can do is rectrict to between 6 and 9 - but this would allow 7 and 8 which is not allowed.
I know there must be some way to write vba to force users of an excel sheet to choose from a drop-down list for that particular cell. I am having trouble with a spreadsheet that is used widely through the company with people doing copy/paste or just typing in entries.
View 2 Replies View RelatedI'm trying to create a sheet where clicking in a range brings up, in my users words, "a box I can type loads of comments in". They want some kind of flag in this cell showing if comments are posted or not. So far so ok, got the userform to pop up using the selection change event below and dump the actual comments somewhere the user won't look. A rather inelegant IF statement to see if there's anything in the dump cell gives them their flag.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Intersect(Target, Range("B2:B300")) Is Nothing Then Exit Sub
UserForm1.TextBox1.Value = ActiveCell.Offset(0, 10).Value
UserForm1.show
End Sub
So what's the problem? When I select entire rows, the userform pops up. Is this unavoidable?
I am trying to use excel to score a test. I want to tick a cell to do so. There is an excellente information about Tick Cell Upon Selection, posted here http://www.ozgrid.com/VBA/excel-checkbox-tick-cell.htm. Its range is limited to (A1:A100)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub
How can I increase the range so it would target C1:C30, E1:E30 ( total of 17 columns)?.
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.
I am trying to restrict a cell to only be able to input the letter "i" multiple times, almost like a tally sheet, in other words I want the cell to be restriced to one letter, but allow that letter to be entered multiple times.
View 4 Replies View RelatedI was reading your Tick Cell Upon Selection article and it works great. I'm just wondering if there is a way to modify it so that they can only select one of the cells in a row.
For example, in this spreadsheet, the cells I have marked for "tick upon selection" are N8-Q8. In this worksheet, though, I only want the user to be able to select one of the four options. Is there a way to format the code so that they either cannot select another cell until they have deselected the first one, or the first one unchecks itself and the new click ticks that cell?
Example: Person selects N8. Person then selects P8. Can it not allow P8 to be selected (and give a warning message) or can it uncheck N8 when P8 is checked?
I have below code which pin out numbers from IBAN in Column P. Its formulas run by macro. It works fine. But what i really need is that instead of putting the formula in the cell, is that it compare the value from the formula, with the excisting value , in each cell . And it mark the cells where there are a difference with yellow colour I have been thinking how to do this but cant really work it out. If some have a better solution its ok but i need a macro for it.
have a look at below code, which change the cell and correct the error, but which i want just to compare the values.
I have attached a sheet to test with.
[Code] .....
Attached File : Testforcompare.xls