Code to insert check mark

[jmcclain]

I found the following code in a post from 2005. I tried to follow the
instructions. I pasted the code in the view code and exited back to EXCEL.

i am trying to allow a user to click column D, E, and F and have EXCEL
automatically insert a check mark.

Am I on the right track? Can someone outline the process to "activate" this
code so it will work?

Thanks again

Jon

 

[jmcclain]

Sorry, here is the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
If Len(Trim(Target.Value)) = 0 Then
Target.Value = Chr(252)
Target.Font.Name = "Wingdings"
Target.Font.Size = 10
Else
Target.ClearContents
End If
End If
End Sub

 

[Bob Phillips]

Try this alternative


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "D:E" '<=== change to suit

On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
Select Case .Value
Case "": .Value = "a"
Case "a": .Value = ""
End Select
me.cells(.row,"C").select
End With
End If
err_handler:
Application.EnableEvents = True
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


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jmcclain]

Thanks Bob,

I have pasted it worksheet code module. Does it have to be someone
"activated"?

I changed the column range to D:F, but nothing happens when I click cell D4.

Can you help?

Thanks

 

[Bob Phillips]

No activation is required, it runs fine in those columns if you paste into
the worksheet code module.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jmcclain]

Bob,

Thanks - got it to work. Had to exit and re-open the file.

Is there a way to "format" the check mark. Center it? Make it bigger? Bold?

Agains...thanks

 

[Bob Phillips]

All 3.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "D:F" '<=== change to suit

On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
Select Case .Value
Case "": .Value = "a"
Case "a": .Value = ""
End Select
.HorizontalAlignment = xlCenter
.Font.Size = 14
.Font.Bold = True
Me.Cells(.Row, "C").Select
End With
End If
err_handler:
Application.EnableEvents = True
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jmcclain]

Thanks very much...it worked great

All 3.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "D:F" '<=== change to suit

On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
Select Case .Value
Case "": .Value = "a"
Case "a": .Value = ""
End Select
.HorizontalAlignment = xlCenter
.Font.Size = 14
.Font.Bold = True
Me.Cells(.Row, "C").Select
End With
End If
err_handler:
Application.EnableEvents = True
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)