When a cell is merely selected, executed an evaluation?

[CRayF]

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?

 

[Bob Phillips]

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.

 

[CRayF]

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

 

[Don Guillett]

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

 

[Bob Phillips]

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

 

[Don Guillett]

I tried that but you must start with true or false IF op really wants that.

 

[Bob Phillips]

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?

 

[Don Guillett]

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.

 

[CRayF]

Thanks guys, this code works great for the initial set of rows.
Now, it doesn’t 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, I’m 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
-------------------------