Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mandatory Cells
Hi
I have 3 adjacent cells. What i want is, for example, a way of ensuring that, if there is data present in cell A1 (which looks up data and places product code in B1), then the user cannot leave cell c1 (order number) blank. The problem i envisage is that cell A1 looksup product codes from sheet 2 and places them in B1, so i'm thinking that any formula to ensure c1 isn't blank must ensure that if the lookup doesn't find the relevant cell (i.e. a1 isn't a valid product code and so throws up the messahe "n/a" in B1) then cell C1 should not be required. Can anyone help me out here? Any help greatly appreciated cheers olly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mandatory Cells
olly,
Post one of the formulas that you're using and we can probably give you a better answer. From what it sounds like you're trying to do, you have a vlookup formula in B1. You could use something like this in B1: IF(C1="","Please Fill In Col C",your vlookup formula) John Olly wrote: Hi I have 3 adjacent cells. What i want is, for example, a way of ensuring that, if there is data present in cell A1 (which looks up data and places product code in B1), then the user cannot leave cell c1 (order number) blank. The problem i envisage is that cell A1 looksup product codes from sheet 2 and places them in B1, so i'm thinking that any formula to ensure c1 isn't blank must ensure that if the lookup doesn't find the relevant cell (i.e. a1 isn't a valid product code and so throws up the messahe "n/a" in B1) then cell C1 should not be required. Can anyone help me out here? Any help greatly appreciated cheers olly |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mandatory Cells
At what point should the user be required to enter a value in B1. After
making an entry in A1 (understand B1 would need to return a valid value - assume B1 has some type of lookup formula). Will this only be enforced in A1 - C1 or is it for any value entered in column A. Anyway, right click on the sheet tab of the sheet where you want this and select view code. then put in a procedure like this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim bC1 As Boolean Static OldRange As Range On Error GoTo ErrHandler If Target.Count 1 Then Set OldRange = Target Exit Sub End If bC1 = False If OldRange.Address = "$A$1" Or _ OldRange.Address = "$C$1" And IsEmpty(Range("$C$1")) Then If Not IsError(Range("B1").Value) Then Application.EnableEvents = False bC1 = True Range("C1").Select End If End If ErrHandler: If bC1 Then Set OldRange = Range("C1") Else Set OldRange = Target End If Application.EnableEvents = True End Sub lightly tested. Regards, Tom Ogilvy "Olly" wrote in message ... Hi I have 3 adjacent cells. What i want is, for example, a way of ensuring that, if there is data present in cell A1 (which looks up data and places product code in B1), then the user cannot leave cell c1 (order number) blank. The problem i envisage is that cell A1 looksup product codes from sheet 2 and places them in B1, so i'm thinking that any formula to ensure c1 isn't blank must ensure that if the lookup doesn't find the relevant cell (i.e. a1 isn't a valid product code and so throws up the messahe "n/a" in B1) then cell C1 should not be required. Can anyone help me out here? Any help greatly appreciated cheers olly |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mandatory Cells
The user should be required to enter a value in C1 (not B1-
B1 merely holds a lookup formula, dependent on A1, looking up the products from sheet2) when there is a valid entry in A1 ( and therefore B1). Tom-the code you have put, does it need to be placed in a module ? Do you have to incorporate it into a button as a macro ? or does it auto check ? as for the range, it will be c1 should be mandatory if valid values in a1 and b1, and c2, if valid entries in a2 and b2. So is that code suitable for the problem i have described ? Many thanks for your help olly -----Original Message----- At what point should the user be required to enter a value in B1. After making an entry in A1 (understand B1 would need to return a valid value - assume B1 has some type of lookup formula). Will this only be enforced in A1 - C1 or is it for any value entered in column A. Anyway, right click on the sheet tab of the sheet where you want this and select view code. then put in a procedure like this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim bC1 As Boolean Static OldRange As Range On Error GoTo ErrHandler If Target.Count 1 Then Set OldRange = Target Exit Sub End If bC1 = False If OldRange.Address = "$A$1" Or _ OldRange.Address = "$C$1" And IsEmpty(Range("$C$1")) Then If Not IsError(Range("B1").Value) Then Application.EnableEvents = False bC1 = True Range("C1").Select End If End If ErrHandler: If bC1 Then Set OldRange = Range("C1") Else Set OldRange = Target End If Application.EnableEvents = True End Sub lightly tested. Regards, Tom Ogilvy "Olly" wrote in message ... Hi I have 3 adjacent cells. What i want is, for example, a way of ensuring that, if there is data present in cell A1 (which looks up data and places product code in B1), then the user cannot leave cell c1 (order number) blank. The problem i envisage is that cell A1 looksup product codes from sheet 2 and places them in B1, so i'm thinking that any formula to ensure c1 isn't blank must ensure that if the lookup doesn't find the relevant cell (i.e. a1 isn't a valid product code and so throws up the messahe "n/a" in B1) then cell C1 should not be required. Can anyone help me out here? Any help greatly appreciated cheers olly . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mandatory Cells
I told you where to put the code. It works each time the selection is
changed as long a macros are enabled and events are enabled. It works for A1:C1. It checks C1 just like you said - it doesn't do anything to B1. It doesn't do anything with any other rows, because you didn't say anything about an other rows. Regards, Tom Ogilvy "olly" wrote in message ... The user should be required to enter a value in C1 (not B1- B1 merely holds a lookup formula, dependent on A1, looking up the products from sheet2) when there is a valid entry in A1 ( and therefore B1). Tom-the code you have put, does it need to be placed in a module ? Do you have to incorporate it into a button as a macro ? or does it auto check ? as for the range, it will be c1 should be mandatory if valid values in a1 and b1, and c2, if valid entries in a2 and b2. So is that code suitable for the problem i have described ? Many thanks for your help olly -----Original Message----- At what point should the user be required to enter a value in B1. After making an entry in A1 (understand B1 would need to return a valid value - assume B1 has some type of lookup formula). Will this only be enforced in A1 - C1 or is it for any value entered in column A. Anyway, right click on the sheet tab of the sheet where you want this and select view code. then put in a procedure like this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim bC1 As Boolean Static OldRange As Range On Error GoTo ErrHandler If Target.Count 1 Then Set OldRange = Target Exit Sub End If bC1 = False If OldRange.Address = "$A$1" Or _ OldRange.Address = "$C$1" And IsEmpty(Range("$C$1")) Then If Not IsError(Range("B1").Value) Then Application.EnableEvents = False bC1 = True Range("C1").Select End If End If ErrHandler: If bC1 Then Set OldRange = Range("C1") Else Set OldRange = Target End If Application.EnableEvents = True End Sub lightly tested. Regards, Tom Ogilvy "Olly" wrote in message ... Hi I have 3 adjacent cells. What i want is, for example, a way of ensuring that, if there is data present in cell A1 (which looks up data and places product code in B1), then the user cannot leave cell c1 (order number) blank. The problem i envisage is that cell A1 looksup product codes from sheet 2 and places them in B1, so i'm thinking that any formula to ensure c1 isn't blank must ensure that if the lookup doesn't find the relevant cell (i.e. a1 isn't a valid product code and so throws up the messahe "n/a" in B1) then cell C1 should not be required. Can anyone help me out here? Any help greatly appreciated cheers olly . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I WANT TO CREATE MANDATORY CELLS | Excel Worksheet Functions | |||
Mandatory entry for a group of cells? | Excel Discussion (Misc queries) | |||
Mandatory Cells | Excel Worksheet Functions | |||
Mandatory Cells | Excel Worksheet Functions | |||
Creating mandatory fields(cells)... | Excel Worksheet Functions |