Inserting little"checkboxes" to a list to indicate amendment

[eliW]

Hi all,

Simple question or maybe not??

I have a list of names and addresses that I am editing from time to time.
Now I added a new column "A" to the list in order to use it to "check" (checkbox) on every edited record. This must be also printable.

I wonder what is the technique of doing this? it must be in every row of the list in column "A".

Is this involves writing VBA code? -I prefer not, or at list a simple one.

Also I want it to be easy to check or uncheck by a click on the mouse.

Help will be appreciated much.

Eli

 

[Von Pookie]

I guess you could draw one on the worksheet by using the Forms toolbar and then copy it into all of the cells you need, but that may be way too time-consuming...

_________________<font color="#3399FF" size="3">Kristy</font>

This message was edited by Von Pookie on 2002-10-21 14:03

 

[eliW]

Thank you Kristy you are right.. and also I want it to be a part of the database and not just a drawing.

Any other suggestions?

Eli

 

[Andrew Poulsom]

Right click the sheet tab and choose View Code. Paste this into the code window on the right:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If IsEmpty(Target) Then
        Target.Formula = "=CHAR(252)"
        Target.Value = Target.Value
        With Target.Font
            .Name = "Wingdings"
            .FontStyle = "Bold"
            .Size = 8
        End With
        Target.Borders.LineStyle = xlContinuous
    Else
        Target.ClearContents
    End If
End Sub

Now if you click a cell in column A a tick will appear/disappear.

Post back if you need to limit the rows for which this happens.

 

[eliW]

Thank you very much Andrew it works nice.

Yes I need to limit the rows for which this happens to the list range.

Eli

 

[Andrew Poulsom]

I assume that your list is in column B. In which row does it start and is it contiguous (no blank cells)?

 

[eliW]

On 2002-10-22 02:54, Andrew Poulsom wrote:
I assume that your list is in column B. In which row does it start and is it contiguous (no blank cells)?

My range is aolumn B to G
Column B is continues
The number of rows may vary.

And one thing more:
When I'm staying on say cell A6 and click to check, I can't uncheck it in place unless I'm first leaving the cell and then coming back. Is there a solution to toggle between check and uncheck in place?

Thank you very much in advance,

Eli

 

[Andrew Poulsom]

Eli,

Here is some slightly revised code to limit the ticks to those rows containing an entry in column B:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    If Target.Row > Range("B65536").End(xlUp).Row Then Exit Sub
    If IsEmpty(Target) Then
        Target.Formula = "=CHAR(252)"
        Target.Value = Target.Value
        With Target.Font
            .Name = "Wingdings"
            .FontStyle = "Bold"
            .Size = 8
        End With
        Target.Borders.LineStyle = xlContinuous
    Else
        Target.ClearContents
    End If
End Sub

The code relies on the SelectionChange event so you have to move away and come back for a single click to work. However the code below will allow you to double click and toggle the tick:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    If Target.Row > Range("B65536").End(xlUp).Row Then Exit Sub
    If IsEmpty(Target) Then
        Target.Formula = "=CHAR(252)"
        Target.Value = Target.Value
        With Target.Font
            .Name = "Wingdings"
            .FontStyle = "Bold"
            .Size = 8
        End With
        Target.Borders.LineStyle = xlContinuous
    Else
        Target.ClearContents
    End If
    Cancel = True
End Sub

Just paste it below the other code if you think it wil help.

 

[eliW]

Nice nice nice!

This opened to me an entire new field of EVENT procedures.

Thank you again,

Eli