|
When a cell is merely selected, executed an evaluation?
My goal is to set up a situation so that with a cell is merely
touched/selected; it adds some values for reconciliation. My example has many complex calculations that range over rows 10-19. The cell I want to have selected in at Q10. However, I want to replicate rows 10-19 many times (20-29, 30-39, etc€¦) I first though using a checkbox at Q10 would be the best solution. A simplified example of the code looked like this: =IF(Q10,B18=E18) and when the checkbox was selected then Q10 evaluated TRUE. But when I replicate the rows, the checkbook does not replicate correctly. It copies the checkbox with the €śControl Cell Link€ť for Q10 (owned by rows 10-19), rather than a separate checkbox assigned to Q20 (owned by rows 20-29), Q30 (owned by rows 30-39), etc. Is there a way that I can merely test for Q10 being selected. And if this is done, then evaluate B18=E18 without using a checkbox? And would allow replicating the rows? And if not, is there a way to do this with a checkbox without the problem above? |
You can use the worksheet selectionchange event for that
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$10" Then 'do your stuff End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips "CRayF" wrote in message ... My goal is to set up a situation so that with a cell is merely touched/selected; it adds some values for reconciliation. My example has many complex calculations that range over rows 10-19. The cell I want to have selected in at Q10. However, I want to replicate rows 10-19 many times (20-29, 30-39, etc.) I first though using a checkbox at Q10 would be the best solution. A simplified example of the code looked like this: =IF(Q10,B18=E18) and when the checkbox was selected then Q10 evaluated TRUE. But when I replicate the rows, the checkbook does not replicate correctly. It copies the checkbox with the "Control Cell Link" for Q10 (owned by rows 10-19), rather than a separate checkbox assigned to Q20 (owned by rows 20-29), Q30 (owned by rows 30-39), etc. Is there a way that I can merely test for Q10 being selected. And if this is done, then evaluate B18=E18 without using a checkbox? And would allow replicating the rows? And if not, is there a way to do this with a checkbox without the problem above? |
Is there a way to code this so I toggle this.
If Q10 is selected again it will toggle P10 back to FALSE? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$10" Then Range("P10").Value = "TRUE" End If End Sub |
try
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$10" Then If UCase(Range("B10")) = "TRUE" Then Range("B10") = "FALSE" Else Range("B10") = "TRUE" End If End If Cancel = True End Sub -- Don Guillett SalesAid Software "CRayF" wrote in message ... Is there a way to code this so I toggle this. If Q10 is selected again it will toggle P10 back to FALSE? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$10" Then Range("P10").Value = "TRUE" End If End Sub |
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$Q$10" Then Range("P10").Value = Not Range("P10").Value End If End Sub -- HTH Bob Phillips "Don Guillett" wrote in message ... try Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$10" Then If UCase(Range("B10")) = "TRUE" Then Range("B10") = "FALSE" Else Range("B10") = "TRUE" End If End If Cancel = True End Sub -- Don Guillett SalesAid Software "CRayF" wrote in message ... Is there a way to code this so I toggle this. If Q10 is selected again it will toggle P10 back to FALSE? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$10" Then Range("P10").Value = "TRUE" End If End Sub |
Not giving up that easily Don <vbg
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$10" Then Range("P10").Value = CBool(Not Range("P10").Value) End If End Sub BTW Okay to mail you off-line about next Year's South-West Fest? -- HTH Bob Phillips "Don Guillett" wrote in message ... I tried that but you must start with true or false IF op really wants that. -- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$10" Then Range("P10").Value = Not Range("P10").Value End If End Sub -- HTH Bob Phillips "Don Guillett" wrote in message ... try Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$10" Then If UCase(Range("B10")) = "TRUE" Then Range("B10") = "FALSE" Else Range("B10") = "TRUE" End If End If Cancel = True End Sub -- Don Guillett SalesAid Software "CRayF" wrote in message ... Is there a way to code this so I toggle this. If Q10 is selected again it will toggle P10 back to FALSE? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$10" Then Range("P10").Value = "TRUE" End If End Sub |
typed Don into cell p10 then I got "type mismatch"
Feel free to always email me about anything. But, I don't know much about that music festival. -- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... Not giving up that easily Don <vbg Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$10" Then Range("P10").Value = CBool(Not Range("P10").Value) End If End Sub BTW Okay to mail you off-line about next Year's South-West Fest? -- HTH Bob Phillips "Don Guillett" wrote in message ... I tried that but you must start with true or false IF op really wants that. -- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$10" Then Range("P10").Value = Not Range("P10").Value End If End Sub -- HTH Bob Phillips "Don Guillett" wrote in message ... try Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$10" Then If UCase(Range("B10")) = "TRUE" Then Range("B10") = "FALSE" Else Range("B10") = "TRUE" End If End If Cancel = True End Sub -- Don Guillett SalesAid Software "CRayF" wrote in message ... Is there a way to code this so I toggle this. If Q10 is selected again it will toggle P10 back to FALSE? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$10" Then Range("P10").Value = "TRUE" End If End Sub |
Thanks guys, this code works great for the initial set of rows.
Now, it doesnt cover the replication of the rows€¦ ROWS 11 trough 22 are replicated. So the next set of rows would be 23 through 34 (in increments of €ś12€ť. Now, Im not familiar with the code syntax (as I am with REXX) and am wondering if there is a way I can set the code below into a loop. So I would need to execute the same instructions below in the €śPrivate Sub€ť for the multiples of 12€¦ Target.Address = "$Q$12" and Range("R11") (BTW€¦ A13 is a number) Target.Address = "$Q$24" and Range("R23") (A25 is a number) Target.Address = "$Q$36" and Range("R35") (A37 is a number) Etc€¦ AND the loop could end when the multiple of A13 €śISNUMBER€ť is tested FALSE. Would this be fairly easy to code instead of me manually replicating the code in the module? ------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$Q$12" Then If UCase(Range("R11")) = "TRUE" Then Range("R11") = "FALSE" Else Range("R11") = "TRUE" End If End If Cancel = True End Sub ------------------------- |
| All times are GMT +1. The time now is 04:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com