Subject: Re: Use Cell as Checkbox-2nd Question
From: "Beth" <
[email protected]>
Sent: 2/25/2004 6:47:45 AM
Frank,
It works great.
How can I change it so the Vlookup happens whether the
user clicks on the cell or not? Possible?
In response to your question, if both A7 and C7 are
populated, then the conditional formatting turns the row
green and the user knows that record has been resolved. No
action in column K is necessary.
Beth
-----Original Message-----
Hi Beth
and what happens if both A7 and C7 are not empty?. but for your
example try the following
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("K1:K100")) Is Nothing Then
With Target
If Cells(.row, 1).Value = "" And Cells (.row, 3).Value
= "" Then
If .Value = "" Then
.Value = "X"
Else
.Value = ""
End If
ElseIf Cells(.row, 1).Value <> "" And Cells (.row,
3).Value = "" Then
.Value = ""
.FormulaR1C1 =
"=VLOOKUP(R2C[0],'lookup'!R7C1:R1000C15,9,FALSE)"
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub
--
Regards
Frank Kabel
Frankfurt, Germany
Beth wrote:
Thanks. Here's the more detail about the vlookup.... If A7
has a value and C7 doesn't, then go get the matching data
from the Lookup sheet. Otherwise, let the user click to
toggle a tick mark on and off.
=IF(AND($A7<>"",$C7=""),VLOOKUP($B7,Lookup!
$A$7:$O$1000,9,FALSE),"")
-----Original Message-----
Hi
you may try the following:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("K1:K100")) Is Nothing Then
With Target
If Cells(.row,1).value = "" and cells (.row,3).value =
"" .Value = "a"
.Font.Name = "Marlett"
Else
.Formula = "=VLOOKUP(....)"
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub
If you can provide more details about the kind of VLOOKUP
formula I would add this respective line to the above code
--
Regards
Frank Kabel
Frankfurt, Germany
Beth wrote:
Bob,
Thank you very much. The user is impressed, and now wants
more! Can I add a condition to one of the IF statements?
The user is now asking for the tick mark functionality to
be available in all of column K, but only where the values
in A and C are blank. Otherwise, they want a vlookup to
pull a value in from elsewhere.
I can handle the if statement, and I can handle the
Vlookup. But I don't know how to put them in the code you
so generously supplied. Is it possible?
-----Original Message-----
Beth,
This is a re-print of a previous post of mine that suggests
what you want, although I use a tick, not X.
===========================================
Here is an alternative approach I have previously suggested
Another way is to use this technique of having a check column,
and monitoring
it with a worksheet selection change event. Add your code as
needed.
Rather than use a checkbox, I suggest just using a check
column. So if we assume that the data is in A1:E100 (change to
suit), clicking in column A will do what you want with this
code. Add this code to the worksheet module (right-click on
the sheet name tab, select the View option, and then paste
this code in).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
With Target
If .Value = "a" Then
.Value = ""
Else
.Value = "a"
.Font.Name = "Marlett"
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
When the user clicks in a cell, I would like the cell
contents to toggle between X or blank. In other words, I'd
like to use a cell as a checkbox. How would I code that?
