Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
In excel:
Inputs: C8-- button can change value to 1 or 0 C9-- =A3 (which can either be a 1 or 0) Output: C10-- =IF(C8=1,0,IF(C9=1,1,0)) Dilemma: When C10 is equal to 1, I want D10 to read "LATCHED". However, when C10 turns back into a 0 by changing the inputs, I want D10 to still read "LATCHED". I want to be able to do this without buttons, requiring the use of Worksheet_Change or something manner which I have no idea about. Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
Begin with opening the VBE by pressing ALT +F11 on the left hand side select
VBA Project(book1) or whatever your project is called select the plus sign file tree thing then Microsoft excel object then sheet one on the top of the VBE there will be two drop downs The on e that says (General) select Worksheet. Delete everything there and paste this in Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim OutRng As Range Dim InRng As Range Set OutRng = Range("D10") Set InRng = Range("c10") If InRng.Value = "1" Then OutRng.Value = "Latched" ElseIf InRng = "0" Then OutRng.Value = "UnLatched" '<-- Change End If End Sub "Mike B." wrote: In excel: Inputs: C8-- button can change value to 1 or 0 C9-- =A3 (which can either be a 1 or 0) Output: C10-- =IF(C8=1,0,IF(C9=1,1,0)) Dilemma: When C10 is equal to 1, I want D10 to read "LATCHED". However, when C10 turns back into a 0 by changing the inputs, I want D10 to still read "LATCHED". I want to be able to do this without buttons, requiring the use of Worksheet_Change or something manner which I have no idea about. Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
You don't need VBA. You can do it with a circular reference formula. (A
circular reference is a formula that refers to the same cell that contains the formula.) Use the following in D10: =IF(C10=1,"Latched",D10) If C10 is any value other than 1, D10 will be 0. If C10 equals 1, D10 will be "Latched". Now, if C10 is changed back to 0, D10 will retain the "Latched" value. To use circular references, you need to go to the Tools menu, choose Options, then the Calculation tab. There, check the "Iterations" box. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Mike B." wrote in message ... In excel: Inputs: C8-- button can change value to 1 or 0 C9-- =A3 (which can either be a 1 or 0) Output: C10-- =IF(C8=1,0,IF(C9=1,1,0)) Dilemma: When C10 is equal to 1, I want D10 to read "LATCHED". However, when C10 turns back into a 0 by changing the inputs, I want D10 to still read "LATCHED". I want to be able to do this without buttons, requiring the use of Worksheet_Change or something manner which I have no idea about. Thanks in advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
On Jun 23, 1:38*pm, Mike B. wrote:
In excel: Inputs: C8-- button can change value to 1 or 0 C9-- =A3 (which can either be a 1 or 0) Output: C10-- =IF(C8=1,0,IF(C9=1,1,0)) Dilemma: When C10 is equal to 1, I want D10 to read "LATCHED". However, when C10 turns back into a 0 by changing the inputs, I want D10 to still read "LATCHED". I want to be able to do this without buttons, requiring the use of Worksheet_Change or something manner which I have no idea about. Thanks in advance without a more detailed description of your conditions, it's difficult to help. However, it seems like you could accomplish what you are describing by adding AND conditions to your IF statement. Basically, this would simply require more than 1 condition to be true before something changed. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
What I am trying to do is simulate some electronics logic through excel.
Inputs: C8: comes from a button I created; the button gives the cell a value of 1 or 0. When the button is pressed, it changes the value of the cell from a 1 to a 0 or a 0 to a 1. When there is a value of 1, the word "UNLATCHED" appears right next to the button. When there is a value of 0, the cell becomes blank. C9: comes from the output of another card; this cell holds a value of either a 1 or a 0 Output: C10: =IF(C8=1,0,IF(C9=1,1,0)) For example: If at any time C8=1 (from the button), the C10 will have a value of 0. The word "UNLATCHED" will be under the button. The word "LATCHED" will not be present in D10. When the button is pressed, C8 goes to 0. "UNLATCHED" disappears. If C9 is 0, nothing happens. "LATCHED" is not present as well. When C9 is 1, C10 has a value of 1 and the word "LATCHED" appears in D10. Next, when C9 goes back to 0, C10 goes back to 0, but the word "LATCHED" is still present. Finally, when the button is pressed to give C8 the value of 1, C10 has a value of 0, "LATCHED disappears, and "UNLATCHED" reappears underneath the button. Hope this helps. "A Mad Doberman" wrote: On Jun 23, 1:38 pm, Mike B. wrote: In excel: Inputs: C8-- button can change value to 1 or 0 C9-- =A3 (which can either be a 1 or 0) Output: C10-- =IF(C8=1,0,IF(C9=1,1,0)) Dilemma: When C10 is equal to 1, I want D10 to read "LATCHED". However, when C10 turns back into a 0 by changing the inputs, I want D10 to still read "LATCHED". I want to be able to do this without buttons, requiring the use of Worksheet_Change or something manner which I have no idea about. Thanks in advance without a more detailed description of your conditions, it's difficult to help. However, it seems like you could accomplish what you are describing by adding AND conditions to your IF statement. Basically, this would simply require more than 1 condition to be true before something changed. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
Let me see if I can make it abit clearer. What I am trying to do is simulate
some electronic stuff through excel. Inputs: C8: I created a button that changes the value of the cell from 0 to 1. When the value is 1, the world "UNLATCHED" appears below the button in let's say F22 (whereever I put the button) C9: The value of this cell comes from another cell. This too, can either be a 1 or 0 depending on the other cell. Ouputs: C10: =IF(C8=1,0,IF(C9=1,1,0)) What exactly I am looking for: 1. At any time when the value of C8 is 1, C10 will always be 0. The word "UNLATCHED" will be located under the button. 2. When the button is pressed, the value of C8 is 0. C10 is still 0. The word "UNLATCHED" is not present anymore. 3. When C9 has a value of 1, C10 will now be 1. The word "LATCHED" will be located in D10. 4. One of two things can happen: a. When C9 goes back to 0, the value of C10 will be 0. The word "LATCHED" however remains present. b. Or, if the button is pressed, C8 becomes 1. "LATCHED" disappears from D10 and "UNLATCHED" appears under the button. 5. 4b doesn't matter since repeats all the previous steps. Once 4a is complete though, if I were to press the button, 4b would commence. Hope this clarifies. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|