[SOLVED] 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" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
"CRayF" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> "CRayF" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

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

--
Don Guillett
SalesAid Software
[email protected]
"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > [email protected]
> > "CRayF" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:%[email protected]...
> I tried that but you must start with true or false IF op really wants
that.
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > [email protected]
> > > "CRayF" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
[email protected]
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > I tried that but you must start with true or false IF op really wants
> that.
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Bob Phillips" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > > [email protected]
> > > > "CRayF" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 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
-------------------------