Use Cell as Checkbox-2nd Question

  • Thread starter Thread starter Beth
  • Start date Start date
B

Beth

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)




.
Click to expand...
..
 
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
 
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),"")
 
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
 
Frank,
This is great. I was even able to modify it a bit and I
think I understand the logic.

How can I change it so the Vlookup happens whether the
user clicks on the cell or not? Possible?

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
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),"")
Click to expand...
check column.
So suit),
clicking
Click to expand...
tab, select
the
.
Click to expand...
 
Hi
one way would be to put the VLOOKUP formula directly on your sheet in
all cells. And let it change if the user clicks the cell (to either a
tick or to the existing formula).

But maybe I did not understand your correctly. You may give an example
for this


--
Regards
Frank Kabel
Frankfurt, Germany
Frank,
This is great. I was even able to modify it a bit and I
think I understand the logic.

How can I change it so the Vlookup happens whether the
user clicks on the cell or not? Possible?

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
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?


.

.

.
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top