Inserting little"checkboxes" to a list to indicate amendment

eliW

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,940
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
 
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Thank you very much Andrew it works nice.

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

Eli
 
Upvote 0
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)?
Click to expand...
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
 
Upvote 0
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.
 
Upvote 0
Nice nice nice!

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

Thank you again,

Eli
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
Checkboxes vs Drop Downs
2 3 4
Replies
38
Views
2K
My Aswer Is This
My Aswer Is This
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
491
Pingaware
P
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
446
Trixterz
T
K
  • Question Question
VBA for using a check box to Regen that row and then uncheck the box (ready to run macro again)
Replies
1
Views
738
Micron
M
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
659
mumps
mumps

Share this page

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
Back
Top