Checkmark Function to be modified

[Tom]

I use the "checkmark" function below (*****). It works great!

I only need to make a slight modification, but I'm not sure how to do it.

At this time, I can "check/uncheck" any cell in the cell range E4:T70 by
right-clicking in the cell.

I now have begun to import the spreadsheets into an Access database. If a
cell is "unchecked" in the spreadsheet (empty cell), I won't import any data
into the database for that particular field/record. I don't want that.

Instead, I'd rather translate the Wingding character 252 into a "Y" for any
checked cell & an empty cell into an "N" so that I can use a boolean field
in Access.

My question is as follows:
Does anyone have an idea how I don't clear the cell when "unchecking" it?
Again, instead, I'd rather show an "N". I replaced the
"Target.ClearContents" with

Target.Formula = "=char(78)"
Target.Font.Name = "Wingdings"

..... that allows me to first select the "checkmark", then toggle to the "N",
but it can not toggle back to the "checkmark".


Even better would be if the fontcolor of the "N" would automatically be
white (since I have a white cell background). Essentially, the user would
only see that a cell is checked.


Thanks,
Tom




**********************************************************************

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)

If Target.Column <> 5 And _
Target.Column <> 6 And _
Target.Column <> 7 And _
Target.Column <> 8 And _
Target.Column <> 9 And _
Target.Column <> 10 And _
Target.Column <> 11 And _
Target.Column <> 12 And _
Target.Column <> 13 And _
Target.Column <> 14 And _
Target.Column <> 15 And _
Target.Column <> 16 And _
Target.Column <> 17 And _
Target.Column <> 18 And _
Target.Column <> 19 And _
Target.Column <> 20 Then Exit Sub
If Intersect(Target, Me.Range("e4:t70")) Is Nothing Then Exit Sub
If Intersect(Target, Me.Range("E4:T70")) Is Nothing And
Intersect(Target, Me.Range("T:T")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Formula = "=char(252)"
Target.Font.Name = "Wingdings"
Else
Target.ClearContents
End If

Cancel = True 'stop the rightclick menu

errHandler:
Application.EnableEvents = True

End Sub

 

[Vasant Nanavati]

It's not clear to me whether you want the checkmark or the character "Y".
Anyway, here's a suggestion (I've also cut out the seemingly unnecessary
parts of your code):

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("E4:T70")) Is Nothing Then
On Error GoTo errHandler:
Application.EnableEvents = False
If Target = "" Or Target = "N" Then
Target = "Y"
Target.Font.Color = RGB(0, 0, 0)
Else
Target = "N"
Target.Font.Color = RGB(255, 255, 255)
End If
End If
errHandler:
Application.EnableEvents = True
End Sub

 

[Tom]

Vasant:

Thanks, that works great... and looks much more efficient.

 

[Tom]

Forgot to ask... what the line of code to drive the Background color of a
cell?

 

[Vasant Nanavati]

Target.Interior.Color (or ColorIndex)