How do I force a dependent validated cell list to go blank (erase previous entry) if the origin cell is changed.
A1 = Fruit B1 selected as APPLE from validated list
A1 changed to = Vegetable but B1 stays as APPLE unless changed as well.
Can I force a B1 blank when A1 is changed to ensure B1 is correct?
I'd like to create a data validation with 1 dropdown list in the second worksheet in B4, relating to 3 cell ranges on the first worksheet (COS, Expenses & Capital). If 3 can't work, I've created 1 called 'dropdown' which incorporates all 3.
which formula I need to write into the validation, or what else I need to do in order to find a solution to this.
I'm building a form comprising some text boxes and drop down lists. I'd like for data (once input into the form by the user) to input, upon click of a submit button, into an excel spreadsheet, row by row.
Here's where i'm struggling: I need the form to validate data before submitting. Namley, the form must not allow null values to be submitted and will show a message box telling the user what is needed.
Below is what i've got so far. I've tried playing around with this but am struggling to implement the above functionality:
[Code] ..........
I'm currently developing a calendar that has a list in it with lets say 4 options. What I want the calendar to do is calculate at a specific 'cell' the number of entries that are selected during the month.
The idea is to have a drop down on each 'day' and a counter that calculates the number of times one specific options has been selected. Once the option has been selected the 'day' will change to the corresponding color.
When opening the attached file, for some reason, I have to again select the cells from column A in tab "Transactions" and go to Data Validation and select again LIST - only then the data validation from list resumes working. When I close the file after saving and open it again, the data validation does not work - I have to again go to Data Validation and again reselect LIST (making sure, of course, that I do not mess up Source) to have Data Validation list run again.
what is wrong with list validation when opening the file.xlsx
I am currently working on a workbook to have employees fill in data on what tasks they compelted for the day, and how long it took. There are 5 colums (for this purpose) Task, Description, Time, Required to complete, Completed. The tasks are predefined and listed out in each row. There is also a space for employees to select the date they are entering the data for.
I would like to have a macro that is linked to a submit button and when pressed populates this data into a database on another sheet. This database is split into two sheets (1 with time and one with tasks required/compelted). Each sheet has the list of tasks going down a cloum on the left, and dates along a row on the top. When the employee presses submit I would like this data to popuplate in the coresponding date and task fields and then reset the form on sheet 1 to all 0's.
I have a drop down in IE in which four values are there
I will need to select each one at at time to make some change and move to next dropdown
the dropdown in IE should ideally have 4 dropdowns 01,02,03 and 04
However due to vendor errors we may have any of the above missing from dropdown or extra orderpoints in the dropdown like 05
IE.document.getElementById("vendororderpoint").value ="01" is the code to select order point 01
I need an alert in excel if any of the 4 dropdowns is missing.
I have a macro that creates a new sheet, the name of which is based off of an input box. Does someone have some generic code to ensure that the user inputs a valid name? Here's what I have, it only checks to see if the name is a duplicate:
'Prompt user for the name of the output sheet
newsheetname = inputbox("Name Your Output Sheet:", "Notice!", "Margin Output")
'Check whether or not the name is taken
TestName:
For a = 1 To Sheets.Count
If Sheets(a).Name = newsheetname Then
newsheetname = inputbox("Name Taken. Please Rename Your Output Sheet:", "Notice!", "Margin Output")
GoTo TestName
End If
Next a
It's the last road-block left to making this function work. The Problem is in the If...Then statement. If A is true the formula works, but is it is not, then it returns a value. The Expression
IBGLink = (E & CustomDate & EndOfLink)
works on its own, so there is an issue with
If Application.WorksheetFunction.IsError(A) Then
Does anyone know an alternative way of validating a variable or a trick of sort that can be used here. A to E and CustoDate and CustDatePN are declared as global variables.
Function IBGLink(IBG_URL As String, FrstDate As Date, ScndDate As Date, Optional EndOfLink As String)
A = Mid(IBG_URL, Application.WorksheetFunction.Find("/200", IBG_URL), 8)
B = Left(IBG_URL, Application.WorksheetFunction.Find("/200", IBG_URL))
C = Right(IBG_URL, Len(IBG_URL) - (Len(A) + Len(B)))
D = Left(C, Application.WorksheetFunction.Find(".200", C))
E = Left(IBG_URL, Application.WorksheetFunction.Find(".200", IBG_URL))
CustomDate = Format(FrstDate, "yyyymmdd")
CustDatePN = Format(ScndDate, "yyyymmdd")
If Application.WorksheetFunction.IsError(A) Then
IBGLink = (E & CustomDate & EndOfLink)
Else: IBGLink = (B & CustomDate & D & CustDatePN & EndOfLink)
End If ................
I have the following codes in a new workbook that has nothing else in it:
Private Sub Textbox1_Change()
OnlyNumbers
Sheet1. Range("A1").Value = TextBox1.Text
End Sub
Private Sub TextBox2_Change()
OnlyNumbers
End Sub
Private Sub UserForm_Click()
End Sub
This works fine in this workbook, however, if I try to use it in a workbook that has a lot of macros & userforms it doesn't work at all!
i am trying to make a user form with two msg boxes (To and From Date). In excel spreadsheet It was rather easy to bring a popup msg if they were invalid under some stated rules. see below a rationale of the rules where A3=From Date and B3 To Date and the desired outcome of is:
[Code] .......
So i wonder if it is possible to make a VBA code to validate these two dates? and bring a warning msg after keypress or after pressing submitcmd or any other available if available.
I'm attempting to require a numerical entry in a cell using data validation. The function =AND(ISNUMBER(cell),NOT(ISBLANK(cell))) does not perform as intended. Unchecking "Ignore Blank" has no effect. The ISNUMBER function evaluates to TRUE on a blank cell. When used outside of data validation, NOT(ISBLANK(cell)) evaluates to FALSE on a blank cell, making me think the AND(...) function should be sufficient.
Valid entries are any number, including 0.
Can this be done without VB?
I'm trying to automate a field in my file that tells me whether or not a promo code is valid.
Col F2 = Promo Start Date (example: 1/1/13)
Col G2 = Promo End Date (example: 5/1/13)
Col H2 = Valid? (Yes/No) (example: "Yes")
What formula would I put in H2?
I have a truth table, let's say it is an ordinary 2^3
ID 1234567 8
condition AYYYYNNNN
condition BYYNNYYNN
condition CYNYNYNYN
Currently I am pasting the results of a DB query into the left half of my sheet and I analyse this data using Excel functions in the right half.
Among these functions are cells that evaluate the conditions above, e.g.:
cond A is evaluated as true or false in column H
cond B is evaluated as true or false in column K
cond C is evaluated as true or false in column M
What I would like is a function to place at the end of the row (col T) which will display the ID of the relevant column in the truth table that the three values together correspond to, e.g.:
A=T, B=T, C=F gives result 2
A=F, B=F, C=T gives result 7
I have a excel sheet which has 2 columns
One has numeric values and other has description.
Each numeric value should match the description..
For eg:-
NoName23Vendor 145Vendor 278Vendor 380Vendor 5
The no should match the name
like this there are around 6000 matches
is there any possible way to include these many validations in one cell and display the result as "Good" or "Bad"
I have been trying to achieve the following with formulas but have not been successful. I am making a simple schedule, and I am trying to validate the cells so that you can not schedule someone for multiple tasks during the same time range. In the example that I attached, Harry is diswashing from 1:00 - 4:00 but is also scheduled to paint from 2:00 - 5:00 (this should not be allowed). How can I achieve this with code?
View 7 Replies View RelatedI have two userforms. When the user chooses the choice "Other" in the userform frmTradeSickOther, another userform called frmOther will be called. At this point, the user will enter some required comments in a textbox (called txtOther) and click the "Add Comment" button. If the user does not enter anything, a prompt will inform the user to type something in the textbox.
My problems: Once the user clicks okay to the prompt, frmOther is called back. When it's called back, I want the cursor to blink in the textbox. Eventhough I have SetFocus on the textbox, it does not work. I don't know why.How do I prevent the user from just hitting enter (i.e., accepting blank lines as comments) and bypassing the commenting part. The code below accounts for hitting enter once (i.e., one blank line), but not multiple times. How do I do this?
Here is what I have when the user clicks on the "Other" choice in frmTradeSickOther:
Code:
Private Sub optOther_Click()
Unload Me
frmOther.Show
frmOther.txtOther.SetFocus 'This gets the cursor to blink in textOther when frmOther is called.
End Sub
Here is what I have when the user clicks the "Add Comment" button on frmOther:
Code:
Private Sub btnAddComment_Click()
On Error Resume Next
txtOther.Value = Trim(txtOther.Value) 'This is to make sure spaces aren't accepted as a comment.
If Me.txtOther = vbNullString Then
Me.Hide
MsgBox "Please enter the necessary information.", vbInformation, "Required Field"
Me.Show
Me.txtOther.SetFocus 'Here is where I set focus on txtOther.
Exit Sub
End If
'Everything below is to add comments automatically to each cell when a value is changed.
Application.CommandBars("Cell").Controls("Delete Comment").Enabled = True
If Not ActiveCell.Comment Is Nothing Then
Application.CommandBars("Cell").Controls("Edit Comment").Enabled = True
Range(curCell).ClearComments
[Code] .......
I'm trying to write a formula that passes or fails a value.
Example: The user types into a cell "ABC 123"
The ABC is a constant therefore it is just a matter of IF=(D10="ABC","pass","fail")
However the 123 must be equal to another cell (d4)
So the formula I'm trying to write looks something like IF=(D10="ABC" + D7, "pass","fail").
This does not work, any better methods?
I want to inform my users via a message box if they have not entered the previous month's information. The months are populated via a User form using a combo box selection. The months start from April through to March and are entered into the worksheet range ("aa3").
Data is entered monthly by the team. I don't know how to begin with this. I've managed to inform them when they've already entered that months information, but I don't know where to begin with this.
while trying to limit the user's input to a userform textbox to no avail. For example one textbox on the form should only be numbers and I therefore want to restrict the user to typing in a two digit code like 02 or 72 (not for calculations). Another textbox I only want to allow the user to input 6 characters in the format letter, four numbers and a letter. If the user inputs the wrong stuff a message box pops up and the focus is reset
View 8 Replies View RelatedI'm trying to validate 2 cells and if both matches it should return a value from column 3
I've gotten so far as it would return a error when criterea is not met.
However is it finds a match it always returns the first value.
My current formula is: =INDEX($C$24:$C$29,(AND(MATCH(A12,$A$24:$A$29,1),MATCH(B12,$B$24:$B$29,0))))
I can't find a way to make Excel validate the first 2 columns and return the value from column 3.
I have been using excel form last 1 year. I do have good knowledge for macro but for validating emailID entry I am not getting success.,So if any body can give me a sample code for validating email ID entry or a macro for checking and highlight those email ID which r wrongly formated..
View 10 Replies View RelatedI have generated a matrix in excel through iteration (I'm trying to calculate a dinamic covariance matrix between 50 values) which looks like this:.......
A 50x50 matrix. What I have generated in each cell is not the formula, but the text of the formula. Somehow Excel has a valid formula in a specific cell, but "doesn't know yet" that within the cell there is no longer a text. So, to make every formula run, I have to go cell by cell pressing F2, then enter, 2.500 times. Notice that in each formula I don't have something like this:
+"+COVAR(Rends!C4:AB4;Rends!C4:AB4)" or
'+COVAR(Rends!C4:AB4;Rends!C4:AB4)
but the valid formula: +COVAR(Rends!C4:AB4;Rends!C4:AB4)
I am trying to find out how many projects might be active as of a certain date.
I have a tab in excel that contains project data. For each project there is a "Start Date" and there might be an "End Date"
My question is, how do I count all the rows where the start date is Less than a given date and the end date is after a given date? The wrinkle is... if the end date is empty then obviously it should be counted as well (assuming the start date is still before the given date).
Here are the approaches I've used thus far:
=SUMPRODUCT(1*( AND(OR(RawMetrics!P:P > A2, RawMetrics!P:P = ""), RawMetrics!$O:$O < A2 )))
=COUNTIFS(RawMetrics!P:P,">"&Sheet1!A2,RawMetrics!O:O,"
I have a workbook with 20 worksheets the first is a summary called Discount Set the 19 sheets with various names
The 19 sheets in column H4:H40 u enter a number greater than 0 which is the quanty required. This is used to calculate a price on which ever row it is entered
the selection must only affect numbers as there are rows that are just shading and they contain no data .
So u could enter quanties on serval rows of different sheets to get a total price displayed in the Discount set
What I want to do is have button on the Discount Set which will clear any quantity that does not equal 0 back to 0 in column H which will reset the total 0.00
Reset Counter to Zero:
I have Record ID on Column A: Auto Increment
I have a vendor Name on Column B: Vendor 1, Vendor 2, Vendor 3
I have a Code on Column C, Code A, Code B
What I want to happen is the Counter to count +1 each time. it sees the same vendor, same code and only to count to 4. As soon as it counts to 4; the next counter should be 0 (Zero).
Col A Col B Col C Col D
======= ====== ===== =======
Record ID Vendor Code Counter
======= ====== ===== =======
1 Vendor 1 Code A 1
2 Vendor 1 Code A 2
3 Vendor 1 Code A 3
4 Vendor 2 Code B 1
5 Vendor 1 Code A 4
6 Vendor 1 Code A 0
8 Vendor 2 Code B 2
9 Vendor 2 Code B 3
10 Vendor 1 Code B 4
12 Vendor 1 Code A 1
13 Vendor 1 Code A 2
14 Vendor 1 Code A 3
15 Vendor 2 Code B 0
It can be either in VBA or formula...
Is there a way to take the values between C2 & D2 and have them automatically post in column F ?
View 2 Replies View Relatedis there anyway i can reset this to start at st001 ive got everything done but i need to reset the orders to start at st001
View 3 Replies View RelatedI know that a variable of string type is equal to "" before it's assigned a value. So I reset the value of strings to empty (i.e. "") when I want them clear of a value. I also know that variables of Variant Type are equal to NULL when they are unassigned so I reset them to NULL when I want to clear them of a value.
But what about a Long? When you declare a variable of Long Type or Int Type or Double Type, what is it's inital value when empty and unassigned. I assume it's not 0 since 0 is an actual number.
I want to reset my lng variable and clear it of a value. So I'm wondering what should I set to... How can i do this?
I have a textbox that pulls language using the following code:
Code:
Private Sub TextBox1_Enter()
TextBox1.Value = Sheets("Language").Range("B2").Value 'places the value of the cell into the textbox
[Code]....
Randomly and sporadically, when working in the textbox, the form seems to reset. By reset, I mean that the language resets to what was there originally.
