how can I make an excel cell "mark" or "unmark" when clicked on?

Rick · 01-07-2006, 06:40 PM

Bob Phillips · 01-07-2006, 07:10 PM

One way

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "a" Then
.Value = ""
Else
.Value = "a"
End If
.Font.Name = "Marlett"
End With
End If

ws_exit:
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

RP
(remove nothere from the email address if mailing direct)

"Rick" <[email protected]> wrote in message
news:[email protected]...
> how can I make an excel cell "mark" or "unmark" when clicked on?

**mevetts** · 01-07-2006, 09:18 PM

Originally Posted by Rick

how can I make an excel cell "mark" or "unmark" when clicked on?

Contained in this worksheet's code module
It detects a click in columns D or E and then inserts a tick mark. It then moves to column G in the same row. Clicking on a tick, removes it and moves you to column G

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iOffset As Integer
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("D:E")) Is Nothing Then
If Target.Column = 4 Then
iOffset = 3
Else
iOffset = 2
End If
If IsEmpty(Target.Value) Then
With Target
.Font.Name = "Wingdings"
.Value = Chr(252)
End With
Target.Offset(0, iOffset).Select
Else
Target.Value = ""
Target.Offset(0, iOffset).Select
End If
End If
err_handler:
Application.EnableEvents = True
End Sub

Roger Govier · 01-08-2006, 04:50 AM

Hi Rick

In addition to the method already posted, you could use something like
the following.

Private Sub AddCheckBoxes()
Dim c As Range, myRange As Range
Set myRange = Selection

For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width,
c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
End With
With Selection.Font
.ColorIndex = 2
End With
Next
myRange.Select

End Sub

The ColorIndex part is just setting the font to be White, so that you
don't see the word TRUE when you select the click box.
The value of the underlying cell will be set to True when clciked, and
False when unclicked.

--
Regards

Roger Govier

"Rick" <[email protected]> wrote in message
news:[email protected]...
> how can I make an excel cell "mark" or "unmark" when clicked on?

David · 01-08-2006, 08:10 AM

mevetts wrote

> With Target
> Font.Name = "Wingdings"
> Value = Chr(252)
> End With

Just a FYI,
Doesn't happen w/o periods before Font and Value:
With Target
..Font.Name = "Wingdings"
..Value = Chr(252)
End With

--
David

Bob Phillips · 01-08-2006, 12:35 PM

I think you might find that is a result of the uploading from ExcelForum to
the NG, not the poster. Have seen it many times.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"David" <[email protected]> wrote in message
news:[email protected]...
> mevetts wrote
>
> > With Target
> > Font.Name = "Wingdings"
> > Value = Chr(252)
> > End With
>
> Just a FYI,
> Doesn't happen w/o periods before Font and Value:
> With Target
> .Font.Name = "Wingdings"
> .Value = Chr(252)
> End With
>
> --
> David

David · 01-08-2006, 06:20 PM

Bob Phillips wrote

> I think you might find that is a result of the uploading from
> ExcelForum to the NG, not the poster. Have seen it many times.
>

Point taken.

--
David