Inserting little"checkboxes" to a list to indicate amendment

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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>
kitten_smush2.jpg

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

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Right click the sheet tab and choose View Code. Paste this into the code window on the right:

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

Well-known Member
Joined
Mar 2, 2002
Messages
1,931

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I assume that your list is in column B. In which row does it start and is it contiguous (no blank cells)?
 

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Eli,

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

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

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

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
Nice nice nice!

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

Thank you again,

Eli
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top