Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
a latching function
If cell (A) = a predetermined value then cell (B) = x. A simple if
statement,but, I want cell (B) to act as a latch. Once cell (A) has switched cell (B) to X I want cell (B) to stay in that condition even if the value of cell (A) changes from the predetermined value. i.e. I want cell (B) to behave like a latch, once it has been switched on it stays on regardless. Can you help. Kind regards -- KenH |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
a latching function
Ken,
One way is by utilising a 3rd cell that initially blank and the first time cell (a) satisfies your pre-determined value this 'flag cell' is given a value and vell (b) is set to x. Any subsequent changes to cell(a) will be evaluated as false by the macro because the check cell now has a value. In the macro below 1,1 is cell(a) 1,2 is the flag cell and 1,3 is cell(b) Private Sub Worksheet_Change(ByVal Target As Range) If Cells(1, 1).Value = 1 And Cells(1, 2).Value = "" Then Cells(1, 2).Value = 1 Cells(1, 3) = "x" End If Mike End Sub "Ken" wrote: If cell (A) = a predetermined value then cell (B) = x. A simple if statement,but, I want cell (B) to act as a latch. Once cell (A) has switched cell (B) to X I want cell (B) to stay in that condition even if the value of cell (A) changes from the predetermined value. i.e. I want cell (B) to behave like a latch, once it has been switched on it stays on regardless. Can you help. Kind regards -- KenH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
a latching function
Thanks Mike. Sounds easy when your brain works, that where I fall down.
Thanks again and kind regards. -- KenH "Ken" wrote: If cell (A) = a predetermined value then cell (B) = x. A simple if statement,but, I want cell (B) to act as a latch. Once cell (A) has switched cell (B) to X I want cell (B) to stay in that condition even if the value of cell (A) changes from the predetermined value. i.e. I want cell (B) to behave like a latch, once it has been switched on it stays on regardless. Can you help. Kind regards -- KenH |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
a latching function
Your welcome and thanks for the feedback.
"Ken" wrote: Thanks Mike. Sounds easy when your brain works, that where I fall down. Thanks again and kind regards. -- KenH "Ken" wrote: If cell (A) = a predetermined value then cell (B) = x. A simple if statement,but, I want cell (B) to act as a latch. Once cell (A) has switched cell (B) to X I want cell (B) to stay in that condition even if the value of cell (A) changes from the predetermined value. i.e. I want cell (B) to behave like a latch, once it has been switched on it stays on regardless. Can you help. Kind regards -- KenH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming |